Solved

Querying within an ADODB.Recordset

Posted on 2002-07-23
5
213 Views
Last Modified: 2008-03-06
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
Comment
Question by:stuart_edwards
5 Comments
 
LVL 5

Expert Comment

by:RainUK
ID: 7171502
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
 
LVL 26

Expert Comment

by:EDDYKT
ID: 7171605
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
 
LVL 38

Accepted Solution

by:
PaulHews earned 100 total points
ID: 7171794
>>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
 
LVL 2

Expert Comment

by:selim007
ID: 7171937
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
 

Author Comment

by:stuart_edwards
ID: 7180128
Thanks Paul - I am performing aggregate functions and this performs very quickly indeed.

Cheers

Stuart
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

There are many ways to remove duplicate entries in an SQL or Access database. Most make you temporarily insert an ID field, make a temp table and copy data back and forth, and/or are slow. Here is an easy way in VB6 using ADO to remove duplicate row…
The debugging module of the VB 6 IDE can be accessed by way of the Debug menu item. That menu item can normally be found in the IDE's main menu line as shown in this picture.   There is also a companion Debug Toolbar that looks like the followin…
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
This lesson covers basic error handling code in Microsoft Excel using VBA. This is the first lesson in a 3-part series that uses code to loop through an Excel spreadsheet in VBA and then fix errors, taking advantage of error handling code. This l…

895 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now