Access VBAを掌握する極限の知見：動的SQL生成の終着点「クエリビルダークラス」の設計
レガシーシステムの迷宮と化したAccessデータベースにおいて、最も開発者を絶望させるのは、散在するフォームの入力値に基づき、無数の `If` 判定で継ぎ接ぎされた「スパゲッティSQL」の生成ロジックだ。
「もしチェックボックスAがTrueなら…」「いや、テキストBが空でないなら…」
このような場当たり的な文字列結合の果てにあるのは、SQLインジェクションの脆弱性、暗黙の型変換によるパフォーマンスの崩壊、そして保守不能に陥ったコードベースである。
私は長年、数多の基幹系Accessシステムを刷新・運用してきた。その中で到達した真理はただ一つ。「動的SQLの生成は、手続き型で書くべきではない。専用のビルダーオブジェクトにカプセル化せよ」ということだ。
今回は、QueryDefのライフサイクルを完全に制御し、メモリ効率と堅牢性を極限まで高めた「クエリビルダークラス」の設計思想と実装の全貌を公開する。
—
1. なぜ「文字列結合によるSQL生成」は悪なのか
多くの初中級プログラマは、以下のようなコードを書く。
‘ 【アンチパターン】絶対に書いてはならないコード
Dim sql As String
sql = “SELECT FROM T_Orders WHERE 1=1”
If Me.txtCustomerID <> “” Then
sql = sql & ” AND CustomerID = ‘” & Me.txtCustomerID & “‘”
End If
If Not IsNull(Me.cmbStatus) Then
sql = sql & ” AND Status = ” & Me.cmbStatus
End If
‘ …この後さらに数十行のIf文が続く
このアプローチには、致命的な欠陥が3つある。
1. 型の安全性の欠如: シングルクォートの閉じ忘れや、日付・文字列のエスケープ漏れによる構文エラー・SQLインジェクション。
2. パフォーマンスの劣化: SQL文の構造が毎回変わるため、Jet/ACEデータベースエンジンがクエリプランをキャッシュできず、実行計画の再計算コスト（Query Plan Compilation Cost）が常にかかり続ける。
3. 可読性と拡張性の死: 条件が増えるたびに結合子（`AND` / `OR`）の制御が破綻する。
これを解決するのが、「条件の断片をコレクションとして安全に蓄積し、実行直前に型安全なパラメータ付きクエリ（QueryDef）として昇華させる」というアーキテクチャである。
—
2. クエリビルダークラス（`QueryBuilder`）の設計
今回のアーキテクチャでは、単にSQL文字列を作るだけでなく、Accessの最大の武器である `QueryDef` オブジェクトとパラメータ（Parametersコレクション）を完全に連動させる。これにより、SQLインジェクションを根絶し、データベースエンジンに最適化された実行プランを強制する。
クラスモジュール名: `QueryBuilder`
以下のコードを、新規クラスモジュール `QueryBuilder` としてインポートしてほしい。
Option Explicit
‘ =========================================================================
‘ クラス名: QueryBuilder
‘ 概要: 複雑な抽出条件を動的に構築し、型安全なQueryDefを生成するクラス
‘ =========================================================================
Private m_BaseTable As String
Private m_SelectColumns As String
Private m_WhereClauses As Collection
Private m_Parameters As Collection
Private m_OrderBy As String
‘ — 内部構造体：パラメータ保持用 —
Private Type ParamInfo
Name As String
Value As Variant
DataType As DataTypeEnum
End Type
Private Sub Class_Initialize()
Set m_WhereClauses = New Collection
Set m_Parameters = New Collection
m_SelectColumns = “”
m_BaseTable = “”
m_OrderBy = “”
End Sub
Private Sub Class_Terminate()
‘ 循環参照を防ぐための明示的クリーンアップ
Set m_WhereClauses = Nothing
Set m_Parameters = Nothing
End Sub
‘ ————————————————————————-
‘ プロパティ設定
‘ ————————————————————————-
Public Property Let SelectColumns(ByVal Value As String)
m_SelectColumns = Value
End Property
Public Property Let BaseTable(ByVal Value As String)
m_BaseTable = Value
End Property
Public Property Let OrderBy(ByVal Value As String)
m_OrderBy = Value
End Property
‘ ————————————————————————-
‘ 条件追加メソッド（型安全なパラメータバインド）
‘ ————————————————————————-
Public Sub AddCondition(ByVal fieldName As String, ByVal op As String, ByVal value As Variant, ByVal dataType As DataTypeEnum)
If IsNull(value) Or (VarType(value) = vbString And value = “”) Then Exit Sub
Static paramCounter As Long
paramCounter = paramCounter + 1
Dim paramName As String
paramName = “@p_” & paramCounter & “_” & Replace(fieldName, “.”, “_”)
‘ WHERE句の断片を蓄積 (例: CustomerID = @p_1_CustomerID)
m_WhereClauses.Add fieldName & ” ” & op & ” ” & paramName
‘ パラメータ情報を蓄積
Dim p As ParamInfo
p.Name = paramName
p.Value = value
p.DataType = dataType
‘ 自作コレクションへ格納するため、ユーザー定義型を包むクラスは省略しバリアント配列で代用
Dim vParam(2) As Variant
vParam(0) = paramName
vParam(1) = value
vParam(2) = dataType
m_Parameters.Add vParam
End Sub
‘ ————————————————————————-
‘ SQL生成とQueryDefの構築（核心部分）
‘ ————————————————————————-
Public Function BuildQueryDef(ByVal queryName As String, Optional ByVal db As DAO.Database) As DAO.QueryDef
If m_BaseTable = “” Then Err.Raise 10001, “QueryBuilder”, “ベーステーブル/クエリが設定されていません。”
If db Is Nothing Then Set db = CurrentDb
‘ 1. 既存の同名一時クエリがあれば削除（QueryDefのライフサイクル管理）
On Error Resume Next
db.QueryDefs.Delete queryName
On Error GoTo 0
‘ 2. SQL文字列の組み立て
Dim sql As String
sql = “SELECT ” & m_SelectColumns & ” FROM ” & m_BaseTable
If m_WhereClauses.Count > 0 Then
Dim i As Long
sql = sql & ” WHERE ”
For i = 1 to m_WhereClauses.Count
If i > 1 Then sql = sql & ” AND ”
sql = sql & m_WhereClauses(i)
Next i
End If
If m_OrderBy <> “” Then
sql = sql & ” ORDER BY ” & m_OrderBy
End If
‘ 3. QueryDefオブジェクトの生成
Dim qdf As DAO.QueryDef
Set qdf = db.CreateQueryDef(queryName, sql)
‘ 4. パラメータのバインド（型と値の注入）
Dim varParam As Variant
For Each varParam In m_Parameters
Dim pName As String
Dim pVal As Variant
Dim pType As DataTypeEnum
pName = varParam(0)
pVal = varParam(1)
pType = varParam(2)
‘ 明示的にパラメータを定義し、値を設定
qdf.Parameters(pName).Type = pType
qdf.Parameters(pName).Value = pVal
Next varParam
Set BuildQueryDef = qdf
End Function
—
3. 呼び出し側の実装：極限まで洗練されたフォーム検索
上記で作成した `QueryBuilder` を利用するフォーム側のコードを見てほしい。無駄な `If` 文の嵐が嘘のように消え去り、宣言的で美しいコードに生まれ変わる。
Private Sub cmdSearch_Click()
On Error GoTo ErrorHandler
Dim qb As QueryBuilder
Set qb = New QueryBuilder
With qb
.BaseTable = “T_Orders”
.SelectColumns = “OrderID, CustomerID, OrderDate, TotalAmount”
.OrderBy = “OrderDate DESC”
‘ 画面の入力値から動的に条件を追加（空値はビルダー側で自動スキップされる）
‘ DAOのDataTypeEnumを使用することで型安全性を担保
.AddCondition “CustomerID”, “=”, Me.txtCustomerID, dbText
.AddCondition “OrderDate”, “>=”, Me.txtDateFrom, dbDate
.AddCondition “OrderDate”, “<=", Me.txtDateTo, dbDate
.AddCondition "Status", "=", Me.cmbStatus, dbInteger
End With
' 一時クエリとしてQueryDefを生成（名前を固定することでメモリリークを防ぐ）
Dim qdf As DAO.QueryDef
Set qdf = qb.BuildQueryDef("tmp_DynamicSearchQuery")
' サブフォームにクエリをバインド
Me.subGrid.Form.RecordSource = "tmp_DynamicSearchQuery"
Me.subGrid.Form.Requery
' クリーンアップ
Set qdf = Nothing
Set qb = Nothing
Exit Sub
ErrorHandler:
MsgBox "検索処理でエラーが発生しました: " & Err.Description, vbCritical, "システムエラー"
End Sub
---
4. シニアエンジニアが知るべき「メモリ最適化」と「QueryDefの罠」
Access/Jetエンジン環境下において、動的SQLの生成と実行は、メモリ管理の観点で地雷原を歩くようなものだ。シニアエンジニアとして知っておくべき極限の知見を共有する。
① `CurrentDb` の毎回呼び出しによるパフォーマンス低下とメモリリーク
コード中で `CurrentDb` メソッドを呼び出すたびに、DAOの新しいデータベースオブジェクトのインスタンスがヒープ上に生成される。これをループ内や頻繁に実行されるイベント内で乱用すると、Accessの内部メモリキャッシュが圧迫され、最悪の場合「リソース不足（Out of Memory）」エラーを引き起こす。
- 対策: `CurrentDb` は必要な局所で1回だけ変数に受け、使い終わったら明示的に破棄（`Set db = Nothing`）すること。上記のビルダーでは、オプションで外部から `DAO.Database` を渡せる設計にしている。
② 一時QueryDefの蓄積によるデータベース肥大化
`db.CreateQueryDef(“tmp_Name”, sql)` を実行すると、そのクエリ定義はAccessのシステムテーブル（MSysObjects等）に物理的に書き込まれる。これを毎回ユニークな名前（例: `tmp_20231024120000` など）で作成し続けると、データベースファイル（.accdb）が爆発的に肥大化し、ゴミデータ（ガベージ）が溜まってパフォーマンスが著しく劣化する。
- 対策: 本稿のコードのように、常に同一の固定名（例: `tmp_DynamicSearchQuery`）を使い回し、生成前に必ず既存の同名QueryDefを削除する設計にすることで、ファイルサイズ肥大化を完全に防止する。
③ 暗黙の型変換（Implicit Conversion）の排除
文字列結合でSQLを作ると、例えば日付型に対して `’2023/10/24’` のようにシングルクォートで囲むため、Jetエンジンはインデックスを無視してフルスキャン（全件走査）を行うことがある。
パラメータクエリ（`QueryDef.Parameters`）を使用することで、データベースエンジンは適切な型情報を保持したまま実行プランを構築するため、数万件以上のレコードを持つテーブルであっても、インデックスが正常に効き、爆速の検索パフォーマンスを維持できる。
—
総括
Access VBAは「おもちゃの言語」と揶揄されることがある。しかしそれは、書き手がリソースのライフサイクルやデータベースエンジンの挙動を理解せず、場当たり的なコードを書き続けてきた結果に過ぎない。
今回紹介した「クエリビルダークラス」は、レガシーなAccess環境であっても、モダンなオブジェクト指向設計と堅牢なセキュリティ、そして圧倒的なパフォーマンスを両立できることの証明である。
あなたのシステムからスパゲッティSQLを駆逐し、真のエンジニアリングを取り戻してほしい。
