Ok -- this isn't as difficult as it may sound. I've listed below a process
and code that will import a series of files into a temporary table, append
those records into a permanent table, delete the records from the temporary
table, and then continue the process.
But you will need to set up an Import Specification (I assume that you have
already done this, as you've done the import process manually in the past).
Set up this specification and save it. If you don't know how to do this, you
start the manual import process, select a file, and then click the
'Advanced' button in lower left when the wizard window displays.
I recommend that you create a temporary table to which the records will
initially be imported. This table should have the correct fields based on
the file data being imported. Name the table "tblRecords" for now.
Then create a second table to which the "completed" records will be copied.
Name this table "tblFinal" for now.
Then put this code in a regular module (this code assumes that the files are
in the path C:\MyFolder\ and that all files end with ".txt" and that there
are no other files in that folder and that there are no header rows in any
of the files):
Public Sub GoGetMyRecords()
Dim strFileName As String, strSQL As String
Dim dbs As DAO.Database
Const strPath As String = "C:\MyFolder\"
On Error GoTo Err_Code
Set dbs = CurrentDb()
DoCmd.Set Warnings False
strSQL = "DELETE * FROM tblRecords;"
dbs.Execute strSQL, dbFailOnError
DoCmd.Set Warnings True
strFileName = Dir(strPath & "*.txt")
Do While strFileName <> ""
DoCmd.TransferText acImportDelim, , _
"tblRecords", strPath & strFileName, False
strSQL = "INSERT INTO tblFinal " & _
"SELECT tblRecords.* FROM tblRecords;"
DoCmd.Set Warnings False
dbs.Execute strSQL, dbFailOnError
strSQL = "DELETE * FROM tblRecords;"
dbs.Execute strSQL, dbFailOnError
DoCmd.Set Warnings True
strFileName = Dir()
Loop
ExitCode:
On Error Resume Next
dbs.Close
Set dbs = Nothing
DoCmd.Set Warnings True
Exit Sub
Err_Code:
MsgBox "Error occurred: (" & Err.Number & ") " & _
Err.Description
Resume Exit_Code
End Sub
Last, run this code.
