Access VBAの深淵：動的クエリ生成で「負債」を生まないためのアーキテクチャ
現場でよく見る「動的クエリ」の地獄絵図をご存知だろうか。
フォームの入力値をひたすら`If`文で連結し、巨大なSQL文字列を生成して`DoCmd.RunSQL`に投げ込む……。そんなコードは、保守フェーズに入った瞬間に「エンジニアの墓場」と化す。
今日は、Access VBAを使いこなし、堅牢で拡張性の高い「フォーム連携型動的クエリ」を実装するための、プロフェッショナルな設計思想を伝授する。
—
1. なぜ「文字列連結」は悪手なのか
多くの初心者は、以下のように書く。
`strSQL = “SELECT FROM T_Sales WHERE ID = ” & Me.txtID & ” AND Date = #” & Me.txtDate & “#”`
これの何が問題か？
- 型変換の罠: 日付のフォーマットやNULLの扱いで必ずバグる。
- SQLインジェクションのリスク: 内部ツールとはいえ、悪意ある入力値や予期せぬ文字でクエリが破壊される。
- 保守性の欠如: 複雑な条件が増えた瞬間、パズルを解くようなデバッグ作業が待っている。
我々が目指すべきは、「条件抽出のロジック」と「SQLの生成」を分離し、データ型を厳格に制御する設計だ。
—
2. 堅牢な実装：`Collection`オブジェクトを使った動的クエリ生成
複数の条件を管理するために、文字列を直接繋ぐのではなく、`Collection`に抽出条件（WHERE句の断片）を格納していく手法を推奨する。これにより、クエリの最後に`Join`関数で`AND`を挟んで結合するだけで済む。
プロダクションコード例
‘ フォーム連携型クエリ生成のテンプレート
Public Function GetDynamicSQL() As String
Dim filters As New Collection
Dim strSQL As String
‘ 1. 条件の収集（ここで型変換とバリデーションを完結させる）
If Not IsNull(Me.txtCustomerName) Then
filters.Add “CustomerName LIKE ‘” & Replace(Me.txtCustomerName, “‘”, “””) & “‘”
End If
If Not IsNull(Me.cmbStatus) Then
filters.Add “StatusID = ” & Me.cmbStatus
End If
If Not IsNull(Me.txtStartDate) Then
‘ 日付は必ず定数フォーマットに変換する
filters.Add “OrderDate >= #” & Format(Me.txtStartDate, “yyyy/mm/dd”) & “#”
End If
‘ 2. SQLの構築
strSQL = “SELECT FROM T_Sales”
If filters.Count > 0 Then
‘ コレクションをANDで連結してWHERE句を生成
strSQL = strSQL & ” WHERE ” & JoinCollection(filters, ” AND “)
End If
GetDynamicSQL = strSQL
End Function
‘ 汎用ヘルパー：コレクションを連結する
Private Function JoinCollection(col As Collection, delimiter As String) As String
Dim i As Long
Dim result As String
For i = 1 To col.Count
result = result & col(i) & IIf(i < col.Count, delimiter, "")
Next i
JoinCollection = result
End Function
---
3. 実行のベストプラクティス：QueryDefを活用せよ
動的なSQLを都度実行する際、`DoCmd.RunSQL`に頼るべきではない。再利用性とパフォーマンス、そして「クエリ定義」としての可視性を確保するために、`QueryDef`オブジェクトを操作する。
なぜQueryDefなのか？
1. 実行計画の最適化: Accessエンジンは保存されたクエリの実行計画を保持できる。
2. デバッグの容易さ: フォーム上の「クエリを表示する」ボタンを作るだけで、現在走っているSQLを即座に確認できる。
Public Sub ApplyFilterToQuery()
Dim db As DAO.Database
Dim qdf As DAO.QueryDef
Set db = CurrentDb
‘ 一時的なクエリ定義（保存用ではなく実行用）
Set qdf = db.QueryDefs(“qry_DynamicFilter”)
‘ SQLを書き換えて保存
qdf.SQL = GetDynamicSQL()
‘ フォームのレコードソースに適用
Me.subForm.Form.RecordSource = “qry_DynamicFilter”
Set qdf = Nothing
Set db = Nothing
End Sub
—
4. チーフアーキテクトからの助言：見えないリスクを制御せよ
この設計において、最後に注意すべきは「NULL」と「空白」の境界線だ。
Accessのフォームにおいて、`Null`と`””`（長さゼロの文字列）は別物として扱われることがある。`IsNull()`だけでなく、`Len(Trim(Me.txtControl)) > 0` を組み合わせるのが最も安全だ。
また、大規模なデータセットを扱う場合は、`QueryDef`のSQLを書き換える前に、必ずインデックスが適切に張られているか確認すること。どれほど洗練されたVBAコードも、物理的なインデックスがなければゴミ同然の速度しか出ない。
まとめ：次に進むために
1. 文字列連結によるSQL構築は禁止。`Collection`を用いたリスト管理に移行せよ。
2. SQL構築と実行を分断せよ。`QueryDef`を経由することで、保守性は劇的に向上する。
3. 型変換を疎かにするな。日付、数値、文字列（エスケープ処理）のルールは関数化して使い回せ。
このアーキテクチャを導入すれば、君のツールは「動く」だけのものから、誰が触っても壊れない「プロダクト」へと進化する。健闘を祈る。
