How to refresh linked tables if Access disconnects from MySQL database server?

Question

So, I need a way to refresh the linked tables in my Access Database so that if the Internet disconnects for some reason the ODBC won't have an error when a query is sent and simply refreshes to see if the query can be sent again. However, the Access database doesn't reconnect for some reason when the Internet comes back up. Is there a way, in VBA, to refresh the linked tables if this does happen?

Answer 1

Would the .RefreshLink method do what you want?

There's an example here: https://msdn.microsoft.com/en-us/library/office/ff198349.aspx

Another solution would be to reconnect to the remote database calling this function.

Function ConnectODBC(ByVal strDsn As String, ByVal strDatabase As String, ByVal strUserName As String, ByVal strPassword As String)

    Dim qdf As DAO.QueryDef
    Dim rst As DAO.Recordset
    Dim strConnection As String

    strConnection = "ODBC;DSN=" & strDsn & ";" & _
                    "DATABASE=" & strDatabase & ";" & _
                    "UID=" & strUserName & ";" & _
                    "PWD=" & strPassword

    Set qdf = CurrentDb.CreateQueryDef("")

    With qdf
        .Connect = strConnection
        .SQL = "SELECT 1;"
    End With

    Set rst = qdf.OpenRecordset(dbOpenSnapshot, dbSQLPassThrough)
    ConnectODBC = True

    Set rst = Nothing
    Set qdf = Nothing

End Function