【ACCESS/VBA】SQL文実行サンプル

 Private Sub ExecSQL()
    Dim cmd As New ADODB.Command
    Dim conn As New ADODB.Connection
    Dim rst As New ADODB.Recordset
    Dim rtn As Integer
    m_cn = New ADODB.Connection
    conn.ConnectionString = "DSN=TEST_ODBC;Uid=USER1;Pwd=userpass1;Database=TEST_DB"
    conn.Open
    
    Set cmd = New ADODB.Command
    Set cmd.ActiveConnection = conn
    
    ' SELECT文（レコードセットに結果取得するパターン）
    With cmd
        .CommandText = "SELECT * " & _
                       "FROM TEST_TBL1 " & _
                       "WHERE column1 = ? AND " & _
                       "      column2 = ?;"
        .CommandType = adCmdText
        .Parameters(0).Value = 1        ' 1つ目?へのバインド
        .Parameters(1).Value = "AAA"    ' 2つ目?へのバインド
    End With
    Set rst = cmd.Execute
    rst.Close
    Set rst = Nothing
    
    ' UPDATE/INSERT文（レコードセットに結果取得しないパターン）
    With cmd
        .CommandText = "UPDATE TEST_TBL1 " & _
                       "SET   column2 = ? " & _
                       "WHERE column1 = ?;"
        .CommandType = adCmdText
        .Parameters(0).Value = "BBB"        ' 1つ目?へのバインド
        .Parameters(1).Value = 1    ' 2つ目?へのバインド
    End With
    Call cmd.Execute

    With cmd
        .CommandText = "INSERT INTO TEST_TBL1(" & _
                       "    column1, " & _
                       "    column2 " & _
                       ")VALUES(" & _
                       "    ?," & _
                       "    ?);"
        .CommandType = adCmdText
        .Parameters(0).Value = 2        ' 1つ目?へのバインド
        .Parameters(1).Value = "CCC"    ' 2つ目?へのバインド
    End With
    Call cmd.Execute
    
    Set cmd = Nothing
 End Sub