how to import multiple files from the same directory into Access?

[Guest]

I have a bunch of .txt and .raw files that reside in the same directory, and
i'd like to import them into Access at one go and place them under the same
table. If i do it by File-> Get External Data -> Import.. , i'd have to do it
1 file by 1 file. How can I import all the files at one go?

 

[a a via AccessMonster.com]

Assuming that the files are of the same layout, ie. comma delim.

1. put all the like files in a single folder
2. start the import wizard and work all the steps, save the import spec
under the advanced button, remember the name you give it. we'll assume you
are appending data all to the same table name.
3. from VBA code set up a loop using the DIR$() function
4. start the loop doing a DIR$(*.txt)
5. then while DIR$() <> "" perform an import
6. use the DoCmd.TransferText(TransferType, SpecificationName, TableName,
FileName, HasFieldNames, HTMLTableName, CodePage) function
7. read the Help on this TransferText function, use the import spec name
you saved before.
8. Execute the code with your loop.