以编程方式更改链接表位置 [英] Changing linked table location programmatically

问题描述

我有一个 Access 数据库，在第二个数据库中有一个链接表，位于与第一个相同的目录中.

I have an Access database with a linked table in a second database, located in the same directory as the first.

我想将整个目录复制到一个新位置(用于测试)并使数据库一仍然链接到数据库二中的表，但链接仍然是原始目录，而不是新位置.

I would like to copy the whole directory to a new location (for testing) and have database one still link to the table in database two, but the linkage is still to the original directory, not the new location.

我想做两件事之一:要么

I'd like to do one of two things: either

1. 以文件夹路径是相对的方式链接到数据库 2 中的表 - 数据库 2 的路径不是硬编码的.

1. Make the link to the table in database two in such a way that the folder path is relative - that the path to database two isn't hardcoded.

或

在 Form_Load(或 autoexec 宏)中有一个例程，用于检查 application.path 并以编程方式相应地调整链接.

Have a routine in Form_Load (or an autoexec macro) that checks the application.path and programmatically adjusts the linkage accordingly.

推荐答案

拥有一个启动表单会很有用，它允许您浏览所需的后端以及应链接的表的表格.您可以遍历表集合，但我认为列表稍微安全一些.之后，只需要一点代码，这里是一个片段:

It can be useful to have a start-up form that allows you to browse for the back-end you want and a table of the tables that should be linked. You could iterate through the tables collection, but i think a list is slightly safer. After that, a little code is all that is needed, here is a snippet:

''Connection string with database password 
strConnect = "MS Access;PWD=pw;DATABASE=" & Me.txtNewDataDirectory

Set rs = CurrentDb.OpenRecordset("Select TableName From LinkTables " _
& "WHERE TableType = 'LINK'")

Do While Not rs.EOF
    ''Check if the table is already linked, if it is, update the connection
    ''otherwise, link the table. 

    If IsNull(DLookup("[Name]", "MSysObjects", "[Name]='" & rs!TableName & "'")) Then
        Set tdf = db.CreateTableDef(rs!TableName, dbAttachSavePWD, _
            rs!TableName, strConnect)
        db.TableDefs.Append tdf
    Else
        db.TableDefs(rs!TableName).Connect = strConnect
    End If
    db.TableDefs(rs!TableName).RefreshLink
    rs.MoveNext
Loop

这篇关于以编程方式更改链接表位置的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！