?
Solved

Querying within an ADODB.Recordset

Posted on 2002-07-23
5
Medium Priority
?
218 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
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 400 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

On Demand Webinar: Networking for the Cloud Era

Did you know SD-WANs can improve network connectivity? Check out this webinar to learn how an SD-WAN simplified, one-click tool can help you migrate and manage data in the cloud.

Question has a verified solution.

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

I’ve seen a number of people looking for examples of how to access web services from VB6.  I’ve been using a test harness I built in VB6 (using many resources I found online) that I use for small projects to work out how to communicate with web serv…
Background What I'm presenting in this article is the result of 2 conditions in my work area: We have a SQL Server production environment but no development or test environment; andWe have an MS Access front end using tables in SQL Server but we a…
Get people started with the process of using Access VBA to control Outlook using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Microsoft Outlook. Using automation, an Access applic…
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…

752 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