Home

Search this site:
 

Horshack’s
very
own site

 

This is Bavaria, Germany!
You will find links to Perl,
bootdisks for system administration,
a lot of Linux,
Windows administration
and some personal belongings.
Refresh linked tables in a sister-database

Refresh linked tables in a sister-database

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
Impressum