DoCmd.TransferText fails sometimes

[ercanu]

hi everyone:

i am trying to import an csv file from internet to my database.

i use these vars:

myurl= "www.heresmyfile/table.csv"
mypath= "C:\users\me\table.csv

'i use the API function URLDownloadtofile to download the
'csv file to my hard disk

anything=URLDonwloadToFile(0, myurl, mypath,0,0)

'later, i use DoCmd.TransferText to pick the created
'csv file and insert to my bd as a table

DoCmd.TransferText acImportDelim, "Table_name", mypath, true

this program has a loop (about 2000 iterations), it runs good for the first 5 or 6 iterations, then show the error message (it is not exactly cause my office is in another language, beg ur pardon for my traslation):

"runtime error 3051:
the microsoft access database engine cannot open the file ''. It is already opened exclusively by another user, or you need permission to view its data.

i read about some ppl who had a similar trouble. i created the function SLEEP to give more time for the DoCmd.TransferText execution. it works, but i have to give more than 3 seconds (SLEEP 3000) to avoid the error, which it happens anyway, but less frequently. And the program takes more time to complete.

Thanks in advance

[jimatqsi]

Is it possible to know how many rows should be imported? You could query the import destination file to ascertain completion before looping to the next file.

Jim

[Rabbit]

Or check that the download is finished before trying to import it.

[NeoPa]

It seems to me you have two fundamental options if, as seems the case, your file is still downloading at the point that you try to import it :

1. Open the file (Using Open #) with the Exclusive option first.
   Loop around until this is possible.
   Include a DoEvents statement within the loop (Not SLEEP).
2. Attempt to rename the file to itself before doing the Transfer.
   Again, loop with DoEvents until it works.