Access VBAの限界を突破せよ：JSON駆動型QueryDefによる「動的検索エンジン」の構築
多くの開発者が、検索画面を作るたびに`WHERE`句を文字列結合で組み立てるという「アンチパターン」に陥っています。`”WHERE ID = ” & Me.txtID` といったコードは、SQLインジェクションの脆弱性を生むだけでなく、デバッグ不可能なスパゲッティコードの温床です。
本稿では、複雑な検索条件をJSONで一元管理し、Accessのネイティブ機能である`QueryDef`とパラメータークエリを融合させた、「堅牢かつ保守可能な動的検索システム」の設計思想を伝授します。
—
1. なぜ「文字列結合」を捨て、JSONとQueryDefに移行すべきか
SQLを文字列として生成すると、以下の問題が必ず発生します。
- 型変換の地獄: 日付の `#` 囲み、文字列の `’` エスケープ忘れによるエラー。
- クエリプランの非効率: 毎回異なる文字列が生成されるため、データベースエンジンが実行計画をキャッシュできず、パフォーマンスが低下する。
- メンテナンス性の欠如: 画面数が増えるたびに、類似のSQL生成ロジックが散乱する。
我々アーキテクトが目指すべきは、「条件のデータ化」と「クエリの固定化」の分離です。JSONで条件を定義し、QueryDefのパラメーターとして渡すことで、SQLエンジンは常に同一のクエリプランを再利用でき、安全性も担保されます。
—
2. アーキテクチャの全体像
1. JSON層: 検索条件を `{“Key”: “Value”}` 形式の文字列として保持。
2. 解析層: VBAの `ScriptControl` または `VBA-JSON` (JSONConverter) を使い、条件をDictionaryオブジェクトに変換。
3. 実行層: `QueryDef` オブジェクトの `.Parameters` コレクションに値を注入し、レコードセットを開く。
—
3. 実装：プロフェッショナル・コード例
※事前に [VBA-JSON](https://github.com/VBA-tools/VBA-JSON) をインポートしておくことを推奨します。
ステップ1: パラメータークエリの準備
Access上で以下のSQLを保存してください（クエリ名: `qry_DynamicSearch`）。
PARAMETERS [p_Category] Text(255), [p_MinPrice] Long;
SELECT FROM T_Products
WHERE Category = [p_Category] AND Price >= [p_MinPrice];
ステップ2: 動的実行エンジン
このモジュールが、あなたのシステムの検索エンジンとなります。
Public Sub ExecuteDynamicSearch(jsonCriteria As String)
Dim db As DAO.Database
Dim qdf As DAO.QueryDef
Dim jsonDict As Object
‘ 1. JSON解析
Set jsonDict = JsonConverter.ParseJson(jsonCriteria)
Set db = CurrentDb
Set qdf = db.QueryDefs(“qry_DynamicSearch”)
‘ 2. パラメーターの注入（型安全）
‘ 文字列結合を一切行わないことが重要
qdf.Parameters(“p_Category”) = jsonDict(“category”)
qdf.Parameters(“p_MinPrice”) = jsonDict(“min_price”)
‘ 3. レコードセットの実行と活用
Dim rs As DAO.Recordset
Set rs = qdf.OpenRecordset(dbOpenSnapshot)
‘ ここでフォームのレコードソースに設定するなど
‘ Set Me.Recordset = rs
Debug.Print “検索完了: ” & rs.RecordCount & ” 件ヒット”
rs.Close
Set rs = Nothing
Set qdf = Nothing
End Sub
—
4. 運用上の極意と注意点
パフォーマンスの最適化
`QueryDef` を使用する最大の利点は、「コンパイル済みのクエリ」であることです。`DoCmd.RunSQL` や `CurrentDb.Execute` で動的SQLを投げ続けるような野蛮な設計は今すぐ止めてください。`OpenRecordset` を使うことで、接続のオーバーヘッドを最小限に抑えられます。
JSONの設計ルール
- 構造のフラット化: JSONはあまり深くネストさせないこと。Accessのパラメーター数は最大255個までですが、保守性を考えると10〜20個程度に収めるのが「プロの境界線」です。
- Null値のハンドリング: JSON上の値がNullの場合、Dictionaryから取り出す際に `vbNullString` ではなく `Null` 型として明示的に渡すよう、変換層でラッパー関数を用意してください。
なぜこれが「負けない」設計なのか
将来的に検索条件が増えたとき、あなたはSQLを書き換える必要はありません。
1. クエリの `PARAMETERS` 句に項目を追加。
2. SQLの `WHERE` 句を調整。
3. VBA側で `qdf.Parameters` を1行追加するだけ。
この「疎結合」こそが、数年間にわたって運用される業務システムの生命線です。
—
最後に：エンジニアとしての矜持
VBAはレガシーと呼ばれることもありますが、その心臓部であるDAO（Data Access Objects）の設計は極めて堅牢です。JSONとQueryDefを組み合わせる手法は、現代的なWeb API開発の思想をAccessに持ち込む試みに他なりません。
「動けばいい」というコードで満足せず、「誰が読んでも意図が明確で、かつ変更に強い」コードを書いてください。それが、真に価値のある業務自動化エンジニアの姿です。
何か疑問があれば、いつでもコードを見せに来てください。修正が必要な箇所があれば、徹底的にレビューしましょう。
