Solved

Querying within an ADODB.Recordset

Posted on 2002-07-23
5
212 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
Comment Utility
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
Comment Utility
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
Comment Utility
>>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
Comment Utility
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
Comment Utility
Thanks Paul - I am performing aggregate functions and this performs very quickly indeed.

Cheers

Stuart
0

Featured Post

Why You Should Analyze Threat Actor TTPs

After years of analyzing threat actor behavior, it’s become clear that at any given time there are specific tactics, techniques, and procedures (TTPs) that are particularly prevalent. By analyzing and understanding these TTPs, you can dramatically enhance your security program.

Join & Write a Comment

Have you ever wanted to restrict the users input in a textbox to numbers, and while doing that make sure that they can't 'cheat' by pasting in non-numeric text? Of course you can do that with code you write yourself but it's tedious and error-prone …
This article describes some techniques which will make your VBA or Visual Basic Classic code easier to understand and maintain, whether by you, your replacement, or another Experts-Exchange expert.
As developers, we are not limited to the functions provided by the VBA language. In addition, we can call the functions that are part of the Windows operating system. These functions are part of the Windows API (Application Programming Interface). U…
Get people started with the process of using Access VBA to control Excel using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Excel. Using automation, an Access application can laun…

743 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

15 Experts available now in Live!

Get 1:1 Help Now