How do I combine databases in Access 2000?

[Guest]

I have our office files set up in Access and we create a new database each
year entitled All Files 200__. I need to combine our databases from All
Files 2003 through All Files 2005 together into one database entitled All
Files 2003-2005.
Thanks for any help.

Beth81159

 

[Guest]

Well Beth this can be either painful or VERY painful. First of all give a
dope slap up the side of the head to who ever thought about creating a new
database each year. Self-applied dope slaps are OK. I should know. ;-)

Are these well set up database with relationships? If just one big table or
two, it would be easier (for this exercise). Do the tables have a date field
or year field? If not you are going to need to add a date or year field in
each table and populate them with the proper year information. If the tables
have relationships with other tables, only the main table probably needs a
date field.

The next problem is primary keys. If you used autonumbers, you have a
problem. When importing the data into a new table, you'll probably have to
use a combination of the existing primary key field plus the date field as
your new primary key.

If you use something else as the primary keys, life would be good if they
are unique across all the years. That way you can import all of the like
tables to a table.

Oh by chance have there been any changes over the years to the tables?
Fields added? Sizes changed? If so, I'm sorry to hear that. It could increase
the difficulty to migrane headache-level pain with a
bowling-ball-dropped-on-foot kicker.

The more I think about it, the more difficult the subject becomes to
describe. In a nutshell, you are going to have to play with it until you get
it right. You might even want to hire someone. Also make sure that the new
database is set up to use year after year.

BTW: Our company once had a person who created a new database each month for
20 similar entities! That was 240 mdb files per year! When asked to create
reports comparing and contrasting these entities over the last years, she
just made up data that kept the bosses happy. She never even got caught;
rather we found out after she retired. Might work for you. ;-)

 

[John Vinson]

I have our office files set up in Access and we create a new database each
year entitled All Files 200__. I need to combine our databases from All
Files 2003 through All Files 2005 together into one database entitled All
Files 2003-2005.
Thanks for any help.

Beth81159

This can be almost trivially easy, or it can be an absolute nightmare,
depending on the structure of the tables. The nightmare scenario comes
when you've used Autonumber primary keys, linked to Long Integers in
related tables - and the primary key 123 exists in all three databases
referring to different information.

There are VERY good reasons why most developers consider creating a
new database every year to be a Very Bad Idea. You're now encountering
the main one - you will someday want to compare the data across years,
and need to combine them. Better would have been not to split them in
the first place; you can use *one single database*, with a date field,
and use Queries to select only the data for an individual year.

If you've been living virtuously and are very, very lucky, you may be
able to simply make a copy of All Files 2003.mdb, open it, use File...
Get External Data... Link to link to the tables in All Files 2004.mdb,
and run one or more Append queries to merge the data from the linked
tables into the local tables. As noted, this WILL FAIL if you have
primary keys duplicated between the databases; there are getarounds
but they depend on the structure of the database.


John W. Vinson[MVP]

 

[John Vinson]

The more I think about it, the more difficult the subject becomes to
describe. In a nutshell, you are going to have to play with it until you get
it right. You might even want to hire someone. Also make sure that the new
database is set up to use year after year.

Comrade! Brother! Fellow-sufferer!

<g>

"There is no problem, however complex, which when looked at in the
proper way does not become even MORE complex."

John W. Vinson[MVP]

 

[Guest]

If you've been living virtuously and are very, very lucky, you may be
able to simply make a copy ......

Now I know why things often go badly wrong for me. :-(