When is transfer text finished?

[SamirNaenenjad]

I have the following code to import a text file into access:

db.Execute "DELETE * FROM tblTest"

DoCmd.TransferText acImportDelim, "ImportSpec", "tblTest", "c:\Test.txt", True

The problem is that I imediately run some queries through ADO that manipulate the data, but when I open the table in a recordset I get 0 records. If I do 10K "DoEvents" before opening the table, I get the whole table.

So long story short, is there anything I can test for to see if the text import is finished, or do I just have to guess based on the file size how many loops I need to run through before manipulating the table?

Thanks in advance,
A.J.

 

[bev]

I was going to post a question which is the opposite of this one: Perhaps somebody could help me with this as well. I'm using DoCMD.RunSQL to select records into a table. Then I'm using DoCmd.TransferSpreadsheet to export the contents of the table to an Excel spreadsheet. The transfer begins before the SQL statement has finished building the table. Is there a way to know when the SQL statement is completed?
Bev

 

[deboyz]

AJ

My suggestion is to set a recordset object for tblTest and run a loop that determines the record count. If the record count property is greater than zero and it doesn't change for a few seconds I would assume the transfer has completed.

bev

My suggestion is to create an excel object, workbook object, worksheet object and range object from code and then use the copyfromrecordset method of range to transfer the recordset you create from the SQL statement.