Importing all files in folder, Part 2

[Craig]

I am trying to set up code to import all files in a folder into an Access
2003 database. Each mdb file that I am importing from has the exact same
structure as the mdb file that I am importing to. There are about 120 files,
and I will have to periodically import batches of files as they are submitted.

Someone was previously kind enough to provide me with VBA code to do this,
but I am encountering an error when trying to run the code, and I am not VBA
savvy enough to troubleshoot the problem. When I select debug, it
stops on the first DoCmd.TransferDatabase line. I have pasted the code
below. Can somebody tell me what I am doing wrong?

Thanks,
Craig

Sub Batch_Import_Macro()
Dim strNextMdb As String

strNextMdb = Dir("P:\ATKINSON\ATKINSON 1 CSRRC SUPPORT\DDA\FINANCIAL
STATEMENT ANALYSIS\2006\TESTING\*.mdb")
Do While strNextMdb <> ""
DoCmd.TransferDatabase , acLink, , strNextMdb, _
acTable, "%_Exp_Travel-Staff", "%_Exp_Travel-StaffTemp"
DoCmd.TransferDatabase , acLink, , strNextMdb, _
acTable, "Attendance_Days", "Attendance_DaysTemp"
DoCmd.TransferDatabase , acLink, , strNextMdb, _
acTable, "Contact_Table", "Contact_TableTemp"
DoCmd.TransferDatabase , acLink, , strNextMdb, _
acTable, "CSLA_Hours", "CSLA_HoursTemp"
DoCmd.TransferDatabase , acLink, , strNextMdb, _
acTable, "Day_Site_Table", "Day_Site_TableTemp"
DoCmd.TransferDatabase , acLink, , strNextMdb, _
acTable, "Expenditure_Table", "Expenditure_TableTemp"
DoCmd.TransferDatabase , acLink, , strNextMdb, _
acTable, "Provider_Table", "Provider_TableTemp"
DoCmd.TransferDatabase , acLink, , strNextMdb, _
acTable, "Reconciliation_Table", "Reconciliation_TableTemp"
DoCmd.TransferDatabase , acLink, , strNextMdb, _
acTable, "Res_CSLA_Site_Table", "Res_CSLA_Site_TableTemp"
DoCmd.TransferDatabase , acLink, , strNextMdb, _
acTable, "Transportation_Table", "Transportation_TableTemp"
CurrentDb.Execute ("%_Exp_Travel-Staff"), dbFailOnError 'Appends the
data
CurrentDb.Execute ("Attendance_Days"), dbFailOnError 'Appends the data
CurrentDb.Execute ("Contact_Table"), dbFailOnError 'Appends the data
CurrentDb.Execute ("CSLA_Hours"), dbFailOnError 'Appends the data
CurrentDb.Execute ("Day_Site_Table"), dbFailOnError 'Appends the data
CurrentDb.Execute ("Expenditure_Table"), dbFailOnError 'Appends the
data
CurrentDb.Execute ("Provider_Table"), dbFailOnError 'Appends the data
CurrentDb.Execute ("Reconciliation_Table"), dbFailOnError 'Appends
the data
CurrentDb.Execute ("Res_CSLA_Site_Table"), dbFailOnError 'Appends
the data
CurrentDb.Execute ("Transportation_Table"), dbFailOnError 'Appends
the data
DoCmd.DeleteObject acTable, "%_Exp_Travel-Staff"
DoCmd.DeleteObject acTable, "Attendance_DaysTemp"
DoCmd.DeleteObject acTable, "Contact_TableTemp"
DoCmd.DeleteObject acTable, "CSLA_HoursTemp"
DoCmd.DeleteObject acTable, "Day_Site_TableTemp"
DoCmd.DeleteObject acTable, "Expenditure_TableTemp"
DoCmd.DeleteObject acTable, "Provider_TableTemp"
DoCmd.DeleteObject acTable, "Reconciliation_TableTemp"
DoCmd.DeleteObject acTable, "Res_CSLA_Site_TableTemp"
DoCmd.DeleteObject acTable, "Transportation_TableTemp"
strNextMdb = Dir()
Loop
End Sub

 

[Klatuu]

The Dir function does not return the full path, it only returns the file name.
The TransferDatabase method needs the pull path and name.

Here are the corrections that should cure the problem:

Sub Batch_Import_Macro()
Dim strNextMdb As String
Const conPath = "P:\ATKINSON\ATKINSON 1 CSRRC SUPPORT\DDA\FINANCIAL
STATEMENT ANALYSIS\2006\TESTING\" As String

strNextMdb = Dir(conPath & "*.mdb")

DoCmd.TransferDatabase , acLink, , conPath & strNextMdb, _
acTable, "%_Exp_Travel-Staff", "%_Exp_Travel-StaffTemp"

 

[Craig]

The As String command is giving me a syntax error.

 

[Craig]

OK, I think I figured out that I need an apostraphy in front of the As
String, but I then get an error at the Loop command that says Loop without
Do. Any thoughts?

 

[Klatuu]

take out the loop line.

oops, my syntax was incorect for the contstant. It should be:
Const conPath As String= "P:\ATKINSON\ATKINSON 1 CSRRC SUPPORT\DDA\FINANCIAL
STATEMENT ANALYSIS\2006\TESTING\"

 

[Craig]

I made that change and am now back to getting hung up at the first
DoCmd.TransferDatabase line. It highlights both lines of the command, and
the yellow arrow is pointed at the acTable line.