我有一个链接表中的第二个数据库,位于同一目录中的第一个Access数据库。
我想整个目录复制到一个新的位置(用于测试),并有数据库一个静止链接到表在数据库中的两个,但联动仍然是原来的目录,而不是新的位置。
我想要做的两件事情之一:要么
请在数据库2的链接的表中的方式的文件夹路径是相对的 - 的路径数据库中的两个是不难$ C $光盘。
或
您也可以在的Form_Load
常规(或自动执行宏),检查application.path和编程方式相应地调整联动。
这可能是有用的有一个初创的形式,可以让你浏览后端你要和表的表应该链接。你可以遍历表的收集,但我认为一个列表稍有更安全。在此之后,一个小code是所有需要,这里是一个片段:
'与数据库密码的连接字符串
= strConnect中的MS Access; PWD = PW; DATABASE =&放大器; Me.txtNewDataDirectory
设置RS = CurrentDb.OpenRecordset(选择表名从LinkTables_
&放大器; WHERE 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
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
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