Microsoft Access Office VB VBA Help and Examples
Real wood furniture for any home at

Relink Tables in Code

Ever had to relink tables as you've moved a database. Want users to map whatever drive they like. Let users store a database where they want and then ask for the location and pass it as a string to this sub and it will re-link all your linked tables

This bit of code can be pasted straight into a module and used from there.

'RelinkTables...Just as the name suggests pass a path to a database to this sub
'eg RelinkTables("c:\windows\test.mdb")
'and it will go through all the tables in your
'database and link them to the new location
'Written by John Hawkins 20/9/99
Public Sub RelinkTables(NewPathname As String)
    Dim Dbs As Database
    Dim Tdf As TableDef
    Dim Tdfs As TableDefs
    Set Dbs = CurrentDb
    Set Tdfs = Dbs.TableDefs
'Loop through the tables collection
       For Each Tdf In Tdfs
        If Tdf.SourceTableName <> "" Then 'If the table source is other than a base table
            Tdf.Connect = ";DATABASE=" & NewPathname 'Set the new source
            Tdf.RefreshLink 'Refresh the link
        End If
    Next 'Goto next table
End Sub
The code and application content of this site is copyright of Smiley I.T. and as such reproduction in any form which is for commercial use requires the permission of the Webmaster. Any use of this code for non-commercial use only requires a link or comment back to the original page you took the code from.