Question: Multiple Databases or Single

  • Thread starter Thread starter Dave Elliott
  • Start date Start date
D

Dave Elliott

Is it better to use a single large db or else link to another db?
Main database is getting a little large.
I have (2) More small db's that I use and was considering whether to
incorporate them or not?
 
In general, you want two database files:
- one back end (mdb), containing just the tables, in a shared location;
- one front end (mde), containing the queries, forms, reports, code, ...,
and installed to each workstation.

Unless your database is pushing 1GB in size, that's probably all you need.

If is is that large, you might want to remove any embedded objects, such as
any OLE Object type fields, or background graphics on forms/reports. These
are big bloaters.

Presumably you have already tried compacting to reduce the size:
Tools | Database Utilities | Compact.

If the database is still pushing 1GB, you might consider options such as:
- splitting some of the data off into another back end mdb, or
- periodic archiving, or
- using a larger database program.
 
Is it better to use a single large db or else link to another db?
Main database is getting a little large.
I have (2) More small db's that I use and was considering whether to
incorporate them or not?
Click to expand...

"A little large" for Access is somewhere between 1 and 2 billion bytes
(GByte); the limit is 2 GByte. If your database is 100 megabytes or
so, you're nowhere NEAR "large". How big is your database? How big is
it after you use Tools... Database Utilities... Compact and Repair?

It is prudent to split the database using the Database Splitter Wizard
to put all the tables in a single backend database, and the user
interface in a separate one; this is essential if it's a shared
multiuser database (to avoid bloating and corruption), and certainly
reasonable even for a single user database. Having tables spread
across multiple databases is possible but becomes far more difficult
to manage; for one thing you cannot maintain relational integrity
between tables in different databases. It's doable, but it should only
be done when you're forced to do so.

John W. Vinson[MVP]
Join the online Access Chats
Tuesday 11am EDT - Thursday 3:30pm EDT
http://community.compuserve.com/msdevapps
 
Back
Top