共有環境における「QueryDefの呪縛」を解く――動的SQLの生存戦略
Accessを大規模な共有データベースのフロントエンドとして運用する際、多くのエンジニアが「QueryDefの書き換え」という禁じ手に手を染め、そして沈没していく。
`CurrentDb.QueryDefs(“qMyQuery”).SQL = “…”`
このコードが実行された瞬間、バックエンドの`.accdb`（または`.mdb`）には共有ロックの嵐が吹き荒れる。マルチユーザー環境下では、Aさんがクエリを書き換えている最中にBさんが同じクエリを参照すれば、あえなく「ファイルが使用中です」という無慈悲なエラーが返される。これを防ぐために`On Error Resume Next`で誤魔化すのは、エンジニアとしての敗北だ。
今日は、共有環境で「動的SQL」を安全かつ高速に走らせるための、アーキテクトレベルの戦術を伝授する。
—
1. 永続クエリを汚すな：一時クエリ生成の極意
共有環境でQueryDefを書き換えてはならない。これが大原則だ。代わりに、実行のたびに「使い捨てのQueryDef」を動的に生成し、処理終了後に即座に破棄する設計を採用する。
ここで重要なのは、「クエリ名の一意性」の確保だ。クライアントPC名、ユーザーID、そしてシステムタイマーを組み合わせたハッシュ値を生成し、それをクエリ名にする。
実行戦略コード
Public Sub ExecuteDynamicQuery(ByVal sql As String)
Dim db As DAO.Database
Dim qdf As DAO.QueryDef
Dim queryName As String
‘ 一意な一時クエリ名を生成（PC名_ユーザー_ミリ秒）
queryName = “tmp_” & Environ(“COMPUTERNAME”) & “_” & Replace(Timer 100, “.”, “”)
Set db = CurrentDb
‘ クエリを動的に生成して実行
Set qdf = db.CreateQueryDef(queryName, sql)
‘ ここでクエリを実行（例：レコードセットを開く、またはActionクエリ）
‘ qdf.Execute dbFailOnError
‘ 後始末（重要：即座に削除してメタデータを解放する）
db.QueryDefs.Delete queryName
‘ オブジェクトの明示的解放
Set qdf = Nothing
Set db = Nothing
End Sub
このアプローチにより、他のユーザーと物理的にクエリ名が衝突することは理論上なくなる。
—
2. メモリとオブジェクトの生存期間（ライフサイクル）
VBAはガベージコレクションが甘い。特に`DAO.Database`オブジェクトを`CurrentDb`経由で乱用すると、内部キャッシュが肥大化し、メモリリークの温床となる。
- DAO.Databaseのキャッシュ: `CurrentDb`は呼び出すたびに新しいインスタンスを生成する可能性がある。大規模処理では、最初に`Dim db As DAO.Database: Set db = CurrentDb`と明示的に参照を保持し、使い回せ。
- 明示的解放: `Set qdf = Nothing`は義務だ。これを怠れば、Access内部のオブジェクトスタックにゴミが残り、クエリのコンパイル時間が指数関数的に増大する。
—
3. パラメータークエリへの回帰と最適化
動的SQLで文字列連結を行うのは、SQLインジェクションのリスクだけでなく、「実行プランの再構築コスト」というパフォーマンス上の損失が大きい。
クエリ名を変える手法（上記）は安全だが、SQL構造が同じなら、パラメータークエリを定義しておき、値だけを注入する方が実行効率は高い。
‘ パラメータークエリの効率的な実行例
Public Sub ExecuteParameterized(ByVal paramValue As String)
Dim db As DAO.Database
Dim qdf As DAO.QueryDef
Set db = CurrentDb
Set qdf = db.QueryDefs(“qry_Predefined_Search”)
‘ パラメーターの型を明示的に指定（暗黙の型変換を防ぐ）
qdf.Parameters(0).Value = paramValue
‘ 実行
qdf.Execute dbFailOnError
Set qdf = Nothing
Set db = Nothing
End Sub
—
4. レガシー環境における「Windows API」の役割
どうしても共有ファイルへのアクセスでデッドロックが発生する場合、Windows APIを使用して「セマフォ（Semaphore）」を実装するのも一つの手だ。`CreateSemaphore`を用いて、クリティカルセクションを強制的に一つに絞る。
しかし、これは「Accessの設計が破綻している」という警告でもある。APIでの制御が必要なレベルに達しているなら、それはAccessを「共有ファイルサーバー」としてではなく、「SQL Server等のRDBMSのフロントエンド」として再構築すべきタイミングだ。
—
チーフアーキテクトからの提言
Accessは、正しく扱えば強力なRADツールだが、甘い設計はマルチユーザー環境で必ず破綻する。
1. 「動的SQLの書き換え」は悪であると認識せよ。
2. 一時的なオブジェクト生成と即時削除のサイクルを徹底せよ。
3. パフォーマンスのボトルネックをSQL解析のコストに求めよ。
技術を使いこなすのではない。技術の限界を理解し、その制約の中で最も美しいアーキテクチャを描くこと。それが真の自動化エンジニアの姿だ。コードの行数ではなく、背後にあるメモリとロックの挙動を想像しろ。それができれば、どんなレガシーシステムもあなたの意のままだ。
