I use the code below to attach frontend MS Access 2000 .mdb tables to .mdb backends. Users execute this code on their frontends in order to "work locally" off the server or with network connection down. They can continue to view data and run reports off the network anywhere for whatever reason. I use the same code with different paths to attach them back to the server or switch them between servers. I also use it for training mode as data changes go locally only. It works nicely.

Now, I'm switching over to MS SQL Server tables on all backends, connecting the frontend through ODBC to two different SQL databases.

The Mission: Create code to attach to SQL databases through ODBC and detach from a local .mdb backend (and vice versa) using a similar method as listed below. The table names on the .mdb must be the same as the once attached to the SQL tables (dbo_tblData) so the frontend code and queries can continue to run unaware of the change (some code conditional statements are necessary).

My challenge is how to use VBA to attach to SQL through ODBC...

Will you accept the mission?


Here is the code for .mbd backends:
Private Sub cmdRelinkLocally_Click()
If MsgBox("Go Local?", vbYesNo, "Relink Tables Locally?") = vbYes Then

Call UpdateLocalBackends() 'Fctn updates local .mdb with network data

Dim db As DAO.Database
Dim tdf As DAO.TableDef
Dim strPath As String
Dim strPath2 As String
'Two different backends
strPath = "C:\Backend1.mdb"
strPath2 = "C:\Backend2.mdb"
Set db = CurrentDb()

For Each tdf In db.TableDefs
If (tdf.Attributes And dbAttachedTable) = _
dbAttachedTable Then
If tdf.Name = "tblSomeTable" Or tdf.Name = "tblOtherTable" Then
tdf.Connect = ";DATABASE=" & strPath2
Else 'All other attached tables
tdf.Connect = ";DATABASE=" & strPath
End If
End If
MsgBox "You are local! Any data changes will be lost."
End If
End Sub