更改链接表的位置编程 [英] Changing linked table location programatically

问题描述

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

我想整个目录复制到一个新的位置（用于测试），并有数据库一个​​静止链接到表在数据库中的两个，但联动仍然是原来的目录，而不是新的位置。

我想要做的两件事情之一：要么

1. 请在数据库2的链接的表中的方式的文件夹路径是相对的 - 的路径数据库中的两个是不难$ C $光盘。

   或

2. 您也可以在的Form_Load 常规（或自动执行宏），检查application.path和编程方式相应地调整联动。

解决方案

这可能是有用的有一个初创的形式，可以让你浏览后端你要和表的表应该链接。你可以遍历表的收集，但我认为一个列表稍有更安全。在此之后，一个小code是所有需要，这里是一个片段：

 '与数据库密码的连接字符串
= strConnect中的MS Access; PWD = PW; DATABASE =＆放大器; Me.txtNewDataDirectory

设置RS = CurrentDb.OpenRecordset（选择表名从LinkTables_
＆放大器; WHE​​RE TABLETYPE ='链接'）

做，而不是RS.EOF
    ''检查表已链接，如果是，更新连接
    ''否则，链接表。

    如果ISNULL（使用DLookup（[名]，MSysObjects，[名] ='＆放大器;！RS表名和放大器;）），然后
        设置TDF = db.CreateTableDef（RS！表名，dbAttachSavePWD，_
            RS！表名，strConnect中）
        db.TableDefs.Append TDF
    其他
        db.TableDefs（RS！表名）。将= strConnect中
    结束如果
    db.TableDefs（RS！表名）.RefreshLink
    rs.MoveNext
循环
 

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. 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.

   or

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