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;SERVER=x.x.x.x;UID=user;PASSWORD=pass;PORT=3306;SOCKET=;OPTION=3;STMT=;"
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/ can I drop the connection or clear this "cache" ?


Who is Participating?
Leigh PurvisDatabase DeveloperCommented:
Hmmm mySQL. :-S
Hadn't spotted that til now.  You can try running a function like this just for information purposes

Function fIsConnected() As Boolean

    Dim cnn As New ADODB.Connection
    Dim strConn As String
    strConn = "DRIVER={MySQL ODBC 3.51 Driver};SERVER=x.x.x.x;DATABASE=XXX;USER=user;PASSWORD=pass;OPTION=3;"
    cnn.Open strConn
    If cnn.State = adStateOpen Then
        MsgBox "ADO connection established", vbInformation
        fIsConnected = True
    End If
End Function

If that worked you should then be able to go on and open a recordset as required...
One question though... why do you connect and then drop the link at all?
Leigh PurvisDatabase DeveloperCommented:
Are you then totally unable to open *any* connection to the server?
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).
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
andrewbrookerAuthor Commented:
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 fact I close the connection when I'm 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.

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.

andrewbrookerAuthor Commented:
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?

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.