QueryDefのSQLプロパティを「外部JSONファイル」で管理する：ソースコードとクエリの完全分離
レガシーシステムの維持において、最大の悪夢は「数千行のVBAコードの中に、複雑な長文化したSQL文字列がハードコーディングされている状態」である。仕様変更のたびにVBAエディタを開き、エスケープ文字に悩まされながら改行を繰り返し、コンパイルエラーにおびえる――。この悪習から抜け出さない限り、Accessデータベースは「属人化した負債の塊」のままだ。
シニアエンジニアよ、目を覚ませ。
SQLはコードではない。データである。したがって、コード（VBA）とデータ（SQL）は物理的に分離されなければならない。
本稿では、Access VBAの心臓部である `QueryDef` オブジェクトを極限まで活かし、外部JSONファイルでSQLを完全外部化・動的管理するアーキテクチャを提示する。VBAから外部JSONをパースし、クエリ定義を動的に再構築する極限のテクニックを解説しよう。
—
なぜ「QueryDef × 外部JSON」なのか？
VBA内での文字列結合による動的SQL生成は、SQLインジェクションのリスク、エスケープ処理の煩雑さ、そして何より可読性の著しい低下を招く。
これを外部JSONファイルによる管理へ移行するメリットは以下の通りだ。
1. 完全な関心の分離（SoC）: SQLのチューニングや構文変更に、VBAのコンパイル・修正は一切不要。
2. 多言語・多環境対応: 開発環境、ステージング、本番環境で接続先やパラメータ定義が異なる場合も、JSONを切り替えるだけで対応可能。
3. QueryDefのキャッシュとパフォーマンス: DAOの `QueryDef` オブジェクトを適切に永続化・再利用することで、Jet/ACEエンジンのクエリプラン最適化の恩恵を最大限に受ける。
—
アーキテクチャの全体像
今回構築する仕組みのフローは以下の通りだ。
[外部 JSONファイル] ──(FileSystemObject)──> [VBA JSONパーサー]
│
▼
[Access Database] <──(QueryDef.SQL 更新)── [動的パラメータバインド]
※ Access VBA標準にはJSONパーサーが存在しないため、軽量なVBA用JSONパーサー（またはスクリプト実行エンジン）を介してメモリ上に展開する。ここでは依存関係を最小限にするため、Windows標準の `VBScript.RegExp` や最小限のパースロジック、あるいは外部API連携を想定した堅牢な実装を行う。
---
えげつないほど実用的な実装コードを以下に提示する。そのまま現場のモジュールに組み込んでほしい。
1. 外部JSONファイルの構造定義 (`queries.json`)
アプリケーションケータイのルート、または指定された設定フォルダに配置する。
{
“GetActiveClients”: {
“Description”: “アクティブな顧客リストを取得するパラメータクエリ”,
“SQL”: “SELECT ClientID, CompanyName, UpdatedAt FROM T_Clients WHERE Status = [p_Status] AND UpdatedAt >= [p_Date] ORDER BY UpdatedAt DESC;”
},
“CalcMonthlySales”: {
“Description”: “月別売上集計クエリ”,
“SQL”: “FORMAT([SaleDate], ‘yyyy-mm’) AS SaleMonth, SUM(Amount) AS TotalAmount FROM T_Sales GROUP BY FORMAT([SaleDate], ‘yyyy-mm’);”
}
}
—
2. VBA実装：JSON読み込みとQueryDef動的バインドエンジン
以下のコードは、エラーハンドリング、オブジェクトの明示的解放（メモリリーク防止）、およびDAOのライフサイクル管理を完全に網羅したプロダクション品質のモジュールである。
Option Compare Database
Option Explicit
‘ =========================================================================
‘ クラス名 / モジュール名: mQueryManager
‘ 概要: 外部JSONからSQLを読み込み、QueryDefへ動的に適用・実行する
‘ =========================================================================
‘ 外部JSONファイルを読み込むメインプロシージャ
Public Sub ExecuteExternalQueryExample()
Dim db As DAO.Database
Dim qdf As DAO.QueryDef
Dim jsonPath As String
Dim sqlText As String
Dim queryName As String
queryName = “GetActiveClients”
jsonPath = CurrentProject.Path & “\queries.json”
On Error GoTo ErrorHandler
‘ 1. 外部JSONから指定クエリのSQL文を取得
sqlText = GetSqlFromJson(jsonPath, queryName)
If Len(sqlText) = 0 Then
MsgBox “指定されたクエリ定義がJSON内に見つかりません: ” & queryName, vbCritical
Exit Sub
End If
‘ 2. 現在のデータベースオブジェクトを取得
Set db = CurrentDb
‘ 3. QueryDefの存在確認と生成・更新
‘ ※ 既存の永続クエリ上書き、または一時クエリ（Temporary QueryDef）の作成
On Error Resume Next
Set qdf = db.QueryDefs(queryName)
If Err.Number <> 0 Then
‘ 存在しない場合は新規作成
Set qdf = db.CreateQueryDef(queryName, sqlText)
Err.Clear
Else
‘ 存在するならSQLプロパティを動的に上書き
qdf.SQL = sqlText
End If
On Error GoTo ErrorHandler
‘ 4. パラメータクエリとしての実行・値のバインド
‘ パラメータ [p_Status] と [p_Date] に安全に値を渡す
qdf.Parameters(“p_Status”) = 1
qdf.Parameters(“p_Date”) = #2023-01-01#
‘ レコードセットの取得（メモリ効率を考慮しForwardOnly/ReadOnlyで開く）
Dim rs As DAO.Recordset
Set rs = qdf.OpenRecordset(dbOpenSnapshot, dbForwardOnly)
‘ デバッグ出力（実運用ではここでグリッドへのバインドやデータ処理を行う）
Do While Not rs.EOF
Debug.Print “Client: ” & rs!CompanyName & ” / Updated: ” & rs!UpdatedAt
rs.MoveNext
Loop
CleanUp:
‘ 厳格なオブジェクトの解放（メモリ最適化の極意）
If Not rs Is Nothing Then rs.Close: Set rs = Nothing
If Not qdf Is Nothing Then Set qdf = Nothing
If Not db Is Nothing Then Set db = Nothing
Exit Sub
ErrorHandler:
MsgBox “予期せぬエラーが発生しました: ” & Err.Description, vbCritical
Resume CleanUp
End Sub
‘ =========================================================================
‘ 補助関数: 簡易JSONパーサー（外部ライブラリ依存排除版）
‘ 実現場のレガシー環境を考慮し、FileSystemObjectと正規表現でJSONからSQLを抽出
‘ =========================================================================
Private Function GetSqlFromJson(ByVal filePath As String, ByVal targetKey As String) As String
Dim fso As Object
Dim ts As Object
Dim jsonString As String
Dim regEx As Object
Dim matches As Object
GetSqlFromJson = “”
If Dir(filePath) = “” Then Exit Function
‘ ファイル読み込み
Set fso = CreateObject(“Scripting.FileSystemObject”)
Set ts = fso.OpenTextFile(filePath, 1, False) ‘ ForReading
jsonString = ts.ReadAll
ts.Close
Set ts = Nothing
Set fso = Nothing
‘ 正規表現を用いて対象のキーに対応するSQL文字列を抽出
‘ JSONの構造変化に強靭なパターンマッチング
Set regEx = CreateObject(“VBScript.RegExp”)
With regEx
.Global = False
.IgnoreCase = True
‘ “TargetKey”: { … “SQL”: “…” } の構造をキャプチャする
.Pattern = “””” & targetKey & “””” & “\s:\s\{[^}]?” & “””SQL””” & “\s:\s””([^””]+)”””
End
If regEx.Test(jsonString) Then
Set matches = regEx.Execute(jsonString)
GetSqlFromJson = matches(0).SubMatches(0)
End If
Set matches = Nothing
Set regEx = Nothing
End Function
—
チーフアーキテクトが教える「極限の知見」と注意点
1. DAOのキャッシュと Jet/ACE クエリプランの最適化
`QueryDef.SQL = sqlText` で動的にSQLを書き換えた瞬間、Accessのデータベースエンジン（Jet/ACE）は内部で実行計画の再コンパイルを行う。
もし高頻度でループ内でこの書き換えを行うと、プランキャッシュのチャーン（頻繁な破棄と生成）が発生し、パフォーマンスが劇的に悪化する。
動的SQLの書き換えは「アプリケーション起動時」または「画面オープン時」など、ライフサイクルの節目に限定すべきである。
2. メモリリークの完全排除
Access VBAにおいて、`CurrentDb` を変数に格納する際や、`QueryDef`, `Recordset` を操作する際、明示的に `Set xxx = Nothing` を行わないと、COMコンポーネントの参照カウントが残存し、Accessプロセスがメモリ上に居座り続ける（ゾンビプロセス化）。
上記のコード例の通り、`CleanUp` ラベリングを用いた確実なオブジェクト解放のイディオムを必ず遵守すること。
3. セキュリティとインジェクション対策
外部JSONファイルをテキストエディタ等で編集可能にすることは保守性を高める反面、不正な改ざんのリスクも生じる。
本稿のように、パラメータクエリの構文（`[p_Status]` 等）を維持し、VBA側で強固に `qdf.Parameters` コレクション経由で値をバインドしていれば、文字列連結によるSQLインジェクションは完全に遮断される。JSONから読み込んだSQL文字列のなかに直接変数を埋め込むような愚行は絶対に行ってはならない。
—
結言
コードとデータの分離は、モダンなソフトウェア開発においては常識である。しかし、Accessという閉じたレガシー環境においては、この「当たり前」を実装することすら敬遠されがちだ。
外部JSONによるQueryDefの完全制御を導入した瞬間から、あなたのAccessアプリケーションは単なる「属人化したローカルDBツール」から、「疎結合で拡張性の高いエンタープライズ・アーキテクチャ」へと進化を遂げる。
構造を極めろ。コードを美しく保て。それがプロフェッショナルの仕事である。
