【Access　VBA】 サンプルコード例メモ

サンプルコード例メモ

クエリ実行処理関連

Public Sub BeginTransX2()

    ‘recordset and connection variables
   Dim Cnxn As ADODB.Connection
   Dim strCnxn As String
   Dim rstTitles As ADODB.Recordset
   Dim strSQLTitles As String
    ‘record variables
   Dim strTitle As String
   Dim strMessage As String

   ‘Open connection
   ‘strCnxn = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\\テスト　データ.accdb"
   ‘Set Cnxn = New ADODB.Connection
   ‘Cnxn.Open strCnxn
   
   Set Cnxn = Application.CurrentProject.Connection
    
   ‘ Open recordset dynamic to allow for changes
   ‘Set RecordsetTmp = New ADODB.Recordset
   
   ‘クエリの実行サンプル（追加クエリ）
   Dim qdf As QueryDef
   Set dbs = CurrentDb
   Set qdf = dbs.QueryDefs("クエリ1")
   With qdf
    .Parameters("ID指定") = 1
    .Execute
   End With

    ‘ Create command object   
    On Error GoTo trans_Err

   
   Cnxn.BeginTrans

   ‘動的クエリの実行サンプル（更新クエリ）   
    Set cmdChange = New ADODB.Command
    Set cmdChange.ActiveConnection = Cnxn
    cmdChange.CommandText = "UPDATE テーブル1 SET [テーブル1].名前 = ""bbbb"", [テーブル1].日付 = """ & Now() & """ WHERE ((([テーブル1].ID)=1));"
    cmdChange.Execute
   
    Cnxn.CommitTrans

trans_Exit:
    ‘Clean up
    Cnxn.Close
    Set Cnxn = Nothing
    Exit Sub
   
trans_Err:
    ‘Roll back the transaction
    Cnxn.RollbackTrans
    Resume trans_Exit

End Sub