'
'Link All Tables From Back End Database
'
'The function LinkTables creates links to all the tables in the remote database.
'The DbPath parameter should contain the complete path and database name.
'The subroutine CallLinkTables has sample code to call the function.


Function LinkTables(DbPath As String) As Boolean
'This links to all the tables that reside in DbPath, whether or not they already reside in this database.
'This works when linking to an Access .mdb file, not to ODBC.
'This keeps the same table name on the front end as on the back end.

Dim rs As Recordset

    On Error Resume Next

'get tables in back end database
    Set rs = CurrentDb.OpenRecordset("SELECT Name " & _
                                    "FROM MSysObjects IN '" & DbPath & "' " & _
                                    "WHERE Type=1 AND Flags=0")
    If Err <> 0 Then Exit Function

'link the tables
    While Not rs.EOF
        If DbPath <> Nz(DLookup("Database", "MSysObjects", "Name='" & rs!Name & "' And Type=6")) Then
            'delete old link, assuming front and back end table have the same name
            DoCmd.DeleteObject acTable, rs!Name
            'make new link
            DoCmd.TransferDatabase acLink, "Microsoft Access", DbPath, acTable, rs!Name, rs!Name
        End If
        rs.MoveNext
    Wend
    rs.Close

    LinkTables = True
End Function


Sub CallLinkTables()
Dim Result As Boolean

'sample call:
Result = LinkTables("C:\Program Files\Microsoft Office\Office\Samples\Northwind.mdb")
Debug.Print Result
End Sub