【ACCESS VBA】ODBCパススルー接続

ACCESS

2023.10.21 2021.06.10

ACCESS VBA上でSQLを記載して　パススルークエリでODBC接続をする方法です。

ACCESSから他のデータベースに接続する時にリンクテーブルを利用する場合、一旦ACCESS内に全データを読み込み、その上でACCESS内のテーブルに対して直接SQLを実行することになります。

そのため大量のデータを取得する場合や、複雑なSQLを発行する場合は処理が非常に遅くなることがあります。

ODBC接続先のデータべースが ACCESS以外の場合（OracleやSQLServer）パススルークエリを利用することで、OracleやSQLServerが稼働しているサーバ上でSQLを実行するため、高速にデータを取得出来る可能性が高くなります。

下記のサンプルコードでは「 Q_パススルー」という名前のクエリを利用します。

既に作成されている場合（前回作成分が残っている場合）はそのまま利用し、無い場合は新規作成します。

サンプルコード

Dim db As DAO.Database
Dim qDef As QueryDef
Dim sql As String
Set db = CurrentDb

sql = "SELECT * FROM TABLE WHERE COLUMN = 12345"

'既にクエリが存在しているかチェック
For Each qDef In db.QueryDefs
    If qDef.Name = "Q_パススルー" Then
       'クエリが存在
        qDef.ODBCTimeout = 30
        qDef.Connect = "ODBC;DSN=データソース名;" & _
        "UID=ユーザー; PWD=パスワード;DATABASE=データベース名;"
        qDef.sql = sql
        qDef.ReturnsRecords = True

        GoTo DataSet
   End If
Next qDef

'クエリがない場合は新規作成
Set qDef = db.CreateQueryDef()
qDef.ODBCTimeout = 30
qDef.Connect = "ODBC;DSN=データソース名;" & _
"UID=ユーザー; PWD=パスワード;DATABASE=データベース名;"
qDef.Name = "Q_パススルー"
qDef.sql = sql
qDef.ReturnsRecords = True
db.QueryDefs.Append qDef
db.QueryDefs.Refresh

DataSet:
'フォームのレコードソースにクエリを設定
Me.RecordSource = qDef.Name

上記のサンプルでは、同一クエリが既に存在する場合は、そのクエリに対してSQLを上書きしています。

クエリが存在しない場合は新たに作成を行います。

パススルークエリを利用する場合は、ACCESSの構文で作成したSQLはそのまま利用できないことがあります。

接続先のデータベースに合わせたSQLの構文で記述する必要があります。