Access のパラメータクエリでトランザクションを行って PostgreSQL のデータを更新する方法

Access 2010 の VBA + ADO で PostgreSQL のデータを、トランザクションを行いながらパラメータクエリ（プレースホルダ）で更新する方法になります。ODBC を使用しているので、SQL Server でもさほど手を入れなくても動作すると思います。

さて、サンプルコードでは、DSN レスで PostgreSQL に ODBC 接続を行い、トランザクション処理をパラメータクエリ（プレースホルダ）で行っています。Access ではあまり問題になることは少ないと思いますが、SQLインジェクション対策ですね。

細かい部分はサンプルコードのコメントを参考にしてください。

■サンプルコード

Private Sub commandRegist_MouseDown(Button As Integer, Shift As Integer, X As Single, Y As Single)

On Error GoTo error_sub

    Dim con As ADODB.Connection
    Dim cmd As ADODB.Command
    Dim param As ADODB.Parameter
    Dim conStr As String
    Dim uId As String
    Dim uName As String
    Dim sql As String
    
    
    If MsgBox("登録しますか？", vbYesNo) = vbNo Then
        Exit Sub
    End If

    '画面より値取得
    uId = Me.textUserId.Value
    uName = Me.textUserName.Value
    
    
    'DNS レスでODBC接続
    conStr = "Provider=MSDASQL;DRIVER={PostgreSQL Unicode(x64)};SERVER=localhost;DATABASE=postgres;UID=user01;PWD=password@1"
    
    Set con = New ADODB.Connection
    con.Open conStr
    
    'SQL を宣言
    sql = "INSERT INTO users (user_id, user_name) values (?,?);"
    
    'Commandをインスタンス化
    Set cmd = New ADODB.Command
    Set cmd.ActiveConnection = con
    
    cmd.CommandType = adCmdText
    cmd.CommandText = sql
    
    'パラメータを作成、１つずつ
    Set param = cmd.CreateParameter("ID", adChar, adParamInput, 5)
    cmd.Parameters.Append param
    
    Set param = cmd.CreateParameter("NAME", adVarChar, adParamInput, 255)
    cmd.Parameters.Append param
    
    'パラメータに値を設定
    cmd.Parameters("ID").Value = uId
    cmd.Parameters("NAME").Value = uName
    

    'トランザクション開始
    con.BeginTrans
    
    'SQL実行
    cmd.Execute
    
    'コミット
    con.CommitTrans
    
    
    con.Close
    Set cmd = Nothing
    Set con = Nothing
    
    MsgBox "登録できました。", vbInformation

    
    Exit Sub
    
error_sub:

    'エラー発生時はロールバック
    con.RollbackTrans
    con.Close
    
    Set cmd = Nothing
    Set con = Nothing
    
    MsgBox "登録に失敗しました。", vbInformation
    MsgBox Err.Description
End Sub

■参考サイト

• パラメータクエリをADOから実行する-CreateParameterメソッド ： ADO入門講座