problem making a passthrough query via oracle in Access

Question

I am having a problem with making an Access passthrough query in the following code which uses Oracle as the database:

Sub doit()
    Dim db As DAO.Database
    Dim qdf As DAO.QueryDef
    Dim rs As DAO.Recordset

    Set db = CurrentDb
    Set qdf = db.CreateQueryDef("")
    c = "Driver={Oracle in OraClient12102_64};dbq=<<location>>;PUID=<<myuid>>;PWD=<<mypwd>>;"
    qdf.Connect = c


    qdf.SQL = "SELECT * FROM SYS.DUAL"

    qdf.ReturnsRecords = True

    Set rs = qdf.openRecordSet
End Sub

The error message I get is Invalid connection string in pass-through query. I use this connection string successfully when using ADODB.connection; however, it fails in this instance.

Answer 1

In DAO, when using an ODBC connection, you should prefix it with ODBC;

Adjust your code accordingly:

 c = "ODBC;Driver={Oracle in OraClient12102_64};dbq=<<location>>;PUID=<<myuid>>;PWD=<<mypwd>>;"