Multiple Imports weirdness (Access 2k)

Hi all,

I have written a sub to import about 25000 files into access. Most are only 1 record. Basically it loops through the directory, checks if the file has been imported already (via SQL query to my import auditlog), imports it and writes to the Auditlog.

No problems so far. However, when checking the data, it appears that some files have not actually been imported, despite the auditlog telling me they have. Im guessing Access is losing the plot somewhere with its memory etc (sometimes it flips completely and tells me it cant find the import spec despite having used it 9000 times already) and not actually writing the data to the table sometimes.

So....

1) Anybody had this problem before and worked out a solution?

2) If not, do you think DoEvents after the TransferText line will make a difference?

Im going to plump for the DoEvents if noone has any better suggestions, but this thing takes ages to run and I'd rather not waste my time if at all possible.

Cheers
J.

instead of using the transfertext method, you could try directly accessing the text files using the Open statement, the Line Input statement, the Close statement etc to see if that helps with memory management.

I actually do a large amount of data manipulation and file cleaning using the methods you described, you think it would be quicker to read in each line and INSERT the data into my database?

i don't know if it would be quicker. but trying different solutions to problems is the name of the programming game. if something doesn't work, then you try something else until you find something that does. i'm a programming professional and that's one of the ways i solve programming problems.

Actually it was quicker to reformat them into their separate files then write each one into 1 big file and TransferText that. I already had my auditlog populated :).