By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,658 Members | 1,501 Online
Bytes IT Community
Submit an Article
Got Smarts?
Share your bits of IT knowledge by writing an article on Bytes.

How to change BackEnd Database links. (Access 2003)

Expert 100+
P: 344
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
Expand|Select|Wrap|Line Numbers
  1. Option Compare Database
  2. Option Explicit
  3.  
  4. Global Const LIVECODE = "c:\bissau\BissauCode.mdb"
  5. Global Const LIVEDATA = ";DATABASE=z:\Bissau\BissauData.mdb"
  6. Global Const LIVEMDW = "z:\bissau\bissau.mdw"
  7.  

in the on-open event of the form I have the following
Expand|Select|Wrap|Line Numbers
  1. Private Sub Form_Open(Cancel As Integer)
  2. On Error GoTo Form_Open_Err
  3. LCode = LIVECODE
  4. LData = LIVEDATA
  5. LMDW = LIVEMDW
  6. Form_Open_Exit:
  7.    Exit Sub
  8. Form_Open_Err:
  9.    MsgBox Err.Description & " in Form_Open"
  10.    Resume Form_Open_Exit
  11. End Sub
  12.  
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.

Expand|Select|Wrap|Line Numbers
  1. Private Sub cmdDropAll_Click()
  2. On Error GoTo cmdDropAll_Click_Err
  3. Dim db As Database
  4. Dim td As TableDef
  5. Dim i As Integer, j As Integer, var As Variant
  6.  
  7. Dim strAttach As String
  8. Set db = CurrentDb
  9. For i = 0 To db.TableDefs.Count - 1
  10.     Set td = db.TableDefs(i)
  11.     strAttach = td.Connect
  12.     If Len(strAttach & "") > 0 Then
  13.         db.Execute "DROP TABLE " & td.Name & ";"
  14.     End If
  15. Next
  16. Set td = Nothing
  17. Set db = Nothing
  18. MsgBox "All Tables Dropped"
  19. cmdDropAll_Click_Exit:
  20.    Exit Sub
  21. cmdDropAll_Click_Err:
  22.   MsgBox Err.Description & " in cmdDropAll_Click"
  23.    Resume cmdDropAll_Click_Exit
  24. End Sub
  25.  
Next

Now to the nice bit, to re-attach to another drive. This is 'Activate Change'
Expand|Select|Wrap|Line Numbers
  1. Private Sub cmdChangeDrive_Click()
  2. On Error GoTo cmdChangeDrive_Click_Err
  3. Dim db As Database
  4. Dim td As TableDef
  5. Dim i As Integer, j As Integer, var As Variant
  6. Dim strAttach As String
  7. Dim strNewAttach As String
  8. Dim strNewCode As String
  9. Dim strNewMDW As String
  10.  
  11. Dim mdl As Module
  12. Dim intLine As Integer, strLine As String
  13.  
  14. strNewCode = NewLiveCode & Right(LCode, Len(LCode) - 1)
  15.  
  16. strNewAttach = Left(LData, 10) & NewLiveData & Right(LIVEDATA, Len(LIVEDATA) - 11)
  17.  
  18. strNewMDW = NewLiveMDW & Right(LMDW, Len(LMDW) - 1)
  19.  
  20.         DoCmd.OpenModule "basConstants"
  21.         ' Return reference to Module object.
  22.         Set mdl = Modules("basConstants")
  23.  
  24.         For intLine = 1 To mdl.CountOfDeclarationLines
  25.         strLine = mdl.Lines(intLine, 1)
  26.         If InStr(strLine, "LIVECODE") > 0 Then
  27.             mdl.ReplaceLine intLine, "Global Const LIVECODE = " & Chr(34) & strNewCode & Chr(34)
  28.         End If
  29.         If InStr(strLine, "LIVEDATA") > 0 Then
  30.             mdl.ReplaceLine intLine, "Global Const LIVEDATA = " & Chr(34) & strNewAttach & Chr(34)
  31.         End If
  32.         If InStr(strLine, "LIVEMDW") > 0 Then
  33.             mdl.ReplaceLine intLine, "Global Const LIVEMDW = " & Chr(34) & strNewMDW & Chr(34)
  34.         End If
  35.         Next
  36.         DoCmd.Close acModule, "basConstants", acSaveYes
  37. Set db = CurrentDb
  38. For i = 0 To db.TableDefs.Count - 1
  39.     Set td = db.TableDefs(i)
  40.     strAttach = td.Connect
  41.     If Len(strAttach & "") > 0 Then
  42.         td.Connect = strNewAttach
  43.         td.RefreshLink
  44.     End If
  45. Next
  46. Set td = Nothing
  47. Set db = Nothing
  48. MsgBox "All Tables Changed"
  49. cmdChangeDrive_Click_Exit:
  50.    Exit Sub
  51. cmdChangeDrive_Click_Err:
  52.    MsgBox Err.Description & " in cmdChangeDrive_Click"
  53.    Resume cmdChangeDrive_Click_Exit
  54. End Sub
  55.  
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.
Attached Files
File Type: zip LinkingCode.zip (20.6 KB, 327 views)
Feb 17 '11 #1
Share this Article
Share on Google+