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

Accessでシステム開発をする場合、リンクテーブルを介したクエリを作成し、そのクエリをフォームに連携させて表示させたり、検索フォームの検索時に使用する場合もあるかと思います。

ただ、リンクテーブルとクエリの組み合わせだと、Accessの仕様上処理速度が著しく遅くなる場合もあります。そんな時はADOを使ってリンクテーブルを介さずにSQLを発行するという改善案もありますが、パススルークエリを使って、データベースに直接SQLを投げる方法もあります。

パススルークエリを使用する場合、通常パススルークエリでデータベースに渡せるSQL文は固定であり、検索条件の変更に合わせて動的に中身を変えて実行することはできません。ただ、状況によってはどうしてもパススルークエリで動的に検索条件などを変えて実行したい場合もあるかも知れません。

よって今回はDAOを使用して、パススルークエリを動的に再作成するサンプルプログラムを紹介します。

■前提条件
　VBAエディタの参照設定で以下の参照を追加
　Microsoft DAO 3.6 Object Library

※Access2007以降でaccdb形式の場合は以下を参照(この参照での検証は未実施)
「Microsoft Office XX.X Access Database Engine Object Library」

■処理概要
第一引数で渡されたクエリ名が登録されていたら削除して、第二引数で渡されたSQLを元にパススルークエリを作成します。

Sub CreatePassThroughQuery(QueryNameString As String, SqlString As String)

    Dim strSQL As String
    
    Dim dbCurrent As DAO.Database
    Dim qdThrough As DAO.QueryDef
    Dim strConn As String
    
    Set dbCurrent = CurrentDb
    Set qdThrough = CurrentDb.CreateQueryDef()
    
    'パススルークエリに埋め込むDB接続情報
    '接続文字列は環境に合わせて変更してください。
    strConn = "ODBC;DSN=データソース名;UID=DBユーザ名;PWD=パスワード;DATABASE=DB名;"
    
    strSQL = SqlString
    
    'クエリーが存在する場合は削除します。
    For Each qdThrough In dbCurrent.QueryDefs
       If qdThrough.Name = QueryNameString Then
          dbCurrent.QueryDefs.Delete qdThrough.Name
       End If
    Next qdThrough
    
    Set qdThrough = CurrentDb.CreateQueryDef()
    
    With qdThrough
    .Name = QueryNameString
    .sql = strSQL
    .connect = strConn
    End With
    
    dbCurrent.QueryDefs.Append qdThrough
    dbCurrent.QueryDefs.Refresh

    Set qdThrough = Nothing
    Set dbCurrent = Nothing

End Sub

呼び出す際には、以下のように記述してください。

　Call CreatePassThroughQuery(“作成するパススルークエリ名”,”SQL文字列”)

今回も参考になれば幸いです。

ではでは皆さまごきげんよう。