2025.03.01 2025.03.01

パススルークエリの高度利用

nanikatoaccess

はじめに：なぜパススルークエリを使うのか
パススルークエリの基本構造と作成手順
高度利用のポイント
- 3.1 ストアドプロシージャ実行
- 3.2 動的なパラメータ設定
- 3.3 大規模データ集計・複雑なJOIN
VBAからパススルークエリを操作する実装例
- 4.1 QueryDefオブジェクトを動的に生成・更新
- 4.2 ADO＋パススルークエリでの実行
セキュリティ・パフォーマンスの観点
- 5.1 SQLインジェクション対策
- 5.2 接続文字列管理と権限設定
- 5.3 パフォーマンス向上のベストプラクティス
まとめ

ないものはない！お買い物なら楽天市場

1. はじめに：なぜパススルークエリを使うのか

Microsoft AccessでSQL Serverのデータを扱う場合、主に以下の手段があります。

リンクテーブル: Accessクエリやフォームからローカルテーブルのように操作できるが、複雑な集計・結合を多用するとパフォーマンスが低下する場合がある。
VBA＋ADO: Call データベース接続() を利用し、SQLを直接実行。パラメータやトランザクション管理を柔軟に扱える。
パススルークエリ: Accessの「クエリ」として定義しつつ、実際にはSQL Serverに直接SQL文を渡す仕組み。AccessエンジンではなくSQL Server側で処理するため、高パフォーマンスが期待できる。

パススルークエリ の最大の利点は、Accessのクエリデザインを利用しながら、処理はSQL Serverに丸投げできる ところです。特に複雑なJOINや集計、ストアドプロシージャ実行など、サーバー側で最適化したい場合に威力を発揮します。

2. パススルークエリの基本構造と作成手順

Accessでクエリデザインを開き、[SQLビュー]に切り替える。
[クエリのプロパティ]で [SQLのパススルー] を選択し、ODBC接続文字列を設定する。
SQL Serverで有効なSQL文（SELECT, UPDATE, EXECなど）を記述する。
クエリを保存すると、Access内で「パススルークエリ」として登録される。

例:
SELECT 顧客ID, 顧客名, 売上高
FROM dbo.顧客売上ビュー
WHERE 売上高 > 100000
これを「SQLのパススルー」クエリとして保存しておけば、Accessが解釈せずにSQL Serverへダイレクトに投げるため高速処理が可能です。

3. 高度利用のポイント

3.1 ストアドプロシージャ実行

パススルークエリのSQL文には、ストアドプロシージャの呼び出しを記述することもできます。

sqlコピーするEXEC dbo.月次売上集計 '2025-01-01', '2025-12-31';

Accessクエリとして保存しておけば、フォームやマクロ、レポートから簡単にストアドプロシージャを呼び出せます。

3.2 動的なパラメータ設定

パススルークエリは基本的に静的SQLを記述しますが、マクロやVBAからクエリのSQLを差し替えることで動的なパラメータを反映できます。
後述のQueryDefオブジェクトを使えば、フォームで入力された値などをSQL文に組み込み、実行前にパススルークエリを更新することが可能です。

3.3 大規模データ集計・複雑なJOIN

Access内で複雑なクエリを組むよりも、SQL Server側の最適化エンジンをフルに活用したほうが、ネットワーク負荷を抑えながら高速に動作します。集計やJOINを多用する業務ロジックは、パススルークエリやストアドプロシージャをベースに設計すると効果的です。

4. VBAからパススルークエリを操作する実装例

4.1 QueryDefオブジェクトを動的に生成・更新

AccessのDAOライブラリにはQueryDefオブジェクトがあり、クエリのSQL文や接続プロパティを動的に変更できます。

コード例：フォーム入力をSQL文に反映させるパススルークエリ

'------------------------------------------------------------------------------------------
'【目的】   フォーム上で指定された期間をもとに、SQL Server側の売上データをパススルーで集計
'【ポイント】
' ①入力必須項目のNullチェックを行う
' ②マクロよりVBAコードのほうが柔軟にパラメータを差し込みやすい
' ③QueryDefのConnectプロパティにDSNレス接続文字列を設定し、SQLを組み立ててパススルー実行
'------------------------------------------------------------------------------------------
Public Sub パススルークエリ動的作成(str開始日 As String, str終了日 As String)

    ' --- ①入力チェック ---
    If IsNull(str開始日) Or str開始日 = "" Then
        MsgBox "開始日を入力してください。", vbExclamation
        Exit Sub
    End If
    If IsNull(str終了日) Or str終了日 = "" Then
        MsgBox "終了日を入力してください。", vbExclamation
        Exit Sub
    End If
    
    ' 日付形式チェック(簡易的)
    If Not IsDate(str開始日) Or Not IsDate(str終了日) Then
        MsgBox "開始日または終了日が日付として不正です。", vbExclamation
        Exit Sub
    End If
    
    ' --- ②SQLインジェクション対策（最低限の日付型チェック）---
    '   日付なら問題ないですが、文字列として扱う際は要注意。
    
    ' --- ③クエリ文字列を組み立て ---
    Dim strSQL As String
    strSQL = "SELECT 顧客ID, SUM(売上金額) AS 合計売上 " & _
             "FROM dbo.売上テーブル " & _
             "WHERE 売上日 BETWEEN '" & str開始日 & "' AND '" & str終了日 & "' " & _
             "GROUP BY 顧客ID"
    
    ' --- ④パススルークエリを作成 or 既存を更新 ---
    Dim qdf As DAO.QueryDef
    Const cQueryName As String = "qry売上集計パススルー"  'クエリ名
    
    ' 既にクエリが存在する場合は取得、なければ作成
    On Error Resume Next
    Set qdf = CurrentDb.QueryDefs(cQueryName)
    On Error GoTo 0
    
    If qdf Is Nothing Then
        Set qdf = CurrentDb.CreateQueryDef(cQueryName)
    End If
    
    ' --- ⑤接続文字列(DSNレス)を設定 ---
    '     ※実際はCall データベース接続()のcn情報を流用する場合もある。
    qdf.Connect = "ODBC;Driver={SQL Server Native Client 11.0};" & _
                  "Server=MyServer\SQLEXPRESS;" & _
                  "Database=MyDatabase;" & _
                  "Uid=MyUser;" & _
                  "Pwd=MyPass;"
    
    ' --- ⑥SQL文設定 & パススルーフラグ ---
    qdf.SQL = strSQL
    qdf.ReturnsRecords = True  ' SELECT文なのでTrue
    ' クエリを保存
    CurrentDb.QueryDefs.Refresh
    
    MsgBox "パススルークエリ「" & cQueryName & "」を作成/更新しました。", vbInformation
    
    ' --- ⑦クエリを実行 or フォーム/レポートのレコードソースに設定可能 ---
    DoCmd.OpenQuery cQueryName   ' 実行して結果を確認（アクセス内で一時的に閲覧）
    
    ' --- ⑧登録後、入力項目をNullに (フォーム初期化イメージ) ---
    'Me.txt開始日 = Null
    'Me.txt終了日 = Null
    
    Set qdf = Nothing
End Sub

解説

入力チェック(Null, 日付型)
入力必須項目と型の検証は、最低限行うことで意図しないSQL注入を防ぎます。
QueryDef.Connect
DSNレス接続を文字列で指定しています。外部ファイルに接続情報を置く場合や、Call データベース接続のcnから取得して組み立てる方法もあります。
QueryDef.SQL
ここで書いたSQL文がそのままSQL Serverに渡されるため、Accessの解釈を経ずにサーバー側で実行されます。
ReturnsRecords
TrueならSELECT、FalseならINSERT/UPDATE/DELETEやストアドプロシージャ呼び出しに使えます。

4.2 ADO＋パススルークエリでの実行

VBAから直接ADOコマンドを組み立てるのではなく、あえてパススルークエリを呼び出すケースもあります。例えば、以下のように既存のパススルークエリを実行して結果をRecordsetとして取得できます。

Public Sub パススルークエリをADOで結果取得()

    Call データベース接続 ' ADO接続 cn を開く（グローバル変数利用）
    
    Dim rs As ADODB.Recordset
    Set rs = New ADODB.Recordset
    
    Dim strSQL As String
    ' パススルークエリそのものをFROM句として参照する例（Access上のクエリ名）
    ' ただし、AccessクエリをFROM句に使う場合はローカル解釈になるケースもあるので注意。
    ' 直接SQL Serverで処理したい場合は、ストアドプロシージャ or パススルークエリのSQLをそのまま渡す方が一般的。
    strSQL = "SELECT * FROM [qry売上集計パススルー]" 
    
    rs.Open strSQL, cn, adOpenForwardOnly, adLockReadOnly
    
    If Not rs.EOF Then
        ' 結果処理
        MsgBox "最初の顧客ID: " & rs!顧客ID & " / 合計売上: " & rs!合計売上
    Else
        MsgBox "データがありません。"
    End If
    
    rs.Close
    Set rs = Nothing
End Sub

実際のところ、「ADO＋パススルークエリを組み合わせる」よりも、“ADOで直接SQL Serverにクエリ実行” する方が柔軟な場合が多いです。しかし、既にAccess内にパススルークエリを定義済みで、それを再利用したいというシナリオではこの方法が役立ちます。

5. セキュリティ・パフォーマンスの観点

5.1 SQLインジェクション対策

パススルークエリで文字列を動的生成する際は、入力値を必ず検証する習慣を。
数値項目はIsNumeric、日付項目はIsDateなどで厳格にチェック。
ストアドプロシージャやパラメータバインド（EXEC dbo.プロシージャ @param = ?)を活用するのも有効。

5.2 接続文字列管理と権限設定

DSNレス接続なら、すべてをVBAで一元管理できる半面、接続情報（ユーザーID、パスワード）をソースに書くリスクがあります。暗号化やWindows認証を検討しましょう。
SQL Server側では、最小限の権限だけ付与するロール設定を行い、テーブル直接の更新権限を外し、ストアドプロシージャやビューを介したアクセスに限定する設計も考えられます。

5.3 パフォーマンス向上のベストプラクティス

必要な列だけ取得: SELECT *よりも必要な列だけ明示してネットワーク負荷を削減。
サーバー側最適化: インデックス・統計情報・クエリプランをSQL Server Management Studio等で確認し、集計ロジックをストアドプロシージャ化する。
分割と分業: AccessでUI（フォーム・レポート）を作成し、複雑な処理はパススルークエリやStored Procedureでサーバー処理。これが最も性能を引き出しやすい構成です。

6. まとめ

パススルークエリの高度利用 のポイントは、「Accessの使いやすいUI」と「SQL Serverの強力な処理エンジン」を両立させるところにあります。具体的には：

複雑なSQLやストアドプロシージャをサーバー側で実行
- Accessエンジンの負荷を減らし、ネットワーク負荷も軽減。
動的パラメータやQueryDefオブジェクトで柔軟に差し替え
- ユーザー入力やフォームの値を元にして、必要なSQL文をリアルタイムで生成可能。
セキュリティと最適化を意識
- 入力バリデーションやDSNレス接続・ストアドプロシージャの併用でインジェクションを防ぐ。
- インデックスや集計ロジックはサーバー側で管理し、性能を高める。

AccessとSQL Serverを組み合わせた業務システムでは、パススルークエリがうまく活用されていないケースも少なくありません。「リンクテーブルだけでパフォーマンスが出ない…」 と困っている方は、ぜひパススルークエリを検討してみてください。UIはAccessのまま、実処理はSQL Serverに任せる ことで、レスポンスと安定性を大幅に改善できるはずです。

今後も当ブログでは、AccessとSQL Serverの連携に関するノウハウを深堀りしていきます。パススルークエリを活用して、現場のニーズに即した高性能システムをぜひ実現してみてください。

DMM

ABOUT ME

パススルークエリの高度利用

目次

1. はじめに：なぜパススルークエリを使うのか

2. パススルークエリの基本構造と作成手順

3. 高度利用のポイント

3.1 ストアドプロシージャ実行

3.2 動的なパラメータ設定

3.3 大規模データ集計・複雑なJOIN

4. VBAからパススルークエリを操作する実装例

4.1 QueryDefオブジェクトを動的に生成・更新

コード例：フォーム入力をSQL文に反映させるパススルークエリ

解説

4.2 ADO＋パススルークエリでの実行

5. セキュリティ・パフォーマンスの観点

5.1 SQLインジェクション対策

5.2 接続文字列管理と権限設定

5.3 パフォーマンス向上のベストプラクティス

6. まとめ

株投資 × Access!? 株投資を効率化するシステムを作るシリーズ①　要件定義編

拡張シナリオ：Azure・クラウド連携

ストアドプロシージャ活用：実運用シナリオ

リンクテーブル管理と再接続のベストプラクティス

API連携とイベントドリブン処理

VBA初心者でもOK！AIアシスタントを使ったコード自動生成テクニック

目次

1. はじめに：なぜパススルークエリを使うのか

2. パススルークエリの基本構造と作成手順

3. 高度利用のポイント

3.1 ストアドプロシージャ実行

3.2 動的なパラメータ設定

3.3 大規模データ集計・複雑なJOIN

4. VBAからパススルークエリを操作する実装例

4.1 QueryDefオブジェクトを動的に生成・更新

コード例：フォーム入力をSQL文に反映させるパススルークエリ

解説

4.2 ADO＋パススルークエリでの実行

5. セキュリティ・パフォーマンスの観点

5.1 SQLインジェクション対策

5.2 接続文字列管理と権限設定

5.3 パフォーマンス向上のベストプラクティス

6. まとめ

株投資 × Access!? 株投資を効率化するシステムを作るシリーズ① 要件定義編

拡張シナリオ：Azure・クラウド連携

ストアドプロシージャ活用：実運用シナリオ

リンクテーブル管理と再接続のベストプラクティス

API連携とイベントドリブン処理

VBA初心者でもOK！AIアシスタントを使ったコード自動生成テクニック

株投資 × Access!? 株投資を効率化するシステムを作るシリーズ①　要件定義編