Relinking Tables after MySQL Timeout

Posted on 2006-04-03
Last Modified: 2011-09-20
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" ?


Question by:andrewbrooker
    LVL 44

    Expert Comment

    by:Leigh Purvis
    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).
    LVL 65

    Expert Comment

    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

    Author Comment

    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.


    Author Comment

    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?

    LVL 44

    Accepted Solution

    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?

    Featured Post

    Looking for New Ways to Advertise?

    Engage with tech pros in our community with native advertising, as a Vendor Expert, and more.

    Join & Write a Comment

    Introduction When developing Access applications, often we need to know whether an object exists.  This article presents a quick and reliable routine to determine if an object exists without that object being opened. If you wanted to inspect/ite…
    Regardless of which version on MS Access you are using, one of the harder data-entry forms to create is one where most data from previous entries needs to be appended to new records, especially when there are numerous fields and records involved.  W…
    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…
    With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…

    734 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

    21 Experts available now in Live!

    Get 1:1 Help Now