Reconnect Attached tables on Start-up (1 Viewer)

[BrettM]

If you have split your Access application into a FrontEnd and a BackEnd and then decide to move the application to different folder, all your links will fail. This code will reconnect all the links automatically. It also handles passwords correctly.

Note that it is assumed that the FrontEnd and ALL BackEnds are in the SAME folder.

The DEBUG.PRINT lines are there simply to show what is happening. If you wish to use them, remove the comments and run the code from within your VB editor. Also - remember to comment the "db.TableDefs(i).RefreshLink" line so no real changes are made.

Create an Autoexec Macro to call this Function at startup.

Remove the "If Source <> path Then" line and its associated "End If" to have the reconnection happen every time.

Function Reconnect()
'*************************************************************
'*      Use an Autoexec Macro to call this function.         *
'*      It will reconnect all links when the FrontEnd        *
'*      and the BackEnd are in the same directory.           *
'*      It also keeps any password settings intact.          *
'*                                                           *
'*      Uncomment the Debug.Print lines and comment out      *
'*      the "db.TableDefs(i).RefreshLink" line to see        *
'*      structure by running code directly.                  *
'* ***********************************************************
Dim db As DAO.Database, Source As String, pathNew As String, pathOld As String
Dim SourceName As String, i As Integer, j As Integer
Set db = CurrentDb
pathNew = CurrentProject.path & "\"
'Debug.Print "New Path = " & pathNew
For i = 0 To db.TableDefs.Count - 1
    If db.TableDefs(i).Connect <> "" Then
        Source = db.TableDefs(i).Connect
        SourceName = right(Source, Len(Source) - (InStr(1, Source, "DATABASE=") + 8))
'        Debug.Print "Connect = " & Source
 
        For j = Len(SourceName) To 1 Step -1
            If Mid(SourceName, j, 1) = Chr(92) Then
                pathOld = Mid(SourceName, 1, j)
                SourceName = Mid(SourceName, j + 1, Len(SourceName))
'                Debug.Print "SourceName = " & SourceName
'                Debug.Print "Old Path = " & pathOld
               Exit For
            End If
        Next
        Source = left(Source, Len(Source) - (InStr(1, Source, "DATABASE=") - 10))
'        Debug.Print "Connect Start = " & Source
'        Debug.Print "Old Connect for " & db.TableDefs(i).Name & " = " & db.TableDefs(i).Connect
        If Source <> path Then
            db.TableDefs(i).Connect = Source + pathNew + SourceName
            db.TableDefs(i).RefreshLink
        End If
'        Debug.Print "new Connect for " & db.TableDefs(i).Name & " = " & db.TableDefs(i).Connect
    End If
Next
End Function