How do you alter the ODBC connection string for existing MS-ACCESS pass-through queries in VBA

vba ms-access pass-through
Acoats picture Acoats · Sep 7, 2011 · Viewed 8.3k times · Source

I have a number of pass-through queries setup in a MS-Access database with pre-defined ODBC connection strings. The problem is the database can link to one of two MySQL databases. The user selects the db on starting the database and the system dynamically links the appropriate tables via VBA (this works fine) However I then need to alter the ODBC Connection strings in the existing queries to match the selected db. I therefore needa VBA function to loop through all the existing pass-through queries setting the ODBC connection string propertiy to the new connection string. Any ideas on how to do this?

Answer

Fionnuala picture Fionnuala · Sep 7, 2011

You can loop through the queries collection:

Dim qdf As QueryDef

For Each qdf In CurrentDb.QueryDefs
    If qdf.Type = dbQSQLPassThrough Then
        qdf.Connect = NewConnect
    End If
Next

You could also examine the Connect string with InStr(qdf.Connect) to test for the existence of the required database name.

Related questions

Access VBA Parameter in passthrough query to SQL Server

I have several queries in an MS Access database. Some of these use parameters. I use the following code in VBA to provide the query with these parameters: VBA Dim startDate As Date Dim endDate As Date Dim dbs As …

Read More
Execute SQL Server Pass-Through Query From Access VBA

I have an UPDATE pass through query saved in Access 2007. When I double-click on the pass through query it runs successfully. How can I get this query to run from VBA? I'd like it to run when my "splash screen" …

Read More
Code to loop through all records in MS Access

I need a code to loop through all the records in a table so I can extract some data. In addition to this, is it also possible to loop through filtered records and, again, extract data? Thanks!

Read More