MS Access Linked Tables - Drive Name Changes Based on Location

0
votes

I have an Access database, split into two. One is the front end with all objects, linked to the other database - containing the tables. Both are in the same folder. Locally, the drive is G:. In our other office in another city, the drive is also G:. However, when a colleague in the other office opens the front end database, he gets an error message that it cannot connect locate the G:\ drive for the linked tables. I think there's a difference in the drive name. I know I can add VBA upon the database opening to change the link to that of the current folder. However, if two people in different offices are using the database at the same time, I think that will cause problems as each user would need a different link. Any suggestions will help! Thanks, Jeff

1
ms-accesslinked-tables
Are users sharing their front-end? That's a bad practice and can lead to trouble. - Erik A
Each user should run their own copy of the frontend installed on their local workstation. - June7

1 Answers

0
votes

You need to map the tables to the explicit folder path. Or the UNC address. It would start with \servername\folder...

I have done this several times, but, access will run very slowly across large distances and outside of a LAN. I would reccomend importing the table if it is used for reference, and create a copy within the front end. Of course, have the front end located on the users PC for fastest accessibility and response. If you are actively working with records in the table, use a query to add and edit records in the table instead of a bound form. Otherwise everything will slow down.