MS-Access Tips for Serious Users

Provided by Peter Vukovic.

Reconnect Attached tables on Start-up

If you have an Access application split into DATA.MDB and PRG.MDB (see Split your MDB file into data and application ), and move the files to a different directory, all tables need to be reconnected. Peter Vukovic's function handles the reconnection.

If your data and program are in different folders, see Dev Ashish's solution: Relink Access tables from code

Function Reconnect ()
'**************************************************************
'*     START YOUR APPLICATION (MACRO: AUTOEXEC) WITH THIS FUNCTION
'*     AND THIS PROGRAM WILL CHANGE THE CONNECTIONS AUTOMATICALLY
'*     WHEN THE 'DATA.MDB'  AND THE 'PRG.MDB'
'*     ARE IN THE SAME DIRECTORY!!!
'*                  PROGRAMMING BY PETER VUKOVIC, Germany
'*                  100700.1262@compuserve.com
'* ************************************************************
Dim db As Database, source As String, path As String
Dim dbsource As String, i As Integer, j As Integer

Set db = dbengine.Workspaces(0).Databases(0)
'*************************************************************
'*                     RECOGNIZE THE PATH                    *
'*************************************************************

For i = Len(db.name) To 1 Step -1
    If Mid(db.name, i, 1) = Chr(92) Then
        path = Mid(db.name, 1, i)
        'MsgBox (path)
        Exit For
    End If
Next
'*************************************************************
'*              CHANGE THE PATH   AND   CONNECT  AGAIN       *
'*************************************************************

For i = 0 To db.tabledefs.count - 1
    If db.tabledefs(i).connect <> " " Then
        source = Mid(db.tabledefs(i).connect, 11)
        'Debug.Print source
        For j = Len(source) To 1 Step -1
            If Mid(source, j, 1) = Chr(92) Then
               dbsource = Mid(source, j + 1, Len(source))
               source = Mid(source, 1, j)
                   If source <> path Then
                        db.tabledefs(i).connect = ";Database=" + path + dbsource
                        db.tabledefs(i).RefreshLink
                        'Debug.Print ";Database=" + path + dbsource
                    End If
                Exit For
            End If
         Next
    End If
Next
End Function

Provided by Peter Vukovic, September 1995.