Import Muliple Access Tables from Multiple Access DBs

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

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
 
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
 
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
 
Back
Top