Solved

ODBC Call Failed with MySQL Linked Tables in Access 2007

Posted on 2008-06-16
7
5,255 Views
Last Modified: 2013-12-25
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").refreshlinks '< 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?


0
Comment
Question by:noisecouk
7 Comments
 
LVL 30

Expert Comment

by:hnasr
ID: 21793824
Can you attach a database (minmal objects required) to check on mySQL setup.
0
 

Author Comment

by:noisecouk
ID: 21801207
I have attached a minimal database with just 4 of the linked tables for you to see how they have been set up. I have stripped out all of the forms, modules and references so you can ignore the security warning. If you require a version with more of the objects than ive left in, or samples of how my code uses the linked tables then let me know.
sample.mdb
0
 

Accepted Solution

by:
noisecouk earned 0 total points
ID: 21883402
Im not sure why, but on the affected systems Microsoft Access seems to remember which ODBC Data Sources (dsns) have failed and will not retry connecting to them until the whole database is closed, which isnt an option when a connection issue occurs while in the middle of something. Even when linked tables are explicitly refreshed by asking for the data source again it still reports "call failed". In fact deleting all the linked tables and adding them back wont work! It succeeds in adding the linked tables but they still cant be opened!

I have managed to come up with a fix for this issue. By deleting the linked tables, renaming the dsn and adding the linked tables back again everything seems to work again without having to close everything down.

Its not ideal but the DSN now includes an incrementing number, and each time a connection fault occurs:

- The server is pinged to make sure that there really is a connection
- The current dsn is retrieved to obtain its current number
- All the table names are copied to a temporary collection and the link tables deleted
- The registry is then edited to increment the dsn to the next number (which involes reading, copying and deleting keys as theres no rename function)
- The link tables are then redefined poitning to the new dsn

If connection errors repeatedly occur during the same session (e.g. every hour on a problematic vpn connection here), Access will remember each broken dsn and not allow reconnecting to it, which is why the dsn has to increment rather than alternate.

If anyone has this problem and wants to see the code let me know. Or if anyone can suggest a much simpler fix I would be very grateful.
0
Highfive + Dolby Voice = No More Audio Complaints!

Poor audio quality is one of the top reasons people don’t use video conferencing. Get the crispest, clearest audio powered by Dolby Voice in every meeting. Highfive and Dolby Voice deliver the best video conferencing and audio experience for every meeting and every room.

 

Expert Comment

by:absoffthewake
ID: 22760076
What exactly do you mean by DSN Number? Where can I find it? I have Access 2003, and am using DSN-less connections.

(I build the DSN Connection String manually and create the table using it)

Set Td = CurrentDb.CreateTableDef(stLocalTableName, dbAttachSavePWD, stRemoteTableName, stConnectString)
CurrentDb.TableDefs.Append Td
0
 

Expert Comment

by:absoffthewake
ID: 22762520
Actually, if you have the code I would like to see it!
0
 

Expert Comment

by:mizveggie
ID: 24526716
I would also like to see the code, I am experiencing the same issue and cannot find a work around.  I do not see any way to contact you directly.
0
 
LVL 7

Expert Comment

by:roycasella
ID: 24980522
YEs please... can I see the code
 
R
0

Featured Post

Free Trending Threat Insights Every Day

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

Join & Write a Comment

Does the idea of dealing with bits scare or confuse you? Does it seem like a waste of time in an age where we all have terabytes of storage? If so, you're missing out on one of the core tools in every professional programmer's toolbox. Learn how to …
You can of course define an array to hold data that is of a particular type like an array of Strings to hold customer names or an array of Doubles to hold customer sales, but what do you do if you want to coordinate that data? This article describes…
In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

746 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

16 Experts available now in Live!

Get 1:1 Help Now