changing where linked tables point to

[galorin]

I am developing an app that uses MySQL as a backend. As a part of the requirement, if no connection to the MySQL server is available, data must still be accessible. I am getting around this via a locally installed, read-only MySQL database.

Now, I am using linked tables in some areas, and I need to change where these linked tables are pointing to, based on an attempted connection. The global variables, DB_CONNECT and DB_CONNECT_L hold the ADODB connection string for the remote and local servers respective. I also have a system in place that enables/disables buttons based on the boolean value of isLocal

The following code is in the first page that loads in my application. It is producing errors in places, from --ODBC call failed to operation not allowed when object is open. It is proving rather frustrating, as sometimes the code works as it should, other times it doesn't. Where am I going wrong, and how can I test ODBC calls to determine if Access is to blame? I can connect via MySQL command line, and ODBC sources in the Administrative tools.

Private Sub Form_Load()
Set cn = New ADODB.Connection
On Error GoTo connErr
With cn
 If .State = 0 Then
  .ConnectionString = DB_CONNECT
  .Open
  islocal = False
  Call refreshtables("contacts_remote", "192.168.101.14")
  MsgBox "Connected to remote server"
End If
 End With

Set cmd = New ADODB.Command
With cmd
 .CommandType = adCmdUnknown
End With
  
sub_exit:
Exit Sub

connErr:
MsgBox Error
Call localconn
Resume sub_exit
End Sub

Private Sub localconn()
On Error GoTo err2
 With cn
  .ConnectionString = DB_CONNECT_l
  .Open
 End With
 Call refreshtables("contacts_local", "localhost")
  islocal = True
 
  MsgBox "Connected to local server"

 
 Set cmd = New ADODB.Command
With cmd
 .CommandType = adCmdUnknown
End With

exit_subs:
Exit Sub

err2:
 MsgBox Error & Chr(13) & "Couldn't connect to any databases, please contact system administrator", vbOKOnly
 Resume exit_subs
End Sub

Private Sub refreshtables(dbname As String, dbLoc As String)
    Dim Dbs As Database
    Dim Tdf As TableDef
    Dim Tdfs As TableDefs
    Dim strConn As String
    
    Set Dbs = CurrentDb
    Set Tdfs = Dbs.TableDefs
    
    On Error GoTo refErr:
    
    For Each Tdf In Tdfs
    If Tdf.SourceTableName <> "" Then
     strConn = "ODBC;DATABASE=contacts;DSN=" & dbname & ";OPTION=4196352;PORT=0;SERVER=" & dbLoc & ";" 'TABLE=" & Tdf.SourceTableName
     'MsgBox Tdf.SourceTableName & Chr(13) & strConn
     Tdf.Connect = strConn
     Tdf.RefreshLink
    End If
    Next

refErr:
MsgBox Error & Chr(13) & "couldn't refresh tables"

End Sub