I have an Access 2007 frontend which connects to several linked tables on a MySQL server using ODBC, through VBA. On my Windows XP Professional SP3 machine with MySQL ODBC Connector 3.51 I have no problems using this frontend. If the network goes down I will get an 'ODBC - Call failed' when I try to do something. Once the network connection is reestablished the operation resumes successfully.
On a Windows Vista Ultimate laptop, also with Access 2007, the frontend will suddenly stop working after 30-60 minutes of running. Any operation which opens a recordset will pop up with an "ODBC - Call Failed" message (runtime error 3146). This will happen predictibly when there is a connection interruption and I suspect out of office there is some minor glitch with the VPN causing a brief otherwise unnoticeable connection interruption.
The problem I am having is that although my pc will reconnect and resume as soon as a connection is re-established, the Vista machine will remain unable to reconnect and the "odbc - call failed" error persists. This is despite the fact that in ODBC settings in control panel the connecion test will succeed. I have very carefully been through all the Microsoft Access and ODBC connector settings and made sure all the same options and timeouts are set on both systems. On the Vista machine I have also tried uninstalling the 32-bit 3.51 driver and tried the 64-bit 5.1 driver but this has not changed anything. Once the "call failed" message appears, the only way to get the database to work is to close the frontend entirely and re-open it. After doing this, everything will immediately work as normal.
I have investigated ways of manually reconnecting to the server but nothing seems to work. For example, refreshing the table links in vba will still cause the "call failed" message to appear. Even deleting the table definitions and recreating them will bring up the "call failed" message at the point of appending the new tabledef to the current database's tabledefs collection.
e.g
currentdb.tabledefs("Test"
).refreshl
inks '< odbc call failed
or:
Dim NewTD as New TableDef
currentdb.tabledefs.delete
"Test"
newtd.connect = "odbc;DSN=noise" ' I have tried this with both a short connection string specifiying just the DSN and a long one with full server connection details
newtd.sourcetablename = "Test"
currentdb.tabledefs.append
newtd '< odbc call failed
Double clicking on a linked table will pop up with "odbc - call failed. followed by "The MySQL Server has gone away." It is just puzzling because my pc will reconnect and resume while the Vista pc will just refuse to work until the database is closed and reopened.
I would be very grateful if anyone could suggest what I could change on the vista pc so that Access/ODBC will actually reconnect after a failure instead of getting stuck. Alternatively are there are any other reconnection operations I could try in my code when it happens other than the ones I have mentioned above?
Start Free Trial
