Solved

Querying within an ADODB.Recordset

Posted on 2002-07-23
5
215 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

Free Tool: Postgres Monitoring System

A PHP and Perl based system to collect and display usage statistics from PostgreSQL databases.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
VB6 - Compare and highlight cell not the same 3 54
How to Add / Edit Windows Menu 4 68
vb6 - Transfer from MSHFlexgrid1 to xls issue 8 56
Problem to line 23 55
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…
Most everyone who has done any programming in VB6 knows that you can do something in code like Debug.Print MyVar and that when the program runs from the IDE, the value of MyVar will be displayed in the Immediate Window. Less well known is Debug.Asse…
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…
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…

828 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