Importing any file in a folder

[mchadwick]

I have a CSV I am putting in a folder on my hard drive. lets say "C:\folder". I would like Access when I run a macro to go out and get the file that is in that folder and import it into the a table in the DB. The file name is never going to be the same name, there are a great deal of files and I need the file name to stay what it is.

[June7]

Pointing to the folder is easy. How should Access know which file to import? Is it the only file in the folder?

It is possible to have code do a "For Each File in Folder" procedure. If there is only the one file then it will simply act on that one file.

[mchadwick]

There are going to be 236 files in there at one time and they all need to be imported. I would love to have something do it all at one time, but my delima is that the file name needs to be added to one of the columns on each record in that file to be imported. I need to know where the record came from. They all go into the same table though and all get processed the same way. They are all basically the same file, with different data.

[rpeare]

Here's what I would do:

1. use file systemobject commands to retreive file names from the folder in question (http://www.4guysfromrolla.com/webtec...ect/faq5.shtml)
2. update the 'permanent' table's default value for the 'source' file (make it the last column it will make things easier on you) using alter table or some other method (http://stackoverflow.com/questions/1...e-syntax-error)
3. Use your transfer method (docmd.transferspreadsheet) to append the data to the 'permanent' table.

[June7]

Think want to reverse steps 3 and 2 and use UPDATE sql to populate the 'source' field.

This assumes the spreadsheets have a simple structure that DoCmd.Transferspreadsheet can work with.

[rpeare]

assuming there is no default value to start the default value has to be changed on the import of the first file, and you are absolutely right, this assumes every file has exactly the same structure. If they don't you'll have to get more creative.