Append the same table from Multiple Databases

susanmgarrett

Registered User.
Local time
Yesterday, 23:24
Joined
Dec 7, 2004
Messages
41
I inherited an interesting problem. I have 3700 tiny MDB files all of which contain the same table (same name, field structure, etc.), but different data. Each database has a slightly different name. It's the result of XML data mining.

I need to combine these 3700 tables from these different databases into one table in one database.

I can't see any way to automate this and as it's a one-time only project I don't know whether it's worth trying to automate it or if I should roll up my sleeves and just start apending tables.

Does anyone have any suggestions?
 
I would not start appending. There are a couple of ways to go forward, depending on the file names/locations. Are they all in the same place, and is there a pattern to the names (file1, file2...file3700)? Presuming they're all in the same place, is there anything else in that folder?
 
All of the 3700 MDBs are in the same folder. The MDB to be created to hold the full file can be placed in the same folder as well.

The 3700 MDB files all have the same nomenclature (xxxx.mdb) where x is a number, but they are not sequential and skip numbers.

There is nothing else in the folder.

Thanks!
 
Not sure how experienced you are. You can use the file scripting object to spin through the files in the folder. There's some code here you can adapt to your needs:

http://www.access-programmers.co.uk/forums/showthread.php?t=126107

With that you can execute SQL that gets the records from the table in that file (using the IN clause) and append them into your main database.

That get you what you want?
 

Users who are viewing this thread

Back
Top Bottom