【Access】動的にパススルークエリを作成

2650 ワード
access access テキストリンク

はじめに

パススルークエリは1度作成するとパラメータの変更ができないため、実行ごとに条件を変えることができません。
そのため、クエリ実行ごとにパラメータを変更したい場合はVBAでパススルークエリを作成する必要があります。

想定環境

Access 2016
SQL Server 2019

作成内容

今回は以下フォームの「生徒番号」を入力後、検索ボタンを押下するとパススルークエリが作成されるプログラムを紹介します。
パススルークエリ作成後はクエリを実行し、実行結果を「氏名」横のテキストボックスに入れています。

###事前準備
SQLServer側で【test_school】というテーブルを作成しておきます。
テーブルの中身は以下。

id name kana
1 佐藤 サトウ
2 山田 ヤマダ
3 加藤 カトウ

VBA

Private Sub passquerycreate_Click()

    Dim selectSQL As String
    Dim db As Database
    Set db = CurrentDb
    Dim qdf As DAO.QueryDef
    
    '確認メッセージ表示なし設定
    DoCmd.SetWarnings False
    
    'パススルークエリ名
    Const QueryName = "P_生徒氏名検索"
    
    'パススルークエリ実行時の接続情報
    Const StrCON = "ODBC; DATABASE=TEST; UID=user; PWD=password; DSN=test"
    
    '実行クエリを変数の中に入れる
    selectSQL = ""
    selectSQL = selectSQL & "SELECT name "
    selectSQL = selectSQL & "  FROM test_school"
    selectSQL = selectSQL & " WHERE id = " & Me.student_id.Value

    '同一名称クエリがあれば削除
    For Each qdf In db.QueryDefs
       If qdf.name = "P_生徒氏名検索" Then
          db.QueryDefs.Delete qdf.name
       End If
    Next qdf
    
    Set qdf = db.CreateQueryDef(QueryName)
    qdf.Connect = StrCON
    qdf.ReturnsRecords = True
    qdf.SQL = selectSQL
    
    'パススルークエリを実行する
    Dim rsZIKList As Recordset
    Dim studentName As String
    Set qdf = db.QueryDefs("P_生徒氏名検索")
    qdf.ReturnsRecords = True
    Set rsZIKList = qdf.OpenRecordset
    studentName = rsZIKList!name
    
    '氏名テキストボックスにクエリ実行結果を入れる
    Me!name = studentName
    
    qdf.Close

    '確認メッセージ表示あり設定
    DoCmd.SetWarnings True

End Sub

実行結果

実行結果例は以下です。
「id」が「1」の「佐藤」が氏名横のテキストボックスに表示されています。

最後に

最後までお読みいただきありがとうございます。
この記事がどなたかの参考になれば幸いです。