best method to find next file

[amberH]

Hi all,
I'm pretty sure the way I'm doing things is NOT the most efficient method...
As I've posted in the past, this is my situation:
I have a folder, where autogenerated text files are placed throughout the day (no set number).
I have my database going in to the folder, opening up the text files, and transferring all the data to Access tables.
The files are named "AL001234.hst" where 001234 is a number that increases by 1 every time a new file is created.
Now I have it working, but I know there has to be a better way.
One problem I've found is that occasionally there are missing numbers, ie it will jump from "AL005501" to "AL 005510". The method I was originally using was saving the number section of each file name in a table, and checking the last number, adding one, and opening up the next one. This obviously doesn't work when there are gaps.
Another problem I have is...the same data stays in the folder for a couple months, and I don't want the same files to be pulled in more than once.
Does anyone have a suggestion of how I could be doing this better. I can think of a few ways to do it...but they would take a LONG time to run through.
Thanks!!
amber

 

[cheerio]

Set up a table with two fields - FileName and Done.

Populate the table with all the names you might need for the next few months (Excel is probably the easiest way to do this).

Set the Done column to Yes for all those that are done and to No for the rest. If you use a character field you can have a third value to indicate files that were skipped and will not be used.

When you want to import just try each undone filename in ascending order to see if it exists until either you find one that exists or the gap is so big you can assume one does not exist.

From time to time use the same query ro identify skipped files and mark them as skipped so the import process will not waste time looking for them.

Ken

Ken

 

[MichaelRed]

Or, skip the Well done, Rotted, and rare stuff. Just store the names of the well done ones in the table. When you check for new files, just check all of the files in the dir (use DIR function). If a file exists in the folder / directory and it is NOT in the list (table) then import it.

Another approach would be to create a "donedir" as a sub-folder (directory) to the active one. When a file is processed, MOVE it t the "donedir". Whenever you process the dir contents, all of the files in it are "new" and ned to be processsed.

MichaelRed
m.red@att.net

There is never time to do it right but there is always time to do it over