VBA：【ADO】AccessDBへのSQL実行（Insert・Update・Delete）
■SQLの実行
SQLは「Executeメソッド」を利用して実行できます。
「Executeメソッド」はアクションクエリ(Insert、Update、Deleteなど)のみ実行可能で、選択クエリ(Selectなど)は実行できません。
■SQLのサンプルコード
ここではひとまず実行するAccess自身への操作のサンプルを記載します。
|
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 |
Private Sub SQL実行_Click() Dim CN As ADODB.Connection 'ADOのオブジェクトを宣言' Dim xSQL As String Set CN = CurrentProject.Connection '現在のデータベースに接続' '//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_追加データ]" CN.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.[点数]" CN.Execute xSQL 'SQLを実行' '//Delete' xSQL = "Delete * " xSQL = xSQL & "FROM [T_サンプルコピー]" CN.Execute xSQL 'SQLを実行' CN.Close 'DBを閉じる' Set CN = Nothing '接続解除' MsgBox "完了" End Sub |
