Invalid Connection String In Pass-Through Query In MS Access VBA

Question

I tried using Pass-Through query to call mysql stored procedure from MS Access VBA. This is the code:

Dim qdf As DAO.QueryDef
    Dim rst As DAO.Recordset
    Set qdf = CurrentDb.CreateQueryDef("")
    qdf.Connect = "DRIVER={MySQL ODBC 5.3 Unicode Driver};SERVER=localhost;PORT=3306;DATABASE=accounting_supp_db;User=xxx;Password=xxx;Option=3"
    qdf.SQL = "CALL MyStoredProcedure('10156','2021-03-03','2021-03-10')"
    qdf.ReturnsRecords = True
    Set rst = qdf.OpenRecordset
    
    rst.Close
    Set rst = Nothing
    Set qdf = Nothing

But it return error 3305: Invalid Connection String In Pass-Through Query. Is there somothing wrong with the connection string?

thx

Answer 1

Well, one way to figure this out?

Simply use the external data from the ribbon - and link a table from access to MySQL. Get that working - once you do?

Then do this in your code:

dim rst  as DAO.RecordSet
With CurrentDB.queryDefs("MyPTQuery")
   .Connection = currentdb.tableDefs("The working linked table").Connection
   .SQL = "CALL MyStoreProc('10156','2021-03-03','2021-03-10')"
    set rst = .OpenRecordSet
End if

You could I suppose add to above .ReturnsRecords = True, but then again?

Well, create that one PT query - set the connection correct. Then you can do this in code:

Dim rst1     as DAO.RecordSet
Dim rst2     as DAO.ReocrdSet

With Currentdb.tableDefs("MyPTQuery")
   .SQL = "CALL MyStoredProcedure('10156','2021-03-03','2021-03-10')"
    set rst1 = .OpenRecordSet
END with

With Currentdb.tableDefs("MyPTQuery")
   .SQL = "CALL MyStoredProcedure('10777','2021-04-03','2021-05-10')"
    set rst2 = .OpenRecordSet
End With

Note how we don't mess with creating a query def. And note how we can use the ONE pt query over and over.

And it gets better Say you want that stored procedure for a report? Well base the report on "MyPTQuery"

Then do this:

With Currentdb.tableDefs("MyPTQuery")
   .SQL = "CALL MyStoredProcedure('10777','2021-04-03','2021-05-10')"
End With

docmd.OpenReport "rptCustomerProjects", acViewPreview

In fact, you can write the above like this:

Currentdb.tableDefs("MyPTQuery").SQL = "CALL MyStoredProcedure('10777','2021-04-03','2021-05-10')"
docmd.OpenReport "rptCustomerProjects", acViewPreview

So I quite much recommend that you SAVE the connection string in the PT query. That way, your code has no messy connection strings - and such connections are now "out" of your code - you can easy change the connection for the whole database - not change any code.

So, when you run your table re-link code? Have that re-link code ALSO update any PT query. That way you can now re-link and point your application to a test database, or production one, or whatever. So, no connection strings in code are the result of the above.

Regardless of above? Get a linked table working - and then use that "steal" the known connection from the linked table and shove it into the connection for the PT query as per first example above.