以编程方式更改链接表的位置 [英] 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.

我想将整个目录复制到一个新位置(以进行测试)，并让数据库1仍然链接到数据库2中的表，但是链接仍然是原始目录，而不是新位置.

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屋！