How can I consolidate three Access databases into one?

[AnnZ]

We used to have one database which housed over 2,000 records. Someone (I
don't know whether he was an Access expert or not) came in and decided the
database was too big and split it into four database. Now I can never modify
records at one time because the information to be modified is found in the
different databases. Any suggestions on how I can consolidate these into one
database?

 

[Wolfgang Kais]

Hello "AnnZ".

We used to have one database which housed over 2,000 records.
Someone (I don't know whether he was an Access expert or not)
came in and decided the database was too big and split it into four
database. Now I can never modify records at one time because the
information to be modified is found in the different databases.
Any suggestions on how I can consolidate these into one database?

Create a new database and link the tables from the other databases
(menu "file, external data"). Tthe data willl still be stored in the other
databases but you can modify the data from one central database.

 

[Jeff Boyce]

You don't mention if the structure of the four databases is identical, or if
different data is being stored in different databases (i.e., Names in one,
Addresses in a second, Phone Numbers in a third, etc.).

One way to pull everything into a single .mdb file is to open a single .mdb
file and import the tables from all the others.

Consolidating the data (if the databases are identical in structure) is a
more difficult topic, and depends on whether you are eliminating duplicates,
and/or need to preserve WHICH database the data came from, and/or ...

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[aaron.kempf]

a) keep all your data in an Access Data Project instead of MDB
b) you don't have to deal with any of this crap.
c) you dont have a 2gb limit
d) with the free sql server 2005 express; you have a 4gb limit
e) with a moderately-priced SQL Server Workgroup Edition you don't have
ANY limit on database size. Hundreds, thousands of gigabytes.
(realistically though i would reccomend using Standard Edition and not
workgroup; so that you could use Analysis Services-- by far the best
component in SQL Server land)

-Aaron