Multi-file import

[rss0213]

I believe I've stumped users on another forum with this one...

I have a process that will create multiple files daily on our mainframe. These files will be semi-colon delimited and will be transmitted to my users' shared drive on a server. I will be appending a date/time stamp to each file to make them unique (e.g. file 'ABC_20091001_1200.txt'). The users have an Access DB that they want to import the data into and manage the data from there. So I would like to have a module that is executed each time the .mdb file is opened. I know I can run an event on the open of a form, but I'm not sure about the specific VBA code to use in the module. Basically, this is what the code needs to do:

1. import the data from each delimited file into the same table every time.
2. move the files to a "processed" sub-directory once the import is complete.
3. since the files will be uniquely named, the module needs to "loop" until all files in the directory have been processed.
4. this code needs to execute each time the .mdb file is opened by a user.
5. if no files are in the directory, do nothing.

I think that about covers it. Thanks in advance for the assistance.

[CreganTur]

Welcome to the forum- it's always good to see new users.

This is a complex request, so I'm going to point you in the right direction for your different issues. After you've done some leg work, let us know if you have any difficulties- we'd be more than happy to help!


1. import the data from each delimited file into the same table every time.

I would suggest looking into the TransferText method- be sure to use acImport. If you cannot use TransferText because of differences between the Table and the format of the Spreadsheet, then I would use an ADO connection to create a recordset based on the Spreadsheet and use the ado.execute method within a loop to run an append query to add each record to the table.


2. move the files to a "processed" sub-directory once the import is complete.

You won't be moving the files- you'll copy them to the new directory, then delete them from the original directory. There are a number of articles on the web and posts here regarding file IO.


3. since the files will be uniquely named, the module needs to "loop" until all files in the directory have been processed.


Since you will be moving worked files to a different directory, you could just have the code work for each file in the folder, regardless of the name. mdMackillop created a great KB article that shows how to loop through all files in a folder.


4. this code needs to execute each time the .mdb file is opened by a user.

Use the Startup option to define what Form is opened when a User launches Access. Have the Form's Load event fire your code.


if no files are in the directory, do nothing.

Look at MD's code, see if you can figure out how to have it check for no files.

HTH:thumb