Access VBAの「IN句」問題、文字列連結で消耗していませんか？
こんにちは。Access開発の深淵を覗き込もうとしている皆さん、ようこそ。
Accessで開発をしていると、必ず一度は壁にぶつかるのが「IN句の動的生成」です。「ユーザーが選択した複数の部署IDで抽出したい」といったケースですね。
多くの入門書では「SQL文を文字列結合（`”WHERE ID IN (” & varList & “)”`）で組み立てなさい」と教えられます。しかし、これはセキュリティ（SQLインジェクション）の観点からも、メンテナンス性の観点からも「禁じ手」です。
今日は、伝説的なアーキテクトたちが密かに実践している、「一時テーブルを用いたIN句の限界突破テクニック」を伝授しましょう。これを知れば、あなたのAccess開発は一気にプロフェッショナルな領域へと昇華します。
—
1. なぜ「文字列連結」は悪なのか？
SQL文を文字列として組み立てる方法には、以下のリスクが伴います。
- SQLインジェクションのリスク: 悪意あるユーザーが入力欄にSQLコマンドを仕込むと、データベースが乗っ取られる可能性があります。
- クエリキャッシュの汚染: SQL文が1文字でも変わるたびに、Access（Jet/ACEエンジン）は新しいクエリとしてコンパイルし直します。これがパフォーマンス低下の主因です。
- 可読性の崩壊: ダブルクォーテーションの嵐で、何を書いているのか自分でも分からなくなる。
—
2. 解決策：一時テーブルへの「流し込み」戦略
今回のテーマは「DAO.QueryDefを使い、一時テーブルとJOINさせる」手法です。
処理の全体図
1. 準備: 抽出条件となるID群を保持する一時テーブル（構造だけ作成）を用意する。
2. 格納: VBAでID群をその一時テーブルへINSERTする。
3. 結合: メインのクエリで、一時テーブルをINNER JOINする。
これなら、SQL文を書き換える必要は一切ありません。クエリ定義は固定されたまま、データだけが入れ替わる「美しい設計」になります。
—
3. 実装コード：プロフェッショナルの書き方
以下のコードは、`tbl_Filter` という一時テーブルにIDを放り込み、メインテーブルと結合して抽出する例です。
Public Sub RunDynamicQuery(arrIDs() As Variant)
Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim i As Long
Set db = CurrentDb
‘ 1. 一時テーブルをクリア（高速化のためDELETE文）
db.Execute “DELETE FROM tbl_Filter”, dbFailOnError
‘ 2. 一時テーブルに条件値を挿入
Set rs = db.OpenRecordset(“tbl_Filter”, dbOpenDynaset)
For i = LBound(arrIDs) To UBound(arrIDs)
rs.AddNew
rs!FilterID = arrIDs(i)
rs.Update
Next i
rs.Close
‘ 3. JOINを用いたクエリの実行（SQLは固定！）
‘ クエリ定義（qdfMain）は「SELECT FROM M_Data INNER JOIN tbl_Filter …」で作成済みと仮定
Dim qdf As DAO.QueryDef
Set qdf = db.QueryDefs(“qdfMain”)
‘ ここで結果を取得・活用する
Set rs = qdf.OpenRecordset()
‘ …（ここに抽出後のロジックを記述）
rs.Close
Set rs = Nothing
Set qdf = Nothing
End Sub
このコードの「賢い」ポイント
- SQLをいじらない: `qdfMain` のSQLは一度書けば一生書き換える必要がありません。
- トランザクションの恩恵: `dbOpenDynaset` での追加処理は、件数が多い場合でも `BeginTrans / CommitTrans` を挟むことで爆速になります。
- 安全: SQLインジェクションは構造的に発生し得ません。
—
4. 陥りやすいエラーと対策
初心者がこの手法で躓くポイントを先回りして解説します。
- 「一時テーブルが使われています」エラー:
マルチユーザー環境で同じテーブル名を使うと競合します。その場合は、一時テーブルの名前を `tbl_Filter_セッションID` のように動的に変えるか、`TempVars` を使う工夫が必要です。
- JOINの方向を間違える:
`INNER JOIN` は「一致するものだけ」を返します。もし「条件なしなら全件出す」という仕様にする場合は、`WHERE` 句で「一時テーブルが空なら全件対象とする」ような工夫をクエリ側で組み込むと、より柔軟になります。
—
最後に：エンジニアとしての誇り
「動的なSQLを文字列で連結して作る」のは、Access VBAを始めたばかりの人が通る道です。しかし、そこから一歩進んで、「データベースの構造そのものを利用して条件を制御する」という考え方にシフトできたとき、あなたはもう「Accessを使う人」から「Accessを操るエンジニア」になっています。
この一時テーブル手法は、大規模なシステムになればなるほど、その真価を発揮します。ぜひ、今のプロジェクトで試してみてください。
ここをクリアすれば、もうAccessで怖いものはありませんよ。また分からないことがあれば、いつでも聞きに来てくださいね。応援しています！
