Access VBAを掌握する極限の知見：QueryDef競合制御の要塞化
マルチユーザー環境のAccessデータベースにおいて、最も開発者を絶望させる現象の一つが「書き込み競合（Write Conflict）」だ。
特に、DAOの`QueryDef`を用いた一括更新や、UIからの非同期なデータ操作が交錯するエンタープライズ領域では、楽観的同時実行制御の限界が牙をむく。エラー番号「3197」（データが他のユーザーに変更されました）や「3218」（テーブルがロックされています）は、何も対策を講じなければ、一瞬にして業務アプリケーションの信頼性を崩壊させる。
本稿では、一般的な「エラーが出たらメッセージを出して終了」という安易な実装を排し、Windows APIとDAOのライフサイクルを完全に掌握した上で、「書き込み競合を自動検知し、ミリ秒単位のバックオフ制御で極限までリトライする堅牢な実行エンジン」の構築手法を解説する。
—
1. DAOの挙動と競合エラーのメカニズム
Accessの基盤であるJet/ACEデータベースエンジンは、ページ単位およびレコード単位でのロック機構を持っている。`QueryDef.Execute`メソッドを用いたアクションクエリ（更新・追加・削除）の実行時、対象レコードが他セッションに占有されている場合、エンジンは即座に例外をスローする。
ここで重要なのは、「競合の多くは一時的なものであり、数ミリ秒〜数秒待機して再実行すれば成功する」という事実だ。
人間が手動でリトライボタンを押す必要などない。システムが裏側でトランザクションとロックの衝突をいなし、トランザクションの整合性を保ったまま自動完結させるアーキテクチャこそが、プロフェッショナルの実装である。
—
2. 堅牢なリトライエンジンの設計要件
プロダクション環境に耐えうるリトライ機構には、以下の要件が課せられる。
1. 特定エラーコードの厳密な捕捉: すべてのDAOエラーをリトライ対象にしてはならない。デッドロック（エラー3314など）やロック競合に関連する特定のエラー番号のみをフィルタリングする。
2. 指数バックオフ（Exponential Backoff）とジッター（Jitter）: リトライ間隔を固定にすると、複数クライアントが同時に再試行を行って再び衝突（ライブロック）を起こす。待機時間を段階的に延ばしつつ、ランダムな揺らぎを与えることで競合確率を劇的に下げる。
3. APIによる高精度な待機: VBAの標準関数である`Sleep`ではなく、`Kernel32.dll`の`Sleep`を呼び出すことで、CPUリソースを無駄に消費せずに正確なミリ秒単位のウェイトを実現する。
4. オブジェクトの完全な解放: リトライループ内でのDAOオブジェクト（`QueryDef`, `Database`）のリークは、メモリ断片化やハンドルの枯渇を招く。明示的な`.Close`と`Set … = Nothing`の徹底。
—
3. 実装コード：自律回復型 QueryDef 実行エンジン
以下に、実務の最前線で即座に利用可能な、極限まで最適化されたモジュールコードを提示する。
Option Compare Database
Option Explicit
‘ ==============================================================================
‘ módulo: basQueryDefExecutor
‘ 概要: 書き込み競合を自動検知・リトライする堅牢なQueryDef実行エンジン
‘ ==============================================================================
‘ Windows API: 高精度なミリ秒単位のウェイト処理用
If VBA7 Then
Private Declare PtrSafe Sub Sleep Lib “kernel32” (ByVal dwMilliseconds As Long)
Else
Private Declare Sub Sleep Lib “kernel32” (ByVal dwMilliseconds As Long)
End If
‘ DAOエラー定数
Private Const ERR_WRITE_CONFLICT As Long = 3197
Private Const ERR_NETWORK_UPDATE As Long = 3043
Private Const ERR_TABLE_LOCKED As Long = 3218
Private Const ERR_LOCK_VIOLATION As Long = 3260
‘ リトライ設定定数
Private Const MAX_RETRIES As Long = 5 ‘ 最大リトライ回数
Private Const BASE_DELAY_MS As Long = 200 ‘ 基本待機時間 (ミリ秒)
‘ ==============================================================================
‘ 担当者向けパブリックメソッド: パラメータ付きQueryDefの安全実行
‘ ==============================================================================
Public Function ExecuteQueryDefSafely(ByVal qdfName As String, ParamArray queryParams() As Variant) As Boolean
Dim db As DAO.Database
Dim qdf As DAO.QueryDef
Dim i As Long
Dim attempt As Long
Dim delay As Long
Dim paramVal As Variant
ExecuteQueryDefSafely = False
Set db = CurrentDb()
On Error GoTo ErrorHandler
Set qdf = db.QueryDefs(qdfName)
‘ パラメータの動的バインディング
If UBound(queryParams) >= 0 Then
For i = LBound(queryParams) To UBound(queryParams)
If i <= qdf.Parameters.Count - 1 Then
qdf.Parameters(i).Value = queryParams(i)
End If
Next i
End If
' --- リトライループ開始 ---
attempt = 0
RetryLoop:
attempt = attempt + 1
' DBエンジンに処理を委譲（トランザクションはクエリ内部または呼び出し元で制御）
qdf.Execute dbFailOnError
' 成功
ExecuteQueryDefSafely = True
GoTo Cleanup
ErrorHandler:
' 競合系エラーであり、かつリトライ回数内である場合のみ再試行
If IsConflictError(Err.Number) And attempt <= MAX_RETRIES Then
' 指数バックオフ + ランダムジッター（競合の波を分散させる）
delay = (BASE_DELAY_MS (2 ^ (attempt - 1))) + (Int Rnd 100)
' ログ出力（必要に応じてファイルやテーブルへ記録）
Debug.Print "Warning: 書き込み競合を検知 (Error " & Err.Number & "). " & _
attempt & "回目のリトライを " & delay & "ms後に実行します [" & qdfName & "]"
Sleep delay
Resume RetryLoop
Else
' 致命的なエラー、またはリトライ上限オーバー
Dim errMsg As String
errMsg = "QueryDef execution failed [" & qdfName & "]." & vbCrLf & _
"Error: " & Err.Number & " - " & Err.Description & vbCrLf & _
"Attempts: " & (attempt - 1)
MsgBox errMsg, vbCritical, "致命的なデータベースエラー"
ExecuteQueryDefSafely = False
GoTo Cleanup
End If
Cleanup:
' オブジェクトのライフサイクル管理（メモリリークの完全防止）
On Error Resume Next
If Not qdf Is Nothing Then qdf.Close: Set qdf = Nothing
If Not db Is Nothing Then db.Close: Set db = Nothing
On Error GoTo 0
End Function
' ==============================================================================
' プライベートメソッド: 競合エラーの判定
' ==============================================================================
Private Function IsConflictError(ByVal errNum As Long) As Boolean
Select Case errNum
Case ERR_WRITE_CONFLICT, ERR_NETWORK_UPDATE, ERR_TABLE_LOCKED, ERR_LOCK_VIOLATION
IsConflictError = True
Case Else
IsConflictError = False
End Select
End Function
---
4. コードのアーキテクチャ的解説
1. 指数バックオフとジッターの数学的意義
`delay = (BASE_DELAY_MS (2 ^ (attempt – 1))) + (Int Rnd 100)` のロジックに注目してほしい。
リトライ間隔を $200\text{ms} \rightarrow 400\text{ms} \rightarrow 800\text{ms}\dots$ と倍増させることで、高負荷時のサーバーやネットワーク帯域の圧迫を防ぐ。さらに `Int Rnd 100`（ジッター）を加えることで、同時にロックを踏んだ複数のクライアント端末が、全く同じタイミングでリトライ要求を発行する「雷鳴効果（Thundering Herd Problem）」を巧みに回避している。
2. `dbFailOnError` との統合
アクションクエリ（`UPDATE`, `INSERT`, `DELETE`）を実行する際、`dbFailOnError` オプションの付与は必須である。これがないと、途中でエラーが発生しても部分的な更新がコミットされ、データの不整合（ゴミデータ）がデータベースに残る。本エンジンでは、例外が発生した際にトランザクション全体のロールバックと自動リトライが安全に行われる構造を担保している。
3. オブジェクトのライフサイクルと解放の規律
VBAのメモリ管理は参照カウンタ方式でありながら、`.Close`を怠るとCOMコンポーネントの内部キャッシュやロックがセッション終了まで解放されないケースがある。とりわけループ構造やエラーハンドリング内では、`On Error Resume Next`を挟みつつ確実に`Set … = Nothing`を実行し、Access特有の「メモリ肥大化現象（Bloat）」を抑制する設計としている。
—
5. チーフアーキテクトからの提言
Accessは「簡易的なデータベース」と揶揄されることがあるが、それは使い手側の設計が稚拙な場合に限る。適切なロック制御、APIの活用、そして今回解説したような堅牢なエラーリカバリ機構を組み込むことで、数十名規模の同時接続環境であっても、基幹系システムに匹敵する安定稼働を実現することが可能だ。
「エラーが出たら止まるシステム」から「障害を自己修復する自律型システムへ」。
このコードをあなたのアーキテクチャに組み込み、レガシーの枠を超えた真の堅牢性を手に入れてほしい。
