Import Muliple Access Tables from Multiple Access DBs

[Guest]

Hi, I found some code for importing multiple text files and am trying to
modify it to deal with Access DB files instead. Can anyone help please? I
have approx 150 DBs I want to import 4 Tables from each and append to four
tables in one master database.

Private Sub btnImportAllFiles_Click()
'procedure to import all files in a directory and delete
'them. 'assumes they are all the correct format for an ASCII
'delimited import.

Dim strfile As String

ChDir ("c:\myAudits")
strfile = Dir("FileName*.*")
Do While Len(strfile) > 0
DoCmd.TransferDatabase acImport, 8, "c:\myaudits\" & strfile, True
'delete the file
Kill "c:\myaudits" & strfile
strfile = Dir
Loop

End Sub

 

[John Nurick]

Hi Sue,

To append data from an external source into existing tables you need to
use append queries rather than TransferDatabase. Something like this air
code should do it:

Dim strFileName As String
Dim strFolder As String
Dim strSQL As String
Dim arTableNames As Variant
Dim dbD as DAO.Database
Dim j As Long

Set dbD = DBEngine(0)(0)
strFolder = "D:\Folder\"
arTableNames = Split("Table1 Table2 Table3 Table4")

strFileName = Dir(strFolder & ".mdb")

Do While Len(strFileName) > 0 'Outer loop, once per file

For j = 0 to UBound(arTableNames) 'Inner loop, once per table
'Construct and execute append query
strSQL = "INSERT INTO " & arTableNames(j) _
& " SELECT * FROM " & arTableNames(j) _
& " IN """ & strFolder & strFileName & """;"
dbD.Execute strSQL, dbFailOnError
Next

'Rename file so it's not processed again
Name strFolder & strFileName _
As strFolder & strFileName & ".done"

strFileName = Dir() 'get next filename
Loop

Set dbD = Nothing

 

[Guest]

I ended up doing it by using transferdatabase to get all the tables in
numbered 1-4, then ran insert into code into the permanent tables. Seemed to
all work fine.
Thanks for your reply!
Sue