Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 220
  • Last Modified:

Querying within an ADODB.Recordset

I want to bring back a recordset of data from an mdb database (via ADO) using a SQL query to display the data in a grid.  I then subsequently wish to perform sub-querying on that recordset to calculate various statistics, but want to avoid having to query the original table in its entirety over and over.
Is this possible? (NB The recordset would not be detached). Would it be as quick to cycle through using the Recordset object functions to look at the rows one at a time and calculate the stats using a loop function?
0
stuart_edwards
Asked:
stuart_edwards
1 Solution
 
RainUKCommented:
well to reduce the number of rows you have to iterate through you could use the Filter or Find command. Filter will reduce the number of rows if the criteria match. When you want to then query again just set the filter to NONE.

adoRs.Filter "CustomerID = " & lngCustomerID
While adoRs.EOF = False
   ' Do your processing
   adoRs.movenext
Wend
adoRs.Filter = ADODB.FilterGroupEnum.adFilterNone


0
 
EDDYKTCommented:
I htink you can clone the recordset and then do the filtering on the new recordset. Otherwise it will interfere the display from the grid
0
 
PaulHewsCommented:
>>Would it be as quick to cycle through using the Recordset object functions to look at the rows one at a time and calculate the stats using a loop function? <<

No.  The filter will allow you to reduce rows, but if you wish to use aggregate functions (like sum or count) then you will need to requery your database.  The aggregate queries work *very* quickly compared to iterating through the rows, so in that case, you are much better off requerying the database.
0
 
selim007Commented:
i sugget using multiple queries which are faster to process than the vb code.but certainly u must set the correct field indexes so it won't slow down.
after all, it depends on the query type u need to run.
i don't know anything about your case and required queries but check the adors.seek and adors.filter may be usefull.
these commands also requires indexed fields
0
 
stuart_edwardsAuthor Commented:
Thanks Paul - I am performing aggregate functions and this performs very quickly indeed.

Cheers

Stuart
0

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Tackle projects and never again get stuck behind a technical roadblock.
Join Now