The following code which automatically reconnects a database backend
by Peter Vukovic works well except for one minor glitch. On the first
opening of the frontend, a message box appears saying that the backend
cannot be found. If the frontend is closed and then reopened the
backend will connect OK. It will then remain connected for any
subsequent opening. The problem only appears on the first opening of
the frontend after the path is changed. Any help on why this is
happening would be appreciated.
Function Reconnect()
On Error Resume Next
'************************************************* *************
'* 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
'* 10*********@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