【実務・中級編】動的SQLのデバッグを効率化する「SQL出力ログ」の自動生成ツール – Access VBA解析バイブル

Access VBAを掌握する極限の知見

動的SQLのデバッグを効率化する「SQL出力ログ」の自動生成ツール

開発現場でこんな絶望を味わったことはないだろうか。

「フォームの入力値によってWHERE句が無限に分岐する動的SQLを組み上げたが、実行時エラー『実行時エラー ‘3075’: 構文エラー (演算子が見つかりません) …』が発生した。しかし、エラーメッセージが示すSQLは変数に格納された断片的なものであり、実際にAccessのエンジン（ACE）に投げられた最終形が分からない」

ブレークポイントを張り、イミディエイトウィンドウに `Debug.Print` を仕込み、文字列のシングルクォートや日付のシャープ(`#`)、NULLの扱いに頭を悩ませる。この「動的SQLの目視デバッグ」という不毛な作業に、エンジニアとしての貴重な時間を溶かすのはもう終わりにするべきだ。

プロフェッショナルな開発者であれば、「実行直前の完全なSQL文を、一撃で、確実にトレースできる仕組み」をアーキテクチャの初期段階で組み込んでおくべきだ。今回は、QueryDefと動的SQLの生成を極め、現場の生産性を爆発的に上げる「SQL出力ログ自動生成ツール」の全貌を授けよう。

—

なぜ「その場しのぎの `Debug.Print`」は悪なのか？

多くの初級〜中級プログラマは、SQLを組み立てる過程で以下のようなコードを書く。

‘ ❌ やってはいけない典型的な実装
Dim sql As String
sql = “SELECT FROM T_Sales WHERE 1=1 ”
If Not IsNull(Me.txtClient) Then
sql = sql & “AND ClientID = ” & Me.txtClient & ” ”
End If
If IsDate(Me.txtDateFrom) Then
sql = sql & “AND SaleDate >= #” & Me.txtDateFrom & “# ”
End If
Debug.Print sql ‘ <- これだけで満足していませんか？ CurrentDb.Execute sql, dbFailOnError このアプローチが実務で破綻する理由は明確だ。 1. コードのあちこちに `Debug.Print` が散乱し、保守性が死ぬ。
2. 本番環境（ユーザーのPC）でエラーが起きた際、イミディエイトウィンドウが見えないため、何が起きたか一切追跡できない。
3. パラメータの型変換ミス（文字列にクォートがない、日付の書式がロケール依存になる等）が起きた時の「生データ」が記録されない。

我々が目指すべきは、「SQLの組み立てロジック」と「ログ出力のメカニズム」を完全に分離し、どんな動的クエリであっても一元的にキャプチャできる堅牢な共通モジュール（Class）の構築である。

—

プロダクションコード：`Logger_QueryExecution` の実装

以下のコードは、単なる文字列出力にとどまらず、「いつ、どのプロシージャから、どのようなSQLが発行され、結果どうなったのか（あるいはエラーになったのか）」をテキストファイルに永続化する、実務仕様のクラスモジュール（または標準モジュール）である。

今回は、最も汎用性が高く、インスタンス管理が美しいクラスモジュール形式（クラス名：`clsSqlLogger`）で実装する。

クラスモジュール: `clsSqlLogger`

Option Compare Database
Option Explicit

‘ ==============================================================================
‘ 処理名: clsSqlLogger
‘ 概要 : 動的SQLの実行前フックとファイルロギングを行うプロフェッショナルクラス
‘ ==============================================================================

Private m_LogFilePath As String

‘ クラス初期化時にログファイルの出力先を決定（既定はDBと同じフォルダ）
Private Sub Class_Initialize()
On Error Resume Next
m_LogFilePath = CurrentProject.Path & “\QueryExecution_Log.txt”
End Sub

‘ ログファイルパスを外部から変更可能に
Public Property Let LogFilePath(ByVal Value As String)
m_LogFilePath = Value
End Property

Public Property Get LogFilePath() As String
LogFilePath = m_LogFilePath
End Property

‘ ==============================================================================
‘ メソッド: LogAndExecute
‘ 概要 : SQLをログに出力しつつ、安全に実行（またはQueryDefを更新）する
‘ ==============================================================================
Public Sub LogAndExecute(ByVal TargetQueryDefName As String, ByVal SQL As String, Optional ByVal IsActionQuery As Boolean = True)
Dim fso As Object
Dim ts As Object

‘ 1. ログファイルへの書き出し（トランザクション保護付き）
On Error GoTo ErrorHandler
Set fso = CreateObject(“Scripting.FileSystemObject”)

‘ 追記モードで開く（ファイルがなければ作成）
Set ts = fso.OpenTextFile(m_LogFilePath, 8, True)

ts.WriteLine “————————————————–”
ts.WriteLine “Timestamp : ” & Now()
ts.WriteLine “TargetDef : ” & TargetQueryDefName
ts.WriteLine “Machine : ” & Environ(“COMPUTERNAME”) & ” / User: ” & Environ(“USERNAME”)
ts.WriteLine “— SQL Statement ——————————–”
ts.WriteLine SQL
ts.WriteLine “————————————————–”
ts.Close

Set ts = Nothing
Set fso = Nothing

‘ 2. QueryDefオブジェクトのSQLを動的に書き換えて実行・保存
Dim qdf As QueryDef
Set qdf = CurrentDb.QueryDefs(TargetQueryDefName)
qdf.SQL = SQL
qdf.Close

‘ イミディエイトウィンドウへも転送（開発時の利便性向上）
Debug.Print “[SQL Logged & Updated] ” & TargetQueryDefName

Exit Sub

ErrorHandler:
‘ ロギング自体のエラーで本体の処理を止めない設計（だがイミディエイトには出す）
Debug.Print “[Log Error] ログの書き込みに失敗しました: ” & Err.Description
Resume Next
End Sub

—

実戦投入：呼び出し側の実装例

上記のロガーを、実際の業務フォームやバッチ処理からどのように呼び出すか。
ここがキモだ。「QueryDefをあらかじめ空の状態で用意しておき、実行直前にSQLを流し込む」というデザインパターンを採用する。

1. 事準備

Accessのクエリナビゲータで、適当な名前のクエリ（例: `qry_DynamicSearch`）を適当なSELECT文で作成しておく（中身は後から上書きされるため何でも良い）。

2. フォームや標準モジュールからの呼び出しコード

Sub ExecuteDynamicSearchQuery()
Dim logger As clsSqlLogger
Set logger = New clsSqlLogger

‘ 動的SQLの組み立て
Dim sbSql As String
Dim clientName As String
clientName = “株式会社” & Me.txtSearchKeyword.Value ‘ 画面からの入力と仮定

sbSql = “SELECT T_Orders.OrderID, T_Orders.OrderDate, T_Clients.ClientName ” & _
“FROM T_Clients INNER JOIN T_Orders ON T_Clients.ClientID = T_Orders.ClientID ” & _
“WHERE T_Orders.DeleteFlag = 0 ”

‘ 条件が入力されている場合のみWHERE句を追加（SQLインジェクション対策も考慮）
If Trim(clientName) <> “” Then
‘ 危険なシングルクォートをエスケープする関数を通すのがプロの作法
sbSql = sbSql & “AND T_Clients.ClientName LIKE ‘” & Replace(clientName, “‘”, “””) & “‘ ”
End If

If IsDate(Me.txtDateFrom.Value) Then
sbSql = sbSql & “AND T_Orders.OrderDate >= #” & Format(Me.txtDateFrom.Value, “yyyy/mm/dd”) & “# ”
End If

sbSql = sbSql & “ORDER BY T_Orders.OrderDate DESC;”

‘ 【核心】ロガー経由でQueryDef（qry_DynamicSearch）にSQLを流し込み、ログを自動生成
On Error GoTo ErrorHandler

‘ ログ出力先を明示的に変えたければここで指定可能
‘ logger.LogFilePath = “C:\Logs\Access_SQL_Trace.txt”

logger.LogAndExecute “qry_DynamicSearch”, sbSql, False

‘ フォームのレコードソースにバインドして画面に反映
Me.RecordSource = “qry_DynamicSearch”
Me.Requery

MsgBox “検索が完了しました。ログが記録されました。”, vbInformation, “完了”

CleanUp:
Set logger = New clsSqlLogger ‘ 厳密にはSet logger = Nothing
Exit Sub

ErrorHandler:
MsgBox “予期せぬエラーが発生しました: ” & Err.Description, vbCritical, “エラー”
Resume CleanUp
End Sub

—

プロフェッショナルが教える「ファイル連携・設計上の注意点」

この仕組みを現場に導入する際、インフラ面やAccess特有の挙動でハマりがちなポイントを先回りして共有しよう。

1. マルチユーザー環境（バックエンド共有）におけるファイルパスの罠

• `CurrentProject.Path` は、フロントエンド（ `.accdb` / `.acde` ）が存在するフォルダを指す。
• ネットワーク共有フォルダ（LANディスク等）でフロントエンドを各PCに配布している場合、ログファイルは各ローカルPCに出力されるか、あるいはネットワーク上の書き込み権限問題でエラーになる。
• 対策: ログの出力先は、ユーザーのローカル環境（例: `Environ(“USERPROFILE”) & “\Desktop\”` や一時フォルダ）を指定するか、あるいはデバッグモード時のみファイル出力するようフラグ制御を入れるのが定石だ。

2. ファイル競合（ファイルロック）の回避

• 複数のフォームや複数のユーザーが同時に同じログファイルへ書き込もうとすると、FileSystemObject（FSO）がエラーを吐く可能性がある。
• 今回のコードでは `OpenTextFile(…, 8, True)`（追記モード）を使用しているため比較的安全だが、極限まで高負荷なバッチ処理の場合は、エラーハンドリング内でリトライ処理（Sleepを入れるなど）を挟むと完璧である。

3. QueryDefを直接書き換えるメリットとデメリット

• メリット: 複雑な動的SQLであっても、Accessの標準クエリとしてQueryDefに定着させることで、そのままレポートのデータソースにしたり、Excelからの外部接続（ODBC）の対象にしたりできる。
• デメリット: 複数人が同時に同じフロントエンドを共有している場合（非推奨構成だが中小企業ではよくある）、QueryDefの定義自体が競合する可能性がある。完全なマルチユーザー対応を目指すなら、QueryDefの書き換えではなく、ADODB.CommandとParametersコレクションを使った「真のパラメータクエリ」を採用すべきである。

—

さらなる高みへ：真のパラメータクエリ（ADODB）とのハイブリッド

SQLインジェクションを根絶し、パフォーマンス（Query Planのキャッシュ）を最大化したいのであれば、文字列結合による動的SQLの生成自体を卒業し、`ADODB.Command` を使うべきだ。

しかし、ADODBを使うと「今どんな値がバインドされて実行されたのか」がブラックボックス化し、デバッグが困難になる。だからこそ、今回紹介した「SQLとパラメータの値を整形してログに吐き出すラッパー関数」の概念が活きてくるのだ。

動的SQLの構築に悩む時間は、今日で終わりだ。
この「SQL出力ログ自動生成ツール」をプロジェクトに組み込み、エラーの兆候をコンマ数秒で検知できる圧倒的な開発環境を手に入れてほしい。