linked tables and database lookups

[lowtek]

Hello,
I have multiple Access databases that use many of the same data tables so I thought that it would be more efficient to delete the common tables in all the databases and create a single database to hold all of these common data tables and then just link to them in the other databases.

My question is (not really knowing about efficient database design), do I even need to link them in all of the other databases when I can just perform a database lookup? Which way is more efficient/faster?

thanks in advance for any help,
Rey

 

[AlexCuse]

I think I would go for linking them, although if you have multiple users writing to tables at the same time you could run into record-locking issues. How many users are there for these db's?

HTH,

Alex


It's a magical time of year in Philadelphia. Eagles training camp marks the end of another brutal season of complaining about the Phillies.

 

[Golom]

Your choices seem to boil down to the linked table option or explicit references to other databases built into your queries. Of the two I would pick linked tables because that gives you a single point of control (i.e. the Linked Table Manager.) If you go with the external references built into SQL then you can have a maintenance nightmare because there's no easy way to find and change all such references to a table's location.

I don't know if there's a performance penalty associated with these choices but, if there is, it's probably minor and I would see it as secondary to the maintenance issue.

 

[lowtek]

thank you Alex and Golom...I will stick with my original plan of just linking to the tables. I appreciate the feedback!
Rey