Asynchronus Query

I am using an Aynchronus Query to get data because i may have end up with 40 or 50,000 records. I want to give control to user to cancel at any point to  the query. That i was able to do. But i want the total record count also for the query. When i am using recordset.recordcount it is taking a while and at this point user can not have any control to stop.
 
Is there a way to give control to user at this point or is there another way to get the rcordcount of my query without taking long time.

My code looks like this

rsADOTmp.Open rname, openqr_cnn, adOpenKeyset, adLockOptimistic, adAsyncExecute
     
      While rsADOTmp.State = adStateExecuting 'Or cn.State = adStateOpen
            DoEvents
            GLOBAL_CenterForm frmCancel
            frmCancel.Show
            If SQL_Execute_cancel = True Then
                rsADOTmp.Cancel
                Unload frmCancel
                SQL_Execute_cancel = False
                DoEvents
                Exit Sub
            End If
            DoEvents
       Wend
       
        gcount = rsADOTmp.RecordCount

Here it is taking a while.

If anybody know the answer please let me know

Thanks
svenkatapuramAsked:
Who is Participating?
 
dhodgeConnect With a Mentor Commented:
Yes you should first get the record count by doing a recordset with :

"select count(*) from tablename where condition = x"

substituting tablename and condition as necessary.

The get the value out of the recordset and this represents the number of records that your async query will return, at a fraction of the time.

I have done this in many similar circumstances.
0
 
svenkatapuramAuthor Commented:
Thanks,

 i know that i could use count(*) to get the count of records but in my situtation i do not want to substitue table name and where. So is this the only alternative (fast one)
0
 
dhodgeCommented:
Yes, say your table is called Sales and the criteria is > 1/1/1998

Obviously you don't know if you have 1 or 1000000 rows so do a :

select count(*) from sales
where date > '1/1/1998'

put the result on screen somewhere, before running the async

select * from sales
where date > '1/1/1998'

Try it you will find it really neat.
0
All Courses

From novice to tech pro — start learning today.