Combining multiple access tables into one

  • Thread starter Thread starter HKEC
  • Start date Start date
H

HKEC

I have the need to combine 5 access files with identical tables and
structures into one big one regularly. What is the best way to automate the
process? Are there any sample codes that I can follow? Or are there
inexpensive software that I can buy to do the job?
Thanks.
 
By '5 access files with identical tables', I assume you have 5 *.mdb files
each of which contain the same table names *AND* each table has identical
field names with each field name of the same table type?

If that's the case then you could export the data from each table in each
database and then import the data into the destination database. I would
probably add another two fields - one to indicate the export date/time and
one to indicate the import date/time as this will make things much easier if
you have to selecting export or delete an import.

However, if all five are running on the same network, I would suggest that
you look at Replication to copy the data over or even to go with a single
back end.

You may want to consider the bigger picture before you start exporting and
importing as going with replicas or a central back end both have their
advantages and disadvantages.
 
I have the need to combine 5 access files with identical tables and
structures into one big one regularly. What is the best way to automate the
process? Are there any sample codes that I can follow? Or are there
inexpensive software that I can buy to do the job?
Thanks.
Click to expand...

You own the software... it's called Access. <g>

You can use File... Get External Data... Link to connect from one Access
database to another; you can then run an Append query to append data from one
table into another table. This can be very easy or nightmarishly difficult,
though, depending on the nature of the data! Do you have one table in each
database, or many related tables? Or do the tables have independently
generated Autonumber primary keys (which will be duplicated in the different
instances), related to multiple child tables? Might there be duplicate - or
worse, *mostly* duplicate - records in different databases?

More info please!!
 
Thanks for the help. I have two related tables in each of those Access files.
My mission is to combine/append all those 5 acess files with identical two
related tables in each and make them into one big file. I don't even care if
there is duplicate for now. I just want to find an automated way to combine
the 5 into one big one. In another words, if there are 5 records in each
file, I want to have a file that has 25 records.
Thanks.
 
Thanks for the help. I have two related tables in each of those Access files.
My mission is to combine/append all those 5 acess files with identical two
related tables in each and make them into one big file. I don't even care if
there is duplicate for now. I just want to find an automated way to combine
the 5 into one big one. In another words, if there are 5 records in each
file, I want to have a file that has 25 records.
Thanks.
Click to expand...


Still a few questions! Do you have Autonumber fields in any of these tables?
If so you might have records numbered 1, 2, 3, 4, 5 in one database, 1, 2, 3,
5, 6 in a second, 1, 2, 3, 4, 5 in a third. Worse, these might be linked to
foreign keys in other tables - so that you can't tell (after merging) WHICH of
the five #3 records you mean!

If that's not an issue, you can choose one database as the target, and use
File... Get External Data... Link on the menu to link to the tables in another
database. Create Append queries based on the linked table, and append to the
local table. These could include a calculated field if you want to track which
database was the source of the data.

If you *DO* have duplicate autonumber primary keys, it gets harder. You may
want to check the range of autonumber values in each database, and assign
(say) 100000 number blocks for each database. Use an append query adding
100000 for database A, 200000 for database B etc. to the autonumber value *and
to each foreign key value in the other tables*.

This can be tricky - back everything up first of course!!
 
Back
Top