By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,504 Members | 2,596 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,504 IT Pros & Developers. It's quick & easy.

Performance Question, Applying Filter to Recordset

TheSmileyCoder
Expert Mod 100+
P: 2,321
I have a read only DAO.Recordset, which I apply a filter on.

Will the application of the filter cause communication between my Access frontend and Access backend?

I would like to optimize my application to reduce stress on the backend, and therefore in general trying to understand when working with recordsets when my frontend communicates with the backend. For instance, if I do FindFirst or a MoveNext will that cause Access to just look in the recordset I allready have open, or will it attempt to contant the backend and refresh the data?

Is there any way to determine when communication goes on between backend and frontend?
Mar 14 '10 #1

✓ answered by ADezii

Unless I am not reading the thread correctly, wouldn't the following SQL work?
Expand|Select|Wrap|Line Numbers
  1. SELECT tblSmiley.ID_Severity, Count(tblSmiley.ID_Severity) AS [Severity Count]
  2. FROM tblSmiley
  3. GROUP BY tblSmiley.ID_Severity
  4. ORDER BY tblSmiley.ID_Severity;
However, it seems that the recordcount is not accurate until I do a MoveLast, Movefirst
Unfortunately, this is often correct, in that you must explicitly Traverse the Recordset in order to obtain an accurate Count of Records. I would imagine that this would more than outweigh any potential benefits from your Filter approach.

Share this Question
Share on Google+
6 Replies


ADezii
Expert 5K+
P: 8,599
  1. You have 2 choices if you want to create a Filtered Subset of Data via the Recordset Object:
    1. Use an SQL WHERE Clause
    2. Use the Filter Property of a DAO/ADO Recordset
  2. If you use the Filter approach, it will take effect immediately.
  3. To the best of my knowledge, it is faster to Open a New Recordset based on an SQL Expression with a WHERE Clause then it is to use the Filter Property.
  4. The New Filtering will never retrieve additional Rows from the original Source Tables but will only Filter Rows that are already in the Recordset.
  5. I'm pretty confident that that same will hold true using a WHERE Clause, namely no additional data will be retrieved from the Source Tables.
  6. You can also supply an Array of Bookmarks that are valid in the Current Recordset to Filter the Recordset to show only specific Records.
  7. Hope this helps.
Mar 14 '10 #2

TheSmileyCoder
Expert Mod 100+
P: 2,321
As always thanky you for your input!

How would you go about adding a Where clause AFTER the recordset is opened?
Mar 14 '10 #3

ADezii
Expert 5K+
P: 8,599
The WHERE Clause would be applied prior to creating the actual Recordset, thus the Filtering.
Mar 14 '10 #4

TheSmileyCoder
Expert Mod 100+
P: 2,321
I have a table with a field called ID_Severity. For 10 values I need to count how many times that variable occurs in my table.

I need to count how many times ID_Severity=1, how many times it equals 2 and so on. My approach to this, in order to avoid running 10 Dcount was to retrieve the full recordset, then apply a filter (for example: ID_Severity=1) , do a recordcount, apply new filter, do another recordcount and so, so I only need to run 1 query against the server, and handle the rest locally. Im using DAO recordset, dbOpenDynaset, dbReadOnly.

However, it seems that the recordcount is not accurate until I do a MoveLast, Movefirst, and it seems to count independent of my filtering (or I mucked something up :P)

Whats the best approach to getting my 10 Counts done, with the least amount of traffic/work to the server? Recordset OR SQL solution
Mar 14 '10 #5

ADezii
Expert 5K+
P: 8,599
Unless I am not reading the thread correctly, wouldn't the following SQL work?
Expand|Select|Wrap|Line Numbers
  1. SELECT tblSmiley.ID_Severity, Count(tblSmiley.ID_Severity) AS [Severity Count]
  2. FROM tblSmiley
  3. GROUP BY tblSmiley.ID_Severity
  4. ORDER BY tblSmiley.ID_Severity;
However, it seems that the recordcount is not accurate until I do a MoveLast, Movefirst
Unfortunately, this is often correct, in that you must explicitly Traverse the Recordset in order to obtain an accurate Count of Records. I would imagine that this would more than outweigh any potential benefits from your Filter approach.
Mar 14 '10 #6

TheSmileyCoder
Expert Mod 100+
P: 2,321
THANK YOU!
Sometimes the obvious stares you so hard in the face it blinds you....

I think I was so focused on getting it in seperate columns (see example), I didn't see the simple solution.

Expand|Select|Wrap|Line Numbers
  1. CountOf1   CountOf2   CountOf3   CountOf4
  2.    1          5          3           6
Mar 14 '10 #7

Post your reply

Sign in to post your reply or Sign up for a free account.