How to change BackEnd Database links. (Access 2003)

[Lysander]

I'm sure many of you use backend and frontend databases. The backend holds all the tables, the frontend holds all the code.

If you are developing for a multi-user system, you may have the same problem I have. As the developer, my front and back end and mdw security file are on my C: drive.

Then end users need the back end, and the security file on the Z: drive (or H: or whatever)

With dozens of tables to swap each time I sent new code out, I needed to do this automaticaly, so I wrote the following bit of code.

This works in Access 2000 and 2003, not sure about Access 2010.

First, I have a simple form, "fdlgDropAll" that has two options.

It allows me to drop all attached tables, or it allows me to change the attachment from one drive to another.

This form has 3 text boxes that display

path for live code
path for live data
path for live mdw

then a button to 'drop all'

and 3 boxes that ask for
drive for live data
drive for live code
drive for live mdw

finally a button to activate change, and a close button.

Ok, so how does it work?

First, I have a module called basConstants that stores, amongst other things, the expected path of the various files. This is used on startup so that I don't accidently send a database out to Africa or India pointing to the wrong files.

basConstants holds the following

Option Compare Database
Option Explicit
 
Global Const LIVECODE = "c:\bissau\BissauCode.mdb"
Global Const LIVEDATA = ";DATABASE=z:\Bissau\BissauData.mdb"
Global Const LIVEMDW = "z:\bissau\bissau.mdw"
 

in the on-open event of the form I have the following

Private Sub Form_Open(Cancel As Integer)
On Error GoTo Form_Open_Err
LCode = LIVECODE
LData = LIVEDATA
LMDW = LIVEMDW
Form_Open_Exit:
   Exit Sub
Form_Open_Err:
   MsgBox Err.Description & " in Form_Open"
   Resume Form_Open_Exit
End Sub
 

so the form opens, showing you the current links..

NEXT

DropAll is very simple, it will drop all links to attached tables, here is the code for it.

Private Sub cmdDropAll_Click()
On Error GoTo cmdDropAll_Click_Err
Dim db As Database
Dim td As TableDef
Dim i As Integer, j As Integer, var As Variant
 
Dim strAttach As String
Set db = CurrentDb
For i = 0 To db.TableDefs.Count - 1
    Set td = db.TableDefs(i)
    strAttach = td.Connect
    If Len(strAttach & "") > 0 Then
        db.Execute "DROP TABLE " & td.Name & ";"
    End If
Next
Set td = Nothing
Set db = Nothing
MsgBox "All Tables Dropped"
cmdDropAll_Click_Exit:
   Exit Sub
cmdDropAll_Click_Err:
  MsgBox Err.Description & " in cmdDropAll_Click"
   Resume cmdDropAll_Click_Exit
End Sub
 

Next

Now to the nice bit, to re-attach to another drive. This is 'Activate Change'

Private Sub cmdChangeDrive_Click()
On Error GoTo cmdChangeDrive_Click_Err
Dim db As Database
Dim td As TableDef
Dim i As Integer, j As Integer, var As Variant
Dim strAttach As String
Dim strNewAttach As String
Dim strNewCode As String
Dim strNewMDW As String
 
Dim mdl As Module
Dim intLine As Integer, strLine As String
 
strNewCode = NewLiveCode & Right(LCode, Len(LCode) - 1)
 
strNewAttach = Left(LData, 10) & NewLiveData & Right(LIVEDATA, Len(LIVEDATA) - 11)
 
strNewMDW = NewLiveMDW & Right(LMDW, Len(LMDW) - 1)
 
        DoCmd.OpenModule "basConstants"
        ' Return reference to Module object.
        Set mdl = Modules("basConstants")
 
        For intLine = 1 To mdl.CountOfDeclarationLines
        strLine = mdl.Lines(intLine, 1)
        If InStr(strLine, "LIVECODE") > 0 Then
            mdl.ReplaceLine intLine, "Global Const LIVECODE = " & Chr(34) & strNewCode & Chr(34)
        End If
        If InStr(strLine, "LIVEDATA") > 0 Then
            mdl.ReplaceLine intLine, "Global Const LIVEDATA = " & Chr(34) & strNewAttach & Chr(34)
        End If
        If InStr(strLine, "LIVEMDW") > 0 Then
            mdl.ReplaceLine intLine, "Global Const LIVEMDW = " & Chr(34) & strNewMDW & Chr(34)
        End If
        Next
        DoCmd.Close acModule, "basConstants", acSaveYes
Set db = CurrentDb
For i = 0 To db.TableDefs.Count - 1
    Set td = db.TableDefs(i)
    strAttach = td.Connect
    If Len(strAttach & "") > 0 Then
        td.Connect = strNewAttach
        td.RefreshLink
    End If
Next
Set td = Nothing
Set db = Nothing
MsgBox "All Tables Changed"
cmdChangeDrive_Click_Exit:
   Exit Sub
cmdChangeDrive_Click_Err:
   MsgBox Err.Description & " in cmdChangeDrive_Click"
   Resume cmdChangeDrive_Click_Exit
End Sub
 

Thus, this simple form allows me quickly to change my c: drive development code to the multi-user Z: drive code, without the hassle of manually dropping dozens of tables and re-linking, maybe forgetting one table, or maybe deleting one local table. A task I have to do 2-3 times a week on multiple databases.

Hopefully tomorrow I can attach a sample database with all the code in it.

Again, this is written in Access 2003.

Attached is an Access 2003 database with the form fdlgDropAll containing all the code from above.