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

Does this answer your question? [run stored procedure and return values from VBA](https://stackoverflow.com/questions/8249706/run-stored-procedure-and-return-values-from-vba) — June7, Mar 14 '21 at 02:14

score 1 · Accepted Answer · edited Apr 19 '21 at 17:12

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.

Thanks for the help! The problem is solved. Thank very much Albert. — Hanni, Mar 14 '21 at 14:24

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

1 Answers1

Linked