MySQL query from Microsoft Access VBA crashes if not interactive?

Question

I am struggling with a non-interactive VBA problem that really scares me: I have an end-to-end query to the mysql database that works well if the user double-clicks. But it fails if called from a VBA automation session (called from excel), if there was no interaction at first. The strangest thing: it works with VBA after clicking on it in the GUI for a while (maybe the mysql connection timeout may be).

There is a password in the proxy request in the DSN and in the connection string (to fix storage problems). The behavior is the same with the linked table.

The problematic VBA code looks like this:

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

The error is a netbc 80004005 general connection failure.

So far, this type of query is working 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” a transit request, as the user interface does to make it work? Or can I use the second type of query to insert into the local MS Access table?

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

Answer 1

I think the problem arises because you are referencing the Access database as an external ADODB data source, instead I would use an Access instance to execute the query through the DAO. The following example creates an invisible Access instance and executes the request, and then exits. I used the last binding in this example with DAO version 3.6, so you may need to change this bit a bit 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 encoded using Excel 2003, but should be easy enough to translate to later versions of the office.