Okay, great. You can use the Dir function to set up the loop to do the
imports. First, I would suggest you set up an Import Specification for the
text files. You do that by manually preforming an import. When you get the
import dialog, click on Advanced. There you can define field names, data
types, etc. Once you have everything configured the way you want it, click
on Save As and give it a name. This is the name you will use in your
TransferText method. In addition to your production table, you will need an
intermediate table. This table will recieve data from the text file, then be
used to transfer the data to the production table.
Here is the seguence of events:
1. Delete the data in the production table
2. Delete the data in the intermediate table
3. Import the text file to an intermediate table
4. Run an Append query to append the data to the production table
5. Repeat 2 - 4 for all text files in the folder.
The code will look something like this:
Dim strNextFile as String
Dim strFilePath as String
Dim dbf as Database
dbf = CurrentDb
strPath = "F:\WhereMyFilesAre\"
strNextFile = Dir(strPath & "*.txt") ' If extension is not txt, change
this to match
dbf.Execute("DELETE * FROM MyProductionTable;"), dbFailOnError
Do While strNextFile <> ""
dbf.Execute("DELETE * FROM MyIntermediateTable;"), dbFailOnError
DoCmd.TransferText acImportDelim, ImportSpecName,
"MyIntermediateTable", strPath & strNextFileName, True
dbf.Execute("qappAddToProduction"), dbFailOnError
strNextFile = Dir()
Loop
