Relative table links in ms-access

Question

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

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

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.

Edit due to comments:

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.

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.

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.

Answer 1

You can use relative paths to link tables, but at the point of linking the relative path is evaluated and translated to an absolute path.

If you want to continue using relative paths, you should have an AutoExec macro that fires a procedure that links tables at run-time from your relative path.

This will, of course, increase your startup time.

Answer 2

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.

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?)

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?

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.

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.