mySQL-query from Microsoft Access VBA fails if not interactive?

Question

I'm fighting with a VBA non-interactive problem that really bugs me: I have a pass-through query to a mysql database, which works well if double-clicked by the user. But it fails if called from VBA automation session (called from excel) if no interaction was done first. Most strange: it works from VBA after clicking it on the GUI for a while (odbc mysql connection timeout possibly).

The passthrough-query has it's password in the DSN and in the connection string (to sort out problems with the store). The behavior is the same with a linked table.

Problematic VBA code called looks like this:

CurrentProject.Connection.Execute "INSERT INTO [SomeLocalTable] (id) SELECT id FROM [somePassThroughOrLinkedMySQLTable]"

The error is a generic odbc connection failure 80004005.

While this type of query works all the time:

Dim cnn As New ADODB.Connection
cnn.Open ("Driver=MySQL ODBC 5.2w Driver;SERVER=myserver;UID=user;DATABASE={db};PORT=3306;DFLT_BIGINT_BIND_STR=1;PWD=secret")

Can I "initialize" the passthrough query like the UI does to make it work? Or can I use the second type of query to insert into a local MS Access table?

Environment: Win8-64bit, Office2013, mysql-odbc-5.2w

Hey, I did not find this article yet. Even though I think it does not apply, as the credentials are already stored in the connection string, I will try it, first. Stay tuned... — Christian
Nope, won't help: Like suggested there I executed 'CurrentProject.Connection.Execute("SELECT * FROM [ODBC;DSN=vTiger;Driver=MySQL ODBC 5.2w Driver;SERVER=myserver;UID=table;DATABASE={db};PORT=3306;DFLT_BIGINT_BIND_STR=1;PWD=secret].mysqlTable WHERE FALSE")' - it fails with the same error if (and only if) the connection was not established with the GUI before. — Christian
it was the same as the table name that's why I confused it for the post - changed it to "user" — Christian
Can you create and run an Append query from the Access UI (not from VBA) with the same statement? — Zev Spitz

Matt Donnan Matt Donnan · Accepted Answer · 2012-12-13T08:56:37

I think the problem is happening because you are referring to the Access database as an external ADODB data source, instead I would use an instance of Access in order to run the query via DAO. The sample sub below creates an invisible instance of Access and executes the query, and then quits once finished. I've used late binding in this example with DAO version 3.6 so you may need to amend that bit slightly or use early binding:

Sub ExecPassThru()

Dim acApp As Object
Dim acDb As Object

    Set acApp = CreateObject("Access.Application")
    Set acDb = CreateObject("DAO.DBEngine.36") 'May need slight alteration
    acApp.OpenCurrentDatabase ("C:\db1.mdb") 'Amend as required

    Set acDb = acApp.CurrentDb

    acDb.Execute "INSERT INTO [SomeLocalTable] (id) SELECT id FROM [somePassThroughOrLinkedMySQLTable]"

    acDb.Close
    acApp.Quit 2

End Sub

This was coded using Excel 2003 but should be easy enough to translate to later versions of office.

mySQL-query from Microsoft Access VBA fails if not interactive?

1 Answers