Providing ODBC database fallover

[galorin]

My project uses linked tables and pass-through queries to a MySQL Server in our main office. I have put in a check when the first form loads that checks if this database is available. If it isn't, the program notifies the end user, and then disables all controls on the main form.

This project is also to be used by people on the road, and as such will not always have a connection to the MySQL Server. Worst case is that they will be using a 3G Mobile card in the laptop, which would give them an intermittent connection to the Server. I am providing a locally installed, read-only copy of MySQL Server on their computers in a truecrypt protected area, and the means for disabling buttons related to adding or updating information to get around this. What I need, is some method for checking that they can talk to the main MSQL Server, and then fall over to the local if that is not the case.

I have the following sub that should change the ODBC connection string for all the tables, but not all the pass-through queries.

Private Sub refreshtables(dbname As String) [COLOR=green]dbname is either contacts_local(RO) or contacts_remote (RW)[/color]
    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;DSN=" & dbname & "TABLE=" & Tdf.SourceTableName
     Tdf.Connect = strConn
     Tdf.RefreshLink
    End If
    Next

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

End Sub

So I need to change this so it changes pass-through ODBC connection strings, and, in another sub, check that it can talk to the remote server, then local server and warn of change, then none and give big warning message. Any thoughts or ideas?

 

[Remou]

I have not tested this, but it seems to me that you could do something similar with queries:

'Example
strConn = strConn = "odbc;dsn=SQLServer;uid=UsersGroup;pwd=password;database=LTD;"

For Each qdf In CurrentDb.QueryDefs
    If qdf.Type = dbQSQLPassThrough Then
        qdf.Connect = strConn
    End If
Next

 

[galorin]

I'll try that out remou. Thanks.

Any ideas on how I can neatly do the checks for server existence? I use the following currently in the onload() event for my main form. Islocal is a global variable that is part of my methodology for disabling buttons.

This section does the job pretty well, but needs to check for more than just the one server. I also use four other subs similar to this, that connect to the database, get data, put data, and close the connection when finished. I would want to modify the connecting sub to do the same checks and refreshes.

Private Sub Form_Load()
On Error GoTo connErr
Set cn = New ADODB.Connection
With cn
 If .State = 0 Then
  .ConnectionString = DB_CONNECT
  .Open
  islocal = False
 End If
End With

cn.CLose
  
sub_exit:
Exit Sub

connErr:
MsgBox "Sorry, couldn't connect to database.  Please try again later", vbOKOnly
Me.Contacts.Enabled = False
Me.OpenQuotes.Enabled = False
Me.OpDirectory.Enabled = False
Me.RepBtn.Enabled = False
Resume sub_exit

End Sub

and my connecting sub, stored in the Modules section

Public Sub connectDB()

Set cn = New ADODB.Connection
On Error GoTo connErr
With cn
 If .State = 0 Then
  .ConnectionString = DB_CONNECT
  .Open
  islocal = False
End If
 End With

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

connErr:
MsgBox "Lost connection to server.  Please try again later", vbExclamation
Resume sub_exit

exit sub