【Excel VBA】Accessに対しSQLコマンド(UPDATE/INSERT)を実行する方法

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データベース操作の強力な武器となります。

よかったらシェアしてね!
  • URLをコピーしました!
  • URLをコピーしました!

この記事を書いた人

私が勉強したこと、実践したこと、してることを書いているブログです。
主に資産運用について書いていたのですが、
最近はプログラミングに興味があるので、今はそればっかりです。

目次