AccessVBA：【DAO】SQL実行。Insert・Update・Delete等サンプルコード
■SQLの実行
SQLは「Executeメソッド」を利用して実行できます。
「Executeメソッド」はアクションクエリ(Insert、Update、Deleteなど)のみ実行可能で、選択クエリ(Selectなど)は実行できません。
■SQLのサンプルコード
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 |
Private Sub SQL実行_Click() Dim CDB As DAO.Database 'DAOデータベース用のオブジェクトを宣言' Dim xSQL As String Set CDB = CurrentDb '現在のデータベースに接続' 'Set CDB = "C:\Users\VBA\Newファイル.accdb"' '他のAccessに接続' '//Insert' xSQL = "INSERT INTO [T_成績データ](" xSQL = xSQL & "[番号] " xSQL = xSQL & ",[名前] " xSQL = xSQL & ",[科目] " xSQL = xSQL & ",[点数] " xSQL = xSQL & ")" xSQL = xSQL & " SELECT" xSQL = xSQL & "[番号] " xSQL = xSQL & ",[名前] " xSQL = xSQL & ",[科目] " xSQL = xSQL & ",[点数] " xSQL = xSQL & "FROM [T_追加データ]" CDB.Execute xSQL 'SQLを実行' '//Update' xSQL = "UPDATE [T_成績データ] AS T1" xSQL = xSQL & " INNER JOIN [T_最新データ] AS T2" xSQL = xSQL & " ON T1.[番号] = T2.[番号]" xSQL = xSQL & " AND T1.[科目] = T2.[科目]" xSQL = xSQL & " SET" xSQL = xSQL & " T1.[点数] = T2.[点数]" CDB.Execute xSQL 'SQLを実行' '//Delete' xSQL = "Delete * " xSQL = xSQL & "FROM [T_サンプルコピー]" CDB.Execute xSQL 'SQLを実行' CDB.Close 'DBを閉じる' Set CDB = Nothing '接続解除' MsgBox "完了" End Sub |
