Linking tables in Access databases on web server

I have several Access databases on my web server that are queried using ColdFusion. I need to centralize some data in one location that will be used by multiple databases.

Linking to the tables in the central database from the others seems like a good way to handle this, but the problem is that when I go into Access and link the tables, Access stores the full path to the database file on my computer, which is not the same as the path to the file on the web server (e.g. C:\whatever.mdb vs. D:\path\whatever.mdb).

Access is such a pain in the ***. I can even see where it is storing the path in one of the hidden tables, but it won't let me edit the field. If there was either a way to get Access to store a relative path to the database with the table that I am linking to, or a way to fool it into storing the path of the file on the web server, then it would probably work.

Can anyone suggest a solution to this problem?

The easiest way is to "clone" the exact same folder structure on your local development machine. Then the linked tables will still work when uploaded to the web server.

Otherwise you probably need to run access on the server itself and use the linked table manager to change the links.

That's really not that easy though.

The technique that I am currently working with, per information I found on the web, is to use an AutoExec macro that executes a TranserDatabase command to link to the table in the other database. So far it seems to be working but I have to test it more thoroughly.

There is code on http://www.mvps.org/access to programatically relink tables. Generally if your app is an asp application on a hosted server, you won't be able to use any VBA, macros, modules, etc, since all that code is executed by the Access application itself.

Yes, you're right, it doesn't work. I don't have ASP, only Coldfusion. The code that you provided the link to is just insane, did you see how long it is, to do one simple thing? Why does Microsoft have to do everything the hard way?

What do you recommend? I just learned of the DOS command subst which can create a "virtual" drive, but I don't know if it will be reflected in Windows, and the drive, D:, that I would need to fake already exists as a real drive.

Thanks for your help.