Results 1 to 2 of 2
  1. #1
    Join Date
    Feb 2012

    Unanswered: Relinking multiple linked tables that are linked to different databases


    I have a database with a number of linked tables that are linked to tables in different databases (not a back-end)

    for example,
    I have table1 that is linked to table1 in K:\database\db1.mdb.
    table2 linked to table2 in S:\data\data.mdb.
    and so on...

    However, recently we have moved all our databases to a new location.
    K:\database\db1.mdb is now residing in O:\master\database
    and S:\data\data.mdb is now residing in O:\master\data
    and so on...

    I'm now in charge of relinking all those tables to point to the new location.
    I would do this in linked table manager one by one but we have 100s of tables linked to multiple different databases in different location.

    My question is,

    is there a way to create a VBA code that will automatically do this re-linking process?

    1. find unlinkable tables
    2. search its new location under O:\master
    3. re-link it to the new location

    database names and tables names have not been changed. Just the location of databases.

    Thank you in advance,

  2. #2
    Join Date
    Mar 2009
    Provided Answers: 14
    Here's a solution:
    Sub RelinkTables(ByVal SearchPath As String)
    ' Note: A reference to the Microsoft Office Object Library is needed.
    ' ----  For Access 2003: Menu "Tools" --> "References" --> "Microsoft Office 12.0 Object Library"
        Dim dbs As DAO.Database
        Dim tdf As DAO.TableDef
        Dim strDBName As String
        Set dbs = CurrentDb
        For Each tdf In dbs.TableDefs
            If tdf.Connect <> "" Then
                Debug.Print tdf.Name, tdf.Connect
                strDBName = Mid(tdf.Connect, InStrRev(tdf.Connect, "\") + 1)
                Debug.Print strDBName
                With Application.FileSearch
                    .SearchSubFolders = True
                    .MatchTextExactly = True
                    .FileType = msoFileTypeAllFiles
                    .FileName = strDBName
                    .LookIn = SearchPath
                    If .FoundFiles.Count = 0 Then
                        MsgBox "File: " & strDBName & "was not found.", vbInformation, "RelinkTables"
                    ElseIf .FoundFiles.Count = 1 Then
                        tdf.Connect = ";DATABASE=" & .FoundFiles(1)
                        MsgBox "Several files named: " & strDBName & " were found.", vbInformation, "RelinkTables"
                    End If
                End With
            End If
        Next tdf
        Set dbs = Nothing
    End Sub
    You can call the procedure like this:
    RelinkTables "O:\master"
    Warning: Make a copy of the original database before using this procedure, just in case something goes wrong.
    Have a nice day!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts