ms-access中的相对表链接 [英] Relative table links in ms-access

问题描述

我确定必须先问过这个问题，但是找不到.

I'm sure this must have been asked before, but I can't find it.

我可以在Access 2003中为链接表的位置设置相对路径吗?

Can I set a relative path for the location of a linked table in Access 2003?

另一个表也存储在mdb文件中.这些数据库有时会通过odbc或oledb连接到，因此依靠Access打开文件时触发的vba代码将无法工作.如果可能的话，我想使数据库的拆分"对正在连接的程序不可见，而不是每次都要检查是否链接了正确的表.

The other table is also stored in an mdb file. These databases will sometimes be connected to via odbc or oledb, so relying on vba code that fires when Access opens the file will not work. If possible I would like to keep the 'splitting' of the database invisible to the programs connecting, rather than them having to check each time that the correct tables are linked to.

根据评论进行

解释得更清楚；我关心的用例是一组相互链接的访问数据库，它们都位于同一目录中.

To explain more clearly; the use case I am concerned with is a set of interlinked access databases which all sit in the same directory.

如果将它们作为一个组进行复制，则所有链接仍将起作用，但是在更新之前，它们将指向错误的数据. 如果，我在Access中打开了mdb文件，则可以使用一个简单的vba宏进行检查，并在需要时更新链接.问题在于，在当前设置中，数据库通过其他程序(通过oledb/odbc)与的连接频率要比在Access中打开数据库的频率高得多.

If they are copied as a group, all of the links will still work, but until they are updated they will point at the wrong data. If I open the mdb file in Access, a simple vba macro can check and if needed update the links. The problem is that in the current set up the databases are connected to far more frequently by other programs (via oledb/odbc) than by opening them in Access.

问题是:我每次移动mdb文件时是否需要手动触发更新，或者是否可以通过某种方式A)使链接相对或B)在建立连接时触发检查?

The question is: do I need to manually trigger the update each time I move the mdb files, or is there some way to either A) make the links relative or B) trigger the check when connections are made?

由于此操作的目的是拆分现有数据库，因此除非有绝对必要，否则我不想修改连接程序以运行这些检查.

As the purpose of this is to split an existing database, I do not want to modify the connecting programs to run these checks unless it is definately necessary, hence this question.

推荐答案

这是一个非常有趣的问题！正如拉吉·摩尔(Raj Moore)所建议的，当链接表时，路径变为绝对路径，因此选项A(使链接相对)将不起作用.

This is a very interesting question! As Raj Moore suggested, when the tables are linked the path becomes absolute, so option A (Making the links relative) won't work.

关于选项B，您将不得不自行拦截连接请求并运行必要的代码以重新链接表-Access中没有内置的工具可以使您立即使用该功能.即使您确实有时间将代码拼凑在一起以执行此操作，但我认为您会发现，实际上，一旦开始研究其工作方式的语义，它就不太可行(例如，您是否在每个请求?当第一个客户端正在运行查询时另一个客户端连接到数据库时会发生什么?如果单个客户端打开到同一个mdb的多个连接会发生什么?)

Regarding option B, you would have to intercept the connection request on you own and run the necessary code to relink the tables - there isn't a facility built into Access that would make this work for you out of the box. Even if you did have time to cobble code together to do this I think you'd find that in practice it just isn't very feasible once you start looking at the semantics of how it would work (For example, do you relink on every request? What happens when another client connects to the database while the first client is running a query? What happens if a single client opens multiple connections to the same mdb?)

正如您所暗示的，您可以更改客户端应用程序以在启动时重新链接表，这可以工作，但是您仍然可能需要考虑多个客户端尝试连接到同一个后端mdb文件的情况( s).即使不应发生，也很可能会发生.要考虑的另一点是可维护性.跟随您的其他人是否会知道客户端应用程序在启动时执行此重新链接的事实，并且知道在新用户到来时不要简单地将连接字符串信息从一台客户端PC复制到另一台客户端PC吗?

As you alluded to, you could change your client apps to relink the tables upon startup, which would work, but you still probably need to think about the situation where more than one client attempts to connect to the same backend mdb file(s). Even if this shouldn't happen, it very well could. The other point to consider is maintainability. Will someone else who follows you be aware of the fact that the client applications are performing this relinking upon startup and know not to simply copy the connection string info from one client pc to another when a new user arrives?

我敢肯定有一个很好的理由将您的mdb分成两个文件，但是鉴于我们正在处理的少量信息，作为这个问题的一部分，我想知道将它们拆分是否会提供足够的信息.胜过可能带来的潜在问题.

I'm sure there is an excellent reason why you are splitting the mdb into two files, but given the small bit of information we're working with as part of this question I wonder if splitting them is going to provide enough of a gain to outweigh the potential issues this could present down the line.

如果必须拆分数据库，并在网络共享上维护这些生成的mdb文件的单独副本，我认为最安全的选择是编写一个VBA过程以使用相对路径.将文件复制到新目录时，只需执行一次该过程即可重新链接表.

If you must split the database, and maintain separate copies of these resulting mdb files on network shares, I think the safest alternative is to write a VBA procedure to relink the tables in the database using the relative path. When you copy the files to a new directory you'll just have to execute the procedure once to relink the tables.

这篇关于ms-access中的相对表链接的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！