Access VBAの「クエリ肥大化」を根絶せよ：QueryDefを支配するアーキテクトの流儀
Accessで業務ツールを開発していると、必ず直面する「肥大化」という悪魔がいる。
「動的SQLを毎回生成して実行する」という安易な実装は、Accessの内部テーブルである`MSysObjects`をゴミの山に変え、データベースを物理的に破壊し、パフォーマンスを奈落へ突き落とす。
今回は、動的クエリを生成する際、「QueryDefを使い捨てず、戦略的に再利用・管理する」ための最高峰の設計手法を伝授する。
—
1. なぜ「動的SQLの直実行」が罪深いのか
多くの初心者は、`DoCmd.RunSQL` や `CurrentDb.Execute` に文字列としてSQLを直接流し込む。
しかし、大規模なツールや、複雑な条件分岐を持つ帳票システムでこれをやると、以下の弊害が起きる。
- MSysObjectsの断片化: 一時的なクエリを大量生成・破棄すると、データベースの内部インデックスが著しく劣化する。
- コンパイルコスト: 毎回SQLを解析し、実行プランを立て直すため、CPUリソースが無駄に浪費される。
- 保守不能: デバッグ時に「今どのSQLが走っているのか」を追跡できない。
結論：QueryDefは「使い捨て」ではなく「テンプレート」として管理せよ。
—
2. 堅牢なQueryDef管理の設計思想
私が推奨するのは、「固定のQueryDefオブジェクトを1つ用意し、中身のSQLだけを書き換えて再利用する」という設計だ。
以下のコードは、単なるコピペ用コードではない。実務で求められる「排他制御」と「エラーハンドリング」を網羅した、プロダクション品質のテンプレートである。
実装コード：クエリを再利用する最適解
‘ @brief 既存のQueryDefを再利用し、動的SQLを安全に流し込むクラスメソッド的関数
‘ @param strQueryName 対象のQueryDef名
‘ @param strSQL 実行したい動的SQL
Public Sub UpdateAndExecuteQuery(ByVal strQueryName As String, ByVal strSQL As String)
Dim db As DAO.Database
Dim qdf As DAO.QueryDef
Set db = CurrentDb
On Error GoTo ErrorHandler
‘ 1. QueryDefが存在するか確認し、なければ作成する（初回のみ）
If Not ExistsQueryDef(strQueryName) Then
Set qdf = db.CreateQueryDef(strQueryName, strSQL)
Else
‘ 2. 既存のQueryDefを再利用してSQLを上書き
Set qdf = db.QueryDefs(strQueryName)
qdf.SQL = strSQL
End If
‘ 3. パラメータの自動解決（必要に応じて）
‘ qdf.Parameters(0).Value = …
‘ 4. クエリ実行
qdf.Execute dbFailOnError
‘ 後処理
qdf.Close
Set qdf = Nothing
Exit Sub
ErrorHandler:
MsgBox “クエリ実行中に致命的なエラーが発生しました: ” & Err.Description, vbCritical
If Not qdf Is Nothing Then qdf.Close
End Sub
‘ QueryDefの存在チェック
Private Function ExistsQueryDef(ByVal strName As String) As Boolean
Dim qdf As DAO.QueryDef
For Each qdf In CurrentDb.QueryDefs
If qdf.Name = strName Then
ExistsQueryDef = True
Exit Function
End If
Next qdf
ExistsQueryDef = False
End Function
—
3. なぜこの設計が「最強」なのか
① 物理的な肥大化の停止
この手法では、`CREATE QUERY`を繰り返さない。`qdf.SQL`プロパティを書き換えるだけで、Accessは内部的にインデックスを再構築するため、`.accdb`ファイルが肥大化する最大の要因を排除できる。
② 明確な責務分離
動的SQLの生成ロジックと、それを実行するエンジンを分離している。これにより、SQL生成にバグがあっても、実行エンジン側で必ずトラップできる。
③ トランザクションの整合性
`dbFailOnError`フラグをセットすることで、SQLに構文ミスやデータ型の不一致があった場合、自動的にロールバックがかかる。本番環境での「中途半端な更新」を防ぐための必須設定だ。
—
4. プロフェッショナルへの助言：さらなる高みへ
もし、あなたのシステムがさらに複雑で、「同時に複数のユーザーが同じQueryDefを叩く可能性がある」ならば、上記のアプローチに加えて「QueryDef名の動的生成（ユーザーIDやセッションIDを付与）」を検討せよ。
しかし、まずはこの「再利用テンプレート」を完璧に実装することから始めてほしい。
「動的なものを、いかに静的に管理するか」。これこそが、Accessアーキテクトが持つべき視座だ。
コードは単に動けばいいのではない。「数年後の自分が保守したとき、感謝されるか」。
その問いを常に持ち続けろ。諸君の健闘を祈る。
