Combine data from multiple Access files

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hiya,

Here is a description of my scenario. I have numerous Access DB files each
containing only one record (could be as many as 1200 files). I need to
combine the data so I end up with one DB file containing 1200 records,
without having to manually copy the records.

Is there some macro or visual basic code that might do it?
How about SQL?

I've scoured the Access help and knowledge base and read SQL in 10 minutes,
etc. But can't get past the "do it one by one" solution.

Any ideas?

(BTW, am working with Access 2003, 2000, and XP)
 
Are all the MDB files in a single folder (or can you at least easily
identify them)? Is the table in each named the same (and is its structure
the same)?

If all the files are in a single folder, it's easy to create a loop that
finds them and lets you work with them one-by-one.

Dim strFolder As String
Dim strFile As String

strFolder = "C:\Data\Databases\"
strFile = Dir$(strFolder & "*.mdb")
Do While Len(strFile) > 0

' At this point, strFolder & strFile will point to a specific database file.
' You could use the TransferDatabase method to like the table
' from that database into your main one.
' Run a query that adds the copied table to your final table
' (adding any additional information required to distinguish
' this data from the data in all the other databases)
' then delete the linked table you just created.

' This will change strFile to the next file in the folder (or to "" if
you've read them all)
strFile = Dir$()
Loop
 
Back
Top