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/settings...how can I drop the connection or clear this "cache" ?

Thanks,
Andrew








andrewbrookerAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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).
rockiroadsCommented:
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
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 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
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?

Thanks
-Andrew
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
        cnn.Close
    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?

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.