Import all files in folder

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

Guest

I have to periodically import data from roughly 100 different Access
databases that are sent to me by various people, and which I save into a
single folder. The databases are all in exactly the same format but have
different file names. I'd like to know how to automate the import of the
data from all files in the folder into my main database, appending the data
to existing tables. The catch is that the file names are not always the
same, so I can't just use a macro and 100 different Transfer Spreadsheet
commands. Can anyone tell me how to do this? (I'm assuming it requires VBA,
and my VBA experience is very limited.)

Thanks,
Craig
 
To really make it work properly, you will need some VBA. You can use the
Dir() function to accomplish this. You said the Access databases will have
different names. That is okay, the Dir() funtion will take care of that.
What you did not say is whether the table names you need to import are all
the same. If they are not, you have a more serious problem.

What catches my attention is you say you are importing from Access
databases, but then you mention TransferSpreadsheet. If you are importing
Access tables from Access databases, you should be using TransferDatabase.

You could put code something like this in the Click event of a command button:

Dim strNextMdb as String

strNextMdb = Dir("V:\AllThoseDatabases\*.mdb")
Do While strNextMdb <> ""
DoCmd.TransferDatabase, acImport, , strNextMdb, _
acTable, "SourceTableName", "DestinationTableName"
strNextMdb = Dir()
Loop
 
Thanks so much for the prompt reply.

All databases will be identical in layout, so this should work perfectly. I
send out a blank database for users to populate, and then I import their data
into my database. You are right about TransferDatabase, I have been
importing from Excel until now, so TransferSpreadsheet is stuck in my head.

For the line of code that reads acTable, "SourceTableName",
"DestinationTableName"
do I just repeat this line for every table that is to be appended? If so,
is there any command that I have to put between them?

Thanks,
Craig
 
If all the tables you will be importing in the 100 mdbs have the same name,
just substitue that for SourceTableName. If you are importing them all to
the same table in your main mdb, you can substitue that name for
DestinationTableName.

Now, I did forget one thing that is very important. It changes how I would
recommend doing this. The problem is what happens if a table with the same
name you are importing already exists in the current database. Let's say you
have a table named BooFuz and you want to import the table BooFuz from the
other database. What will happen, is it will come in as a separate table
named BooFuz1.
I don't think that is what you want. I think you want all the BooFuz tables
combined.

So, here is what I recommend. Instead of Importing, let's link to the table
in the other mdb, run an append query that will pull the data from it's
BooFuz and append it to our BooFuz. I have modified the code to include
this. You will just need to create an Append query to pull the data. For
example purposes, I will call it qappBooFuz.

Dim strNextMdb as String

strNextMdb = Dir("V:\AllThoseDatabases\*.mdb")
Do While strNextMdb <> ""
DoCmd.TransferDatabase, acLink, , strNextMdb, _
acTable, "BooFuz", "BooFuzTemp"
Currentdb.Execute("qappBooFuz"), dbFailOnError 'Appends the data
DoCmd.DeleteObject acTable, BooFuzTemp
strNextMdb = Dir()
Loop
 
The linking sounds good to me. Each of the 100 mdb files have 11 different
tables, so, for example purposes, if there was a table called BooFuz and
another called BooSmooth, would the code be as follows:

Dim strNextMdb as String

strNextMdb = Dir("V:\AllThoseDatabases\*.mdb")
Do While strNextMdb <> ""
DoCmd.TransferDatabase, acLink, , strNextMdb, _
acTable, "BooFuz", "BooFuzTemp"
acTable, "BooSmooth", "BooSmoothTemp"
Currentdb.Execute("qappBooFuz"), dbFailOnError 'Appends the data
Currentdb.Execute("qappBooSmooth"), dbFailOnError 'Appends the data
DoCmd.DeleteObject acTable, BooFuzTemp
DoCmd.DeleteObject acTable, BooSmoothTemp
strNextMdb = Dir()
Loop
 
NOt quite. Each transfer has to happen on it's own

DoCmd.TransferDatabase, acLink, , strNextMdb, _
acTable, "BooFuz", "BooFuzTemp"
DoCmd.TransferDatabase, acLink, , strNextMdb, _
acTable, "BooSmooth", "BooSmoothTemp"

Other than that, it looks good.
 
I am encountering an error when trying to run the code. When I debug, it
stops on the first DoCmd.TransferDatabase line. I have pasted the code
below. What am I doing wrong?

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