Building a database with multiple linked tables from other databas

[mark909]

Is this a good idea?

Basically ive been asked to build a database in Access 2007 using linked
tables from several other databases.

Im not sure if this is a good idea regarding the proper structuring of
relational databases, data integrety, etc

Any ideas?

 

[Gina Whipp]

Mark,

My reply would be depends...

I have done such databases BECAUSE the Clients are in one database and since
I don't want ten databases with Clients in it I link all databases that
require Clients to the database that contains the master Client list. You
don't want Client information in ten databases... SOMEBODY has to remember
to update all the tables... good luck with that!

In this particular case it is a good idea... Is your situation similar?
Note, proper structure is part, what is the best way to store the data'and
part, what works for the situation. Try this thought... Is it a
stand-alone house OR and add-on to an existing house?

IMHO
Gina Whipp

"I feel I have been denied critical, need to know, information!" - Tremors
II

http://www.regina-whipp.com/index_files/TipList.htm

 

[Armen Stein]

On Tue, 23 Jun 2009 09:36:42 -0400, "Gina Whipp"

Since referential integrity isn't supported between databases, having
separate back-ends only makes sense when you are retrieving data from
a completely different stand-alone system. And you also need to
handle the situation when some of the databases are not available. Can
your application still run?

Is it a
stand-alone house OR and add-on to an existing house?

Gina has a good point. I would not normally recommend a new system be
built with multiple databases. Usually you would want one larger
database that holds all the data, so that you can define the
relationships properly. If you're concerned about security or
performance, those issues can be handled other ways, like by moving to
SQL Server if necessary.

If you do end up needing multiple Access databases, you're welcome to
use our free J Street Access Relinker at:
http://www.jstreettech.com/downloads

It handles multiple Access back-end databases, ignores ODBC linked
tables, and can automatically and silently relink to back-end
databases in the same folder as the application (handy for work
databases or single-user scenarios). There's a ReadMe table with
instructions.

Armen Stein
Microsoft Access MVP
www.JStreetTech.com