Access 97 database merge

[Guest_imported]

I need help working with an Access 97 Database - or rather several databases. When they compiled the data in the database they used several "Stand Alone" machines so that our data is in 5 different files - with the database structure being the same. What is the best way to combine into one file without typing everything back into one database file - is there a program that will import the data into the current tables? I tried the merge in access and it just created new tables adding a 1 to the end of the name. Any help would be appreciated.

 

[petermeachem]

Is some of the data duplicated across the five? Peter Meachem
peter@accuflight.com

Support Joanna's Bikeathon

http://www.accuflight.com/blue_peter_bikeathon_2002.htm

 

[dbaseboy]

If the files are all the same structure then I personally think the easiest thing to do would be to save each table as an excel spreadsheet (using Save As)and then ImportSpreadsheet into the required table.

Someone with more experience may have an easier way but I find this is fairly straightforward.

Peter

 

[grnzbra]

Assuming the structure is the same, import them as separate tables. Copy one of the tables structure only and add an auto number field (assuming that each table has an auto number for the key field, you might find you have duplicate keys between the different tables.) Create a series of append queries to append the data into the table you just created. If you know that you won't end up with duplicate keys, create one make table query instead of copying the table and append queries for the rest of the input tables.

 

[grnzbra]

I don't like going through Excel because it seems to like to mess with the format of the data - especially numbers with leading zeros.

 

[dbaseboy]

Good point!, I agree your way's better!

 

[grnzbra]

Actually, if they're all Access 97, the easiest way would be to create a link to the tables in each of the other databases and then create a series of append queries to add the data into the first database's table. The only tricky part of this would be the possibility of duplicate keys.

 

[shelron]

If you set the data type to text in Excel it usually will import into Access clean, dates are recognized, as are leading zeros.

Ron