Multiple Databases

[Poseidon]

Hello,

Is it possible, or a good practice, to have multiple Access databases, each
with maybe one or two tables? I would be using Access 2000 and feel that
tables like 'Transactions' and 'Financial Companies' would grow too big for
Access's 2 GB maximum for just one DB to handle considering the amount of
data that it would store for each entity. Therefore, I would like to keep
the large tables like these separate and spread the size over multiple DBs so
they can grow without me having to worry about them exceeding their size
limit in the near future .

Also:
How would system performance react by linking the DBs if they were on a
network?
Should I be thinking about going with a more robust DBMS like SQL Server
instead?

 

[Allen Browne]

If you are planning a project where some tables will contain millions of
records and you foresee many gigabytes of text-based data, Access is
probably the wrong storage database.

If you already have a database up and running in Access, and even when
compacted it is more than 1gb, then splitting it into multiple mdb files is
a great solution. Other than verifying that each of the back end files are
available, you are not introducing further issues.

 

[Douglas J. Steele]

Sorry to quibble with Allen, but I thought it was probably appropriate to
point out that you will not be able to create relationships between tables
if they're in different databases. That means you will not be able to use
the database to enforce referential integrity.

 

[Craig Alexander Morrison]

Use DB2 UDB or SQL Server 2000. Under no circumstances should you fail to
use DRI and this is not available to you if you use different MDB files for
related tables.

 

[Craig Alexander Morrison]

It is not that great a solution - no DRI to protect the data.

 

[Allen Browne]

Yes, good point, Doug.

 

[ProfWdesk1]

You could have a database.for each large table and then make a link table to
use the data. Roy , (e-mail address removed)

 

[Craig Alexander Morrison]

....and say goodbye to data integrity and consistency!