MS Access - How to change the linked table path by

问题:

Below query allow me to show all linked table and its corresponding database path

SELECT DISTINCTROW msysobjects.Name, msysobjects.Database, msysobjects.Connect
FROM msysobjects WHERE (((msysobjects.Type)=6 Or (msysobjects.Type) Like "dsn*")) ORDER BY msysobjects.Database;

Output

Name                    Database                             Connect
Account Transactions    C:\Users\Desktop\Database6_be.accdb 
Categories              C:\Users\Desktop\Database6_be.accdb 
Filters                 C:\Users\Desktop\Database6_be.accdb 
tblAuditLog             C:\Users\Desktop\Database6_be.accdb

As i renamed the database for particular 2 tables, while i unable to amend the path. Is there any way i can amend the linked table path by amend the table?

回答1:

Yes, you can either do it through VBA, or through the GUI

Through the GUI (Access 2010):

You can use the following VBA sub to change the connection strings on certain tables (you need to provide both the old and new connection string):

Public Sub ChangeConnection(OldStr As String, NewStr As String)
    Dim td As DAO.TableDef
    Dim db As DAO.Database
    Set db = CurrentDb()
    For Each td In db.TableDefs
        With td
            If .Connect = OldStr Then
                .Connect = NewStr
                .RefreshLink
            End If
        End With
    Next td
End Sub

Or, you can use the following sub to change one specific table

Public Sub ChangeTableConnection(Tablename As String, NewStr As String)
    Dim td As DAO.TableDef
    Dim db As DAO.Database
    Set db = CurrentDb()
    Set td = db.TableDefs(Tablename)
    td.Connect = NewStr
    td.RefreshLink
End Sub