【Excel VBA】Accessへのデータ登録で使うトランザクション処理 (Commit/Rollback)

ExcelからAccessへ大量のデータをインポートする際、「もし処理の途中でエラーが発生したらどうなるのか？」と考えたことはありますか？通常、エラーが起きた時点で処理は中断され、データが中途半端に登録された「壊れた」状態になってしまいます。

このような事態を防ぐための仕組みが**「トランザクション処理」**です。

これは、「一連の処理を一つの塊とみなし、**すべて成功した場合のみ、その結果をデータベースに確定（コミット）**し、一つでも失敗した場合は、すべての処理を無かったことにする（ロールバック）」という、データの整合性を保つための非常に重要な機能です。

この記事では、DAOを使ってこのトランザクション処理をVBAで実装する方法を解説します。

---

【重要】実行前の参照設定

DAOの機能、特にWorkspaceオブジェクトなどを扱うには、事前にVBEの ツール > 参照設定 から**「Microsoft Office XX.0 Access database engine Object Library」**にチェックを入れてください。

---

完成したVBAコード

以下が、Excelの複数行のデータを、100件ずつのバッチでトランザクション処理しながらAccessのテーブルに追加するVBAコードです。

'参照設定: Microsoft Office XX.0 Access database engine Object Library
Sub ImportDataWithTransaction()

    ' 変数を宣言します
    Dim ws As DAO.Workspace
    Dim db As DAO.Database
    Dim rs As DAO.Recordset
    Dim sourceRange As Range
    Dim dbPath As String
    Dim commitInterval As Long
    Dim i As Long

    ' --- 1. オブジェクトと変数の設定 ---
    ' データベースのパス
    dbPath = ThisWorkbook.Path & "\SalesDB.accdb"
    ' Excelのデータ範囲
    Set sourceRange = ThisWorkbook.Worksheets("Sheet1").Range("A2:C501")
    ' コミットする間隔（レコード数）
    commitInterval = 100
    
    ' エラーが発生した場合は「ErrorHandler」セクションにジャンプ
    On Error GoTo ErrorHandler
    
    ' --- 2. データベースに接続し、トランザクションを開始 ---
    ' デフォルトのワークスペースを取得
    Set ws = DBEngine.Workspaces(0)
    ' データベースを開く
    Set db = ws.OpenDatabase(dbPath)
    ' 操作対象のテーブルを開く
    Set rs = db.OpenRecordset("T_Sales_Log")
    
    ' トランザクションを開始
    ws.BeginTrans
    
    ' --- 3. ループでデータを追加 ---
    For i = 1 To sourceRange.Rows.Count
        rs.AddNew
        rs!SalesID = sourceRange.Cells(i, 1).Value
        rs!ProductName = sourceRange.Cells(i, 2).Value
        rs!Quantity = sourceRange.Cells(i, 3).Value
        rs.Update
        
        ' 指定した件数ごとにコミット（バッチ処理）
        If i Mod commitInterval = 0 Then
            ws.CommitTrans ' 現在のトランザクションを確定
            ws.BeginTrans  ' 次のトランザクションを開始
        End If
    Next i
    
    ' --- 4. 正常終了時の処理 ---
    ' 端数のレコードが残っていれば、最後のトランザクションを確定
    ws.CommitTrans
    
    MsgBox "すべてのデータが正常に書き込まれました。"
    
    ' 正常終了なので、エラーハンドラを飛び越して終了
    GoTo CleanUp

ErrorHandler:
    ' --- 5. エラー発生時の処理 ---
    ' トランザクションをロールバック（処理を無かったことにする）
    If Not ws Is Nothing Then ws.Rollback
    
    ' エラー内容をメッセージで表示
    MsgBox "エラーが発生したため、処理を中断しロールバックしました。" & vbCrLf & _
           "エラー発生行: " & i + 1 & "行目" & vbCrLf & _
           Err.Description
           
    ' エラーが発生したExcelのセルを選択
    Application.Goto sourceRange.Rows(i)

CleanUp:
    ' --- 6. 後片付け ---
    If Not rs Is Nothing Then rs.Close
    If Not db Is Nothing Then db.Close
    
    Set rs = Nothing
    Set db = Nothing
    Set ws = Nothing

End Sub

---

コードのポイント解説

① Workspaceの取得とトランザクションの開始

Set ws = DBEngine.Workspaces(0)
ws.BeginTrans

トランザクションは、データベースのセッションを管理するWorkspaceオブジェクトを通じて制御します。DBEngine.Workspaces(0)でデフォルトのWorkspaceを取得し、その.BeginTransメソッドを呼び出すことでトランザクションが開始されます。これ以降のデータ操作は、CommitTransされるまで一時的な状態となります。

② エラー処理の仕組み (On Error GoTo)

On Error GoTo ErrorHandler
' ... 正常な処理 ...
GoTo CleanUp

ErrorHandler:
    ' ... エラー時の処理 ...
    ws.Rollback

CleanUp:
    ' ... 後片付け ...

On Error GoToで、もしコードの実行中に何かエラーが発生した場合、プログラムの流れをErrorHandler:ラベルが付いた場所に強制的にジャンプさせます。正常に処理が完了した場合は、GoTo CleanUpでエラー処理部分を飛び越します。これにより、成功時と失敗時の処理を明確に分離できます。

③ バッチ処理（分割コミット）

If i Mod commitInterval = 0 Then
    ws.CommitTrans
    ws.BeginTrans
End If

数千、数万件のデータを一つの巨大なトランザクションで処理すると、メモリを大量に消費しパフォーマンスが低下することがあります。そこで、commitInterval（ここでは100件）ごとに処理を区切り、CommitTransでこまめにデータベースに内容を確定させています。コミット後、すぐに.BeginTransで次のトランザクションを開始するのがポイントです。

④ エラー発生時のロールバック処理

ErrorHandler:
    ws.Rollback
    MsgBox "..."

エラーハンドラのセクションでは、まず.Rollbackメソッドを呼び出します。これにより、直前のBeginTrans以降に行われた全てのDB操作が取り消され、データベースはクリーンな状態に保たれます。その後、ユーザーにエラー箇所を知らせることで、原因の特定を助けます。

---

まとめ

データの整合性を保証するために、トランザクション処理はデータベースを扱うアプリケーションにとって不可欠な機能です。

• **Workspace**オブジェクトでトランザクションを管理する。
• 処理の塊を**BeginTransとCommitTrans**で囲む。
• **On Error GoToでエラーを検知し、エラーハンドラでRollback**を実行する。

この「All or Nothing（全か無か）」の考え方をVBAで実装することで、より安全で信頼性の高いデータ登録処理を構築できます。