Linking tables in separate databases on web server

[JMM]

I have several Access databases on my web server, supporting ColdFusion applications. Each separate database represents a different application. I need to centralize some data that will be used by multiple applications. I plan to create a new database to store the centralized data.

As I see it, my options are to:

A) Keep the databases separate and "link" them together with my ColdFusion code

or

B) Link the tables in Access

Option B seems much more attractive to me as it would greatly simplify my queries and would be a more concrete link between databases. However, Access seems hellbent on preventing me from doing so.

If I link the tables in the Access client on my local machine, Access stores the full path to the database with the linked table. The path on the web server is different. As far as I can tell, Access doesn't provide any way to create the link with a relative path or manually change the path after linking. I've seen various programmatic ways of relinking the tables discussed, but I find it hard to believe I would need to use code dozens of lines long to do one simple little thing, and I don't have access to ASP anyway.

I'm currently investigating the possibility of using the DOS subst command to create a virtual drive, so that I can imitate the directory structure on the web server when I link the tables in Access, but the virtual drive I would need to create already exists as a recovery partition on my computer, so I'm trying to find out if I can change that to another drive letter.

Any suggestions?

 

[FoFa]

Can't you use UNC paths?
\\servername\share\filename.mdb
for your linking?

 

[JMM]

No, unfortunately I can't. My local machine is not networked with the web server -- I am just connecting to that machine over the Internet, e.g. to FTP the Access files up. Anyway, Access never gives you the opportunity to just enter a path -- it's all interactive, you have to browse to the file with the table you are linking to. Any ideas?

 

[Pat Hartman]

Access does not support relative paths.

You can type the unc name into the FileName field.

 

[JMM]

Yes, as I indicated, I was unable to find any way to link the table with a relative path.

I could type the UNC path into the filename field if there was a UNC path to the file with the table I want to link to, but there isn't.