andrewbrooker
asked on
Relinking Tables after MySQL Timeout
Access 2002/XP
MyODBC 3.51
Linked table to MySQL database.
After 60 of no activity MySQL drops connection. It appears that Access still thinks the connection is up.
I use a DSN-less connection in VBA to attempt to reconnect...
strconnect = "ODBC;DRIVER={MySQL ODBC 3.51 Driver};DESC=;DATABASE=XXX
DoCmd.TransferDatabase acLink, "ODBC", strconnect, acTable, "Remote", "Local"
All (queries, functions, reports, etc) work fine until after 60 of inactivity. Then when attempting to reconnect I receive:
Runtime Error 3011
The Microsoft Jet database engine could not find the object "remote" make sure the object exists and that you spell its name and the path correctly.
The scenerio is:
I connect...I run a query...then I drop the table. If I attempt to reconnect within 60 sec it will work. (regardless of dropping the table or not)
I have even tried deleteing the tabledefs....without success.
If I leave the tabledefs intact, I can run the Linked Table Manager, and then I can reconnect (most of the time). Otherwise I must close Access and reopen.
I do not have permission to modify server settings. This is a hosted MySQL server. I can absolutly confirm that it is within 60 secs, as I can watch the server connection drop after 60 of 'sleep' per the MySQL Admin Console.
Since it seems that Access is caching the connection/settings...how can I drop the connection or clear this "cache" ?
Thanks,
Andrew
MyODBC 3.51
Linked table to MySQL database.
After 60 of no activity MySQL drops connection. It appears that Access still thinks the connection is up.
I use a DSN-less connection in VBA to attempt to reconnect...
strconnect = "ODBC;DRIVER={MySQL ODBC 3.51 Driver};DESC=;DATABASE=XXX
DoCmd.TransferDatabase acLink, "ODBC", strconnect, acTable, "Remote", "Local"
All (queries, functions, reports, etc) work fine until after 60 of inactivity. Then when attempting to reconnect I receive:
Runtime Error 3011
The Microsoft Jet database engine could not find the object "remote" make sure the object exists and that you spell its name and the path correctly.
The scenerio is:
I connect...I run a query...then I drop the table. If I attempt to reconnect within 60 sec it will work. (regardless of dropping the table or not)
I have even tried deleteing the tabledefs....without success.
If I leave the tabledefs intact, I can run the Linked Table Manager, and then I can reconnect (most of the time). Otherwise I must close Access and reopen.
I do not have permission to modify server settings. This is a hosted MySQL server. I can absolutly confirm that it is within 60 secs, as I can watch the server connection drop after 60 of 'sleep' per the MySQL Admin Console.
Since it seems that Access is caching the connection/settings...how can I drop the connection or clear this "cache" ?
Thanks,
Andrew
Just a thought, have you checked to see if you have the latest ODBC driver? Perhaps there was an issue with an earlier version you may be working on
http://bugs.mysql.com/bug.php?id=7802
http://bugs.mysql.com/bug.php?id=7802
ASKER
I have no problem, if I continuously write or query. The connection if fine, unless 60 seconds on inactivity the server drops the connection.
I don't have a problem with the fact that it drops...in fact I close the connection when I'm done...so I think...I drop the table. But the connection of sorts remains in the background, and b/c the server drops let's say 50 seconds later. When I go to re-attach the table, it doesn't respond as....the connection is gone...Access isn't attempting a server reconnect when it is attaching the table.
I will try to connect to a different table. But I would imagine since it is the same server, it won't attempt a reconnect...but I will try....I will also try ADO.
rockiroads:
Yes I have the latest driver. I downloaded it before starting the project. And when I had this problem, I uninstalled and re-downloaded from a different mirror.
Thanks
I don't have a problem with the fact that it drops...in fact I close the connection when I'm done...so I think...I drop the table. But the connection of sorts remains in the background, and b/c the server drops let's say 50 seconds later. When I go to re-attach the table, it doesn't respond as....the connection is gone...Access isn't attempting a server reconnect when it is attaching the table.
I will try to connect to a different table. But I would imagine since it is the same server, it won't attempt a reconnect...but I will try....I will also try ADO.
rockiroads:
Yes I have the latest driver. I downloaded it before starting the project. And when I had this problem, I uninstalled and re-downloaded from a different mirror.
Thanks
ASKER
Sorry, I have been busy with another project.
Given the info in the original post. How would I connect using ADO?
Could you provide a VBA code example?
Thanks
-Andrew
Given the info in the original post. How would I connect using ADO?
Could you provide a VBA code example?
Thanks
-Andrew
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
An ADO connection? (Using the ODBC provider)
Are you unable to prevent the connection dropping out?
Maintaining a persistently open recordset does nothing? (Present just for that purpose - so it does nothing else).