MS Access-如何通过修改表来更改链接表的路径 [英] MS Access - How to change the linked table path by amend the table

问题描述

下面的查询允许我显示所有链接表及其对应的数据库路径

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;

输出

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

当我重命名了特定2个表的数据库时，我无法修改路径.有什么办法可以通过修改表来修改链接表的路径?

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?

推荐答案

是的，您可以通过VBA或GUI进行操作

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

通过GUI(Access 2010):

Through the GUI (Access 2010):

您可以使用以下VBA子项来更改某些表上的连接字符串(您需要提供旧的和新的连接字符串):

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

这篇关于MS Access-如何通过修改表来更改链接表的路径的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！