Importing excel file ...too slow

[ahjeck]

Hi, I have a relatively slow machine and it seems to be affecting the way I import an excel file data to a temp table, while immediately looking into that temp table afterwards.

DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel97, "tmpStudentImport", "C:\StudentImport.xls", True

Do While Not rst.EOF
 . . .
 . . .

where rst is opening tmpStudentImport

The problem is, the code done in the while loop stops prematurely (does not go through the whole tmpStudentImport table) 'sometimes'. When I open the form too quickly and run the import, it stops prematurely with no errors (I checked and it indeed does go to EOF). If I open the form and wait for everything to finish loading, and then do the import, it imports everything fine.

My question is, is there a way to check, via code, that the export has been completed?, before starting the while loop? I was thinking on adding a dummy loop between it so it will do the loop (for no good reason) only to stop it from reading quickly.

Suggestions? Thanks in advance!

 

[ahjeck]

Ok I don't know but it appears that I had the rst = db.openrecordset("tmpStudentImport", dbOpenSnapshot) line declared before it was imported so that may have caused the problem??

So my guess is, the tmpStudentImport was left open while an import was going on, therefore the EOF may have glitched .

Does that seem valid? It doesn't seem to have premature stops anymore.

 

[Moniker]

Did you try throwing in a DoEvents right after the import? You may also want a DoCmd.Hourglass True before the import and a DoCmd.Hourglass False after the DoEvents.

 

[ahjeck]

Thanks for the reply moniker. I'm not too familiar with the DoEvents function. But I will give it a shot. Thanks!

In addition, when importing to the temp folder, I placed a break right after the parsing so that I could check where the last record was broken before the EOF. Sure enough it was coming out prematurely, but the loop was satisfied as well. I checked the temp table and there were more records than what was originally read.