Combine Access FILES into one File and one Table

[TomTaylor]

I have many .MDB files (100+) with more to come, each containing one table with 20+ fields. The different files may have had additional fields added to the table, but the fields I am interested in are all in the first 20 fields which are in each table and file.

I need to combine all this data, (300,000 + records) into one Access Database and one Table. I don't want to use mutiple tables in one file because each file has basically the same layout and I will need to be adding to this table as more data is gathered.

Additional issue, the ID number field in each existing FILE is Unique, however when I combine Files, there will be duplicates, which I will want to keep with a timestamp.

HELP

 

[Norie]

Tom

I think you probably will need to have multiple tables in one file at some point.

You could then create append queries to combine them all in one table.

This could possibly be automated using VBA but more information would be needed to help you with that.

 

[TomTaylor]

If mutiple tables in one file is a transition step, that is ok. How to I get to that step without hand typing and hand importing each mdb file?

I can put them all in one directory.

Also would the tables have any identification as to where they came from?

Thank you in advance.