This is Bavaria, Germany!
This is the the code to refresh the linke tables to a sister-database in MS Access. This is important if you want to remove your database to another path and links to tables in a data-database shall not get broken
It's a good idea to call this function from the AutoExec-Macro in the Access-database
Function refresh_links() As Variant
Dim db As Database
Dim rs As Recordset
Dim sSql As String
Dim sPfad As String
Dim file_search As Variant
Dim i As Long
Dim lfiles_found As Long
Dim ssisterdb_fn As String
Dim lDummy As Long
Dim tdf As TableDef
Dim tdf_change As TableDef
Dim tdefs As TableDefs
Dim sTabellenname As String
Dim llast_backslash As Long
' this database is proxy.mdb with forms, queries, ...
' I need to connect the linked tables to another database where
' the real tables are
ssisterdb_fn = "proxy_data.mdb"
sPfad = CurDir
sPfad = CurrentDb.Name
' search the directory of this database
' CurrentDB.Name includes full directory and filename
For i = Len(sPfad) To 1 Step -1
If (Mid$(sPfad, i, 1) = "\") Then
sPfad = Left$(CurrentDb.Name, i - 1)
Exit For
End If
Next i
' is the sister-datbase really there?
' Maybe someone deleted it
Set file_search = Application.FileSearch
With file_search
.LookIn = sPfad
.FileName = ssisterdb_fn
If .Execute() > 0 Then
' rem, ok sisterdb exists
' renew path of all linked tables to the current path
' search names of alle linked tables
Set db = CurrentDb
Debug.Print "tablename;connect"
For Each tdf In db.TableDefs
Debug.Print tdf.Name & "===" & tdf.Connect
If (Left$(tdf.Connect, Len(";DATABASE=")) = ";DATABASE=") Then
Debug.Print "linked table is: "; tdf.Name
Set tdf_change = db.TableDefs(tdf.Name)
tdf_change.Connect = ";DATABASE=" & sPfad & "\" & ssisterdb_fn
tdf_change.RefreshLink
End If
Next
Else
lDummy = MsgBox("Sister database " & sPfad & "\" & ssisterdb_fn & _
" with real data could not be found!", vbCritical)
End If
End With
End Function