Access VBAのトランザクション処理｜BeginTransで整合性を守る

結論：BeginTrans / CommitTrans / Rollback をWorkspaceに対して呼ぶのが基本

Access VBAでトランザクションを使うには、DAOのWorkspaceオブジェクトに対してBeginTrans・CommitTrans・Rollbackの3メソッドを呼び出します。WorkspaceはDBEngine(0)またはDBEngine.Workspaces(0)で取得できます。

使いどころは「複数テーブルの更新を全部成功させるか、全部元に戻すか」が必要な処理です。受注登録と在庫引当を同時に行うケース、送金元の減算と送金先の加算を対で実行するケースなどがその典型です。途中でエラーが起きた場合、Rollbackを呼べばBeginTrans以降のすべての変更が取り消されます。

Microsoftの公式ドキュメント（Workspace.BeginTrans method (DAO)）には「データ整合性を保つため、2つ以上のテーブルの変更が全部完了するか全部取り消されるかを保証したい場合に使う」と明記されています。ただし、ACEファイルをファイルサーバー上で使う場合、サーバーが停止するなど極端な状況では変更が中途半端な状態で残る可能性があります。公式ドキュメント（Use transactions in a DAO Recordset）にも「耐久性を含む真のトランザクション保証が必要な場合はクライアント/サーバー構成を検討すること」と記載があります。DAOのBeginTransはアプリケーションロジックの整合性を守るものとして活用し、DBレベルの完全な耐久性が要件に含まれる場合はSQL Serverへの移行を検討します。

3メソッドの役割と基本コード

まずメソッドの役割を整理します。

基本的な構造を示します。在庫引当テーブルと受注テーブルを同時に更新する例です。

Sub RegisterOrder(orderId As Long, productId As Long, qty As Long)
    Dim wrk As DAO.Workspace
    Dim db As DAO.Database
    Dim inTransaction As Boolean

    Set wrk = DBEngine(0)
    Set db = CurrentDb()
    inTransaction = False

    ' 事前検証（BeginTrans前に行う）
    If qty <= 0 Then
        MsgBox "数量は1以上を指定してください。", vbExclamation, "入力エラー"
        Exit Sub
    End If

    On Error GoTo ErrHandler

    wrk.BeginTrans
    inTransaction = True

    ' 在庫を引き当てる（在庫数 >= qty の条件を付けて在庫不足も防ぐ）
    db.Execute "UPDATE 在庫テーブル " & _
               "SET 在庫数 = 在庫数 - " & qty & " " & _
               "WHERE 商品ID = " & productId & " AND 在庫数 >= " & qty, dbFailOnError

    ' 更新件数が1件でない場合はエラーとして扱う
    ' （0件=在庫不足または商品IDなし、2件以上=商品IDが重複している）
    If db.RecordsAffected <> 1 Then
        Err.Raise vbObjectError + 1, , "在庫引当に失敗しました（在庫不足または商品ID不正）。"
    End If

    ' 受注レコードを登録する
    db.Execute "INSERT INTO 受注テーブル (受注ID, 商品ID, 数量, 受注日) " & _
               "VALUES (" & orderId & ", " & productId & ", " & qty & ", Date())", _
               dbFailOnError

    ' 両方成功したら確定
    wrk.CommitTrans
    inTransaction = False

    Exit Sub

ErrHandler:
    ' BeginTransが実行済みの場合のみRollbackを呼ぶ
    If inTransaction Then
        wrk.Rollback
        inTransaction = False
    End If
    MsgBox "登録に失敗しました。処理を元に戻しました。" & vbCrLf & _
           "エラー番号: " & Err.Number & vbCrLf & Err.Description, _
           vbCritical, "エラー"
End Sub

dbFailOnErrorを付けることで、更新不能やロック失敗など実行時に検出できる失敗が起きたときにVBAのエラーとして捕捉できます。ただし、更新対象が0件の場合はエラーにならない点に注意が必要です。在庫テーブルに対象商品IDが存在しない場合や在庫数が不足している場合、UPDATEは0件のまま成功扱いになります。このため、WHERE句にAND 在庫数 >= qtyを加えて在庫不足を事前に除外したうえで、db.RecordsAffectedで更新件数が1件かどうかを確認します。1件でなければ（0件=在庫不足/商品IDなし、2件以上=商品IDが重複）Err.Raiseで明示的にエラーを起こすことでトランザクションをRollbackに誘導できます。

また、inTransactionフラグでBeginTransが実行済みかどうかを管理しています。BeginTransを呼ぶ前にエラーが起きた場合、フラグがFalseのままなのでRollbackを呼ばずに済みます。公式ドキュメントにあるとおり、BeginTransなしにRollbackを呼ぶとそれ自体がエラーになるためです。

エラー処理の基本（On Error文やErrオブジェクトの詳細）についてはAccess VBAのエラー処理入門で解説しているため、本記事ではトランザクション固有の話に絞ります。

トランザクションのスコープはWorkspace全体

公式ドキュメントに明記されている点ですが、見落とされやすいのでここで強調します。

BeginTransのスコープは、そのWorkspace内で開かれているすべてのデータベースに及びます。1つのWorkspaceで複数のDatabaseオブジェクトを開いている場合、CommitTransやRollbackはそのすべてに影響します。

' 2つのデータベースを同じWorkspaceで開いている場合
Dim wrk As DAO.Workspace
Dim dbMain As DAO.Database
Dim dbSub As DAO.Database

Set wrk = DBEngine(0)
Set dbMain = CurrentDb()
Set dbSub = wrk.OpenDatabase("C:\data\sub.accdb")

wrk.BeginTrans

' dbMainへの更新も、dbSubへの更新も、同じトランザクション対象
dbMain.Execute "UPDATE ...", dbFailOnError
dbSub.Execute "INSERT INTO ...", dbFailOnError

' Rollbackすると両方のDBへの変更が取り消される
wrk.Rollback

2つのデータベースにまたがる更新の整合性を取りたい場面では、これが便利です。ただし、同じWorkspaceで開いていることが前提です。CurrentDb()とwrk.OpenDatabase(...)で開いたデータベースが同じWorkspaceに属するかどうかは、Workspaceの取得方法によります。上記のようにDBEngine(0)で明示的にWorkspaceを取得してから操作する書き方が安全です。

注意点：ネスト・ロック・TEMP領域

実務で引っかかりやすい3点を整理します。

ネストトランザクションについて。ACEデータベース（.accdb）はトランザクションのネスト自体は可能です。ただし公式ドキュメントには「内側のトランザクションを解決してから、外側のトランザクションを解決しなければならない」と明記されています。内側をCommitTransして外側をRollbackした場合、内側のCommitTransは取り消されます。一方、ODBCデータソースをAccess DBエンジン経由で使う場合はネストが使えないと公式に記載があります。

ロックについて。トランザクション中は更新対象レコードやそれを含むページなどにロックがかかります（Accessのロック粒度はレコードレベルかページレベルかが設定・操作方法によって変わります）。複数人で同じAccessを使っている環境では、トランザクションが長く続くほど他のユーザーが書き込めない時間が延びます。「全部ひとつのトランザクションに入れたい」という発想は整合性の面では正しいのですが、業務データが多い場合は処理を小さい単位に分けることも考える必要があります。複数人利用時の競合問題はAccessが複数人で使うと遅い・壊れる原因でも触れています。

TEMP領域について。公式ドキュメントによると、ACEワークスペースではトランザクションログがTEMP環境変数で指定されたドライブに書き込まれます。TEMP領域が枯渇するとエンジンが実行時エラーを発生させ、その状態でCommitTransを呼んでも一部だけが確定して残りは失われる可能性があります。一方Rollbackはトランザクションログを解放してすべての変更を取り消せます。長時間・大量件数のトランザクションを扱う場合は、TEMPドライブの空き容量も事前に確認してください。

Recordsetを使うパターン

Executeメソッドではなく、Recordsetオブジェクトで1件ずつ更新する処理にもトランザクションを使えます。

Sub UpdateStatusBatch()
    Dim wrk As DAO.Workspace
    Dim db As DAO.Database
    Dim rs As DAO.Recordset
    Dim inTransaction As Boolean

    Set wrk = DBEngine.Workspaces(0)
    Set db = CurrentDb()
    Set rs = db.OpenRecordset("SELECT * FROM 受注テーブル WHERE 処理済み = False", _
                               dbOpenDynaset)
    inTransaction = False

    On Error GoTo ErrHandler

    wrk.BeginTrans
    inTransaction = True

    Do Until rs.EOF
        rs.Edit
        rs!処理済み = True
        rs!処理日時 = Now()
        rs.Update
        rs.MoveNext
    Loop

    wrk.CommitTrans
    inTransaction = False
    MsgBox "更新が完了しました。"

    rs.Close
    Exit Sub

ErrHandler:
    If inTransaction Then
        wrk.Rollback
        inTransaction = False
    End If
    If Not rs Is Nothing Then rs.Close
    MsgBox "更新中にエラーが発生しました。変更を元に戻しました。" & vbCrLf & _
           Err.Description, vbCritical, "エラー"
End Sub

この書き方では、ループ中に1件でもエラーが起きた場合にRollbackが走り、そこまでに更新したすべてのレコードが元に戻ります。Recordsetを使う場合も、Workspaceに対するトランザクションが有効である点は変わりません。

なお、公式ドキュメントには「Recordsetがトランザクションをサポートするかどうかは、Database.TransactionsプロパティまたはRecordset.Transactionsプロパティで確認できる」とあります。ACEテーブルに基づくDynaset型またはTable型のRecordsetではトランザクションを利用できます。一方、Snapshot型やForward-only型はACEテーブルが元でもTransactions = Falseになります。外部ISAM（ParadoxやdBASEのファイル）をリンクしているRecordsetもFalseになる場合があります。

SQL Server移行時の注意

バックエンドをSQL Serverへ移行した後は、DAOのトランザクションメソッドの扱いが変わります。AccessのフォームはそのままでバックエンドだけSQL Serverへ移す構成（アップサイジング）についてはAccessの画面はそのまま、データだけSQL Serverへ移す構成で解説していますが、トランザクション面でも注意が必要です。

SQL Serverにリンクテーブルで接続している場合、ODBCを介したトランザクションになります。この場合、公式ドキュメントには「Access DBエンジンを通じてODBCデータソースにアクセスする場合、ネストトランザクションは使えない」と記載があります。なお、公式ドキュメントの「CommitTrans後にRecordsetのカーソルが無効になる場合がある」という記述は旧ODBCDirectワークスペース（Access 2013以降は非サポート）に関するもので、通常のAccessワークスペースからSQL Serverリンクテーブルを操作する場合にそのまま当てはまるわけではありません。

SQL Server側でトランザクションを完結させたい場合は、ADOのConnection.BeginTransを使う方法が確実です。パススルークエリでSQL ServerのBEGIN TRANSACTION以降を1つのバッチまたはストアドプロシージャとして発行する方法もありますが、複数回のパススルー発行をまたいでトランザクションを保持させるには接続の管理が難しくなります。当社の実務では、Access側のDAOトランザクションからSQL Server側のトランザクションへ移行する際はADOへの切り替えを検討することを勧めています。

今のAccessにどれだけ複雑なVBAやトランザクション処理が含まれているか把握したい場合は、無料の解析可否チェックから状況を整理することもできます。

よくある質問

Q. BeginTransの前にエラーが起きた場合、Rollbackを呼んでも問題ありませんか。

問題があります。公式ドキュメントには「BeginTransを呼ばずにCommitTransまたはRollbackを呼ぶとエラーになる」と明記されています。このため、エラーハンドラで無条件にRollbackを呼ぶのではなく、フラグ変数でBeginTransが実行済みかどうか管理する書き方が安全です。あるいはBeginTransの直後にOn Error GoTo ErrHandlerを置き、BeginTrans前の前処理とBeginTrans以降を明確に分けることで、RollbackのタイミングをBeginTrans後に限定する設計にします。

Q. CommitTransの後にエラーが発生した場合、変更を取り消せますか。

CommitTrans後の変更は取り消せません。公式ドキュメントに「CommitTransを使った後は、そのトランザクション内の変更を元に戻すことはできない（別のトランザクションにネストされており、その上位トランザクションがRollbackされる場合を除く）」と明記されています。CommitTrans後の後処理でエラーが起きた場合は別途対応が必要です。たとえば、後処理の結果が失敗したことをテーブルに記録しておき、後から補正処理を走らせる設計が一般的です。

Q. CurrentDbで取得したデータベースに対してトランザクションは使えますか。

使えます。CurrentDb()はデフォルトWorkspace（DBEngine(0)）に属するデータベースを返すため、DBEngine(0).BeginTransを呼べばCurrentDbへの変更も対象になります。CurrentDb()は呼び出すたびに新しいDatabaseオブジェクトを返しますが、同じデフォルトWorkspaceのトランザクションに参加するため、整合性の問題はありません。ただしdb.RecordsAffectedを参照する際など、Executeに使用したDatabase変数を使い続けることで可読性が上がります。また、DBEngine(0)とDBEngine.Workspaces(0)は同じWorkspaceを指しています。

Q. トランザクション中にWorkspaceを閉じたらどうなりますか。

公式ドキュメントによると、未解決のトランザクションが残った状態でWorkspaceを閉じると、トランザクションは自動的にロールバックされます。ただしデフォルトWorkspace（DBEngine(0)）はプロシージャの変数スコープとは独立して存続するため、プロシージャが終了するだけでは自動ロールバックは起きません。CommitTransもRollbackも呼ばずにプロシージャが終了した場合、Workspaceが閉じられるまでトランザクションは未解決のまま残り、その間ロックも保持されます。確定したい場合は必ずCommitTransを、取り消したい場合はRollbackを明示的に呼ぶ必要があります。