VBAでAccessデータベースを操作する際、一件ずつレコードを処理するのではなく、「条件に合う全てのレコードを一度に更新したい」または「新しいレコードを一件、SQLで直接追加したい」という場合があります。
このようなデータ操作には、SQL言語で記述した命令(アクションクエリ)を直接データベースに送り込んで実行するDatabase.Execute
メソッドが非常に強力で効率的です。
この記事では、Excel VBAからAccessに対し、UPDATE
(更新)とINSERT INTO
(追加)のSQLコマンドを実行する方法を解説します。
【重要】実行前の参照設定
DAOの機能を利用するため、事前にVBEの ツール
> 参照設定
から**「Microsoft Office XX.0 Access database engine Object Library」**にチェックを入れてください。
レコードを更新する (UPDATE文)
特定の条件に一致する複数のレコードを一度に変更する場合、UPDATE
文を使用します。
コード例 (UPDATE)
例えば、「勤続年数(YearsOfService
)が10年以上」の全従業員の給与(Salary
)を5%引き上げる、といった処理は以下のようになります。
'参照設定: Microsoft Office XX.0 Access database engine Object Library
Sub ExecuteUpdateSQL()
' 変数を宣言
Dim db As DAO.Database
Dim sqlStatement As String
Dim dbPath As String
' データベースのパスを指定
dbPath = ThisWorkbook.Path & "\EmployeeDB.accdb"
' データベースに接続
Set db = DBEngine.OpenDatabase(dbPath)
' 実行するUPDATE文を文字列として作成
sqlStatement = "UPDATE M_Employees SET Salary = Salary * 1.05 WHERE YearsOfService >= 10"
' SQLステートメントを実行
db.Execute sqlStatement, dbFailOnError
' データベースを閉じる
db.Close
' オブジェクトを解放
Set db = Nothing
MsgBox "UPDATE処理が完了しました。"
End Sub
ポイント解説
- SQL文の作成: 実行したい
UPDATE
文を、まずは一つの文字列変数(sqlStatement
)に格納します。 db.Execute
メソッド:Database
オブジェクトの.Execute
メソッドに、作成したSQL文字列を渡すだけで、データベースエンジンがその命令を実行してくれます。dbFailOnError
オプション:.Execute
の第二引数にdbFailOnError
を指定すると、もしSQLの実行中にエラーが発生した場合にVBAの実行時エラーとして通知してくれるため、トランザクション処理などを行う際に安全です。
レコードを追加する (INSERT INTO文)
新しいレコードを1件追加する場合、INSERT INTO
文を使用します。
コード例 (INSERT)
「M_Employees」テーブルに新しい従業員データを1件追加する処理は、以下のようになります。
'参照設定: Microsoft Office XX.0 Access database engine Object Library
Sub ExecuteInsertSQL()
' 変数を宣言
Dim db As DAO.Database
Dim sqlStatement As String
Dim dbPath As String
' データベースのパスを指定
dbPath = ThisWorkbook.Path & "\EmployeeDB.accdb"
' データベースに接続
Set db = DBEngine.OpenDatabase(dbPath)
' 実行するINSERT INTO文を文字列として作成
' 長いSQL文は「& _」で改行すると見やすい
sqlStatement = "INSERT INTO M_Employees (EmployeeID, EmployeeName, Department) " & _
"VALUES ('EMP031', '鈴木 次郎', '経理部')"
' SQLステートメントを実行
db.Execute sqlStatement, dbFailOnError
' データベースを閉じる
db.Close
' オブジェクトを解放
Set db = Nothing
MsgBox "INSERT処理が完了しました。"
End Sub
ポイント解説
- SQL文の作成:
INSERT INTO テーブル名 (フィールドリスト) VALUES (値リスト)
という構文に従ってSQL文を作成します。 - 文字列の引用符: SQL文の中でテキスト(文字列)型の値を指定する場合は、
'経理部'
のように**シングルクォーテーション('
)**で囲む必要がある点に注意してください。 db.Execute
メソッド:UPDATE
の時と同様に、.Execute
メソッドでSQL文を実行します。
まとめ
Recordset
オブジェクトをループさせて一件ずつデータを更新するのに比べ、Database.Execute
メソッドを使う方法は、特に大量のデータを一括で処理する場合に非常に高速でシンプルです。
- UPDATE: 条件に合うレコードの一括更新
- INSERT INTO: 新規レコードの追加
- DELETE: 条件に合うレコードの一括削除
これらのアクションクエリをVBAで組み立てて.Execute
で実行する手法は、Accessデータベース操作の強力な武器となります。