In need of an Access expert!

Chedmeister · 29 Jan 2012

Hi chaps, I have a customer who has asked me to solve a problem with his Access database. The employee who authored it is long gone and the 'redesign' of his company relies on this issue being solved.

He has this moderately complicated database that he wants to keep on a server and have network computers connect to it using a mapped drive. This would be fine if the DB was a single file but instead its one DB with many other linked DB files (linked tables) attached to it. I'm sure I don't need to tell you that this causes the remote computer to look for the linked tables on ITS OWN hdd and not the servers.

So is there a way to work it so he can do what he wants or do I have to find a way of incorporating all those linked tables into the main database file?

Hope someone can help!

Lemur · 30 Jan 2012

There are reasons why you would want to link to other external tables, such as SQL, etc. But if all these external tables are simply other MS Access database files, I don't see why you couldn't combine them into one file. It makes sense to have a database on a server, so it will be backed up and accessible to more than one user. I don't see why the links are looking to an individuals hard drive, rather than the mapped drive. You need to examine the code and make sure it accounts for the mapping, and the mapping is consistent. I'm assuming this worked at one time? If so, has your mapping changed? Did you make any changes that affected the dB?

Chedmeister · 30 Jan 2012

Ok, no biggy. I just put the main DB and the collection of linked tables in their own folders and shared them on the network. Then I replaced all the filepath's within the' linked table manager' with the new network path. it didn't work before because all the files were on one local drive before the links were all local too.
Simple now I know!

I do have one other issue though. Because the database will now be available to many people instead of one is there a way that I can lock access to it if another user already has it open?

Lemur · 30 Jan 2012

Yes, but you have to do that from within Access rather than the operating system.

Keep in mind you want to lock the particular record and not the whole database.

Chedmeister · 31 Jan 2012

Ok, thanks loads for your help!