MS Access dropping connection to ODBC tables after long period of inactivity

Question

I have an Access 2010 front end which has multiple linked tables on a local MySQL server (Windows) connected via ODBC. Most of the time it works just fine however Access drops the connection if left for several hours with Access open but not being used. When a form is opened again I get an ODBC error.

The problem is fixed by closing Access and opening it again.

I'm guessing that it's a timeout issue but I'm not an Access expert. It seems that I need a way of checking for a valid ODBC connection and if necessary, re-connecting?

Any ideas?

Answer 1

Unfortunately there is no readily available way at this point in time to resolve this issue.

Often the issue is the result of power management on the computer. If the computer goes to sleep, or the computer puts the network card to sleep, then you in trouble and you have to re-start Access.

So if your computer sleeps, or puts the network card to sleep, you often loose the connection - once lost you have to re-start.

Also some hardware with windows 10 also has issues since the DEFAULT setting for windows 10 is fast start up (this means a copy of the kernel and MOST important a copy of the drivers loaded and initialized are written to disk and used to shorten boot/start up time).

If you seeing this issue on windows 10, then I would consider disabling fast start up as that will “always” force a cold re-initializing of the drivers (such as those for the network card). I would also consider turning off the “sleep” mode if that’s been determined to be an issue here (and that such offending computers are seeing this issue after a wake up).

Edit: I should also point out that some "test" for a ODBC connection will not fix this issue since as noted, once lost, you have to re-start Access.

Answer 2

You could try an old trick - at launch to open a simple form bound to a table and hide that form, and leave it open.

Answer 3

I rarely worked during several hours on MySQL tables so I never experienced this issue, but IMO it sounds like a driver-related issue or feature.

Waht I would do in this case is to refresh the tables' connections in VBA, looping over your TableDef objects.

Make a hidden form with a 1 hour timer for instance, and add the following code in the form_timer:

Sub Form_Timer()

    Dim tDef As DAO.TableDef
    Dim strConString As String

    Dim strMySQLServer As String
    Dim strMySQLDB As String
    Dim strMySQLUser As String
    Dim strMySQLPass As String

        ' ADAPT THIS :
        strMySQLServer = ""
        strMySQLDB = ""
        strMySQLUser = ""
        strMySQLPass = ""

     strConString = "ODBC;DRIVER={MySQL ODBC 5.1 Driver};Server=" & strMySQLServer & ";Database=" & strMySQLDB & ";Uid=" & strMySQLUser & ";Pwd=" & strMySQLPass & ";Option=3"

     For Each tDef In CurrentDb.TableDefs

        ' You probably have to adapt the folowing if
        ' Debug and inspect  the connection string of your actual MySQL Linked tables

         If Left(tDef.Connect, 4) = "ODBC" And InStr(tDef.Connect, "MySQL") > 0 Then
             tDef.Connect = strConString
             tDef.RefreshLink
         End If

     Next tDef

End Sub

Watch my comments carefully, espacially if you have linked table to several MySQL databases. It will break some of them without adaptation.

You might also have to adapt the connection string following your MySQL driver, or to use the DSN entry that you specified in the ODBC control panel instead.