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
– Christian, Commented Dec 9, 2012 at 16:40
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
– Christian, Commented Dec 9, 2012 at 16:51
it was the same as the table name that's why I confused it for the post - changed it to "user" — Christian
– Christian, Commented Dec 9, 2012 at 19:17
Can you create and run an Append query from the Access UI (not from VBA) with the same statement? — user20416
– user20416, Commented Dec 9, 2012 at 19:23

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

2

+100

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.

answered Dec 13, 2012 at 8:56

Matt Donnan

5,0233 gold badges23 silver badges32 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

Christian Over a year ago

That's what I'm already doing from Excel. But the problem is reproducible with Access itself. Just executing the VBA code '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")' in Access shows the same behavior.

Matt Donnan Over a year ago

@Christian CurrentProject.Connection still uses the ADODB string rather than DAO which is why I suggested this method. In access do you still get the error if you use CurrentDb.Execute rather than CurrentProject.Connection.Execute?

Christian Over a year ago

CurrentDb.Execute prints another error message: Runtime Error 3065: A select query could not be executed (translated from german). But: OpenRecordset works! So using DAO instead of ADODB seems to be solution. And it's enough to use it once, afterwards I can continue with ADODB. This is a real bug in Access isn't it? Thanks a lot for solving this!

Matt Donnan Over a year ago

@Christian I thought it was an Insert Into rather than Select query, true enough for DAO that Selects can't be executed. Note: Unless you only want basic Access queries with no UDF's or other interaction etc, then ADODB is ok, but for more involved procedures within Access DAO is a safer bet. And as you say, once this bit is done you can continue the rest with ADODB, glad to help : )

Collectives™ on Stack Overflow

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

1 Answer 1

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related