Solved

From within MS Access VBA How do I Count records in a recordset not using MoveLast

Posted on 2014-12-13
16
538 Views
Last Modified: 2014-12-14
I have a qdf recordset within MS Access VBA.  Using this recordset I am currently doing a
rs.MoveLast
lngRecCount = rstEmployees.RecordCount


I am told I need to move last since the query does not get fully populated until I move last.  I also read to avoid this if working with large recordsets.  If I am dealing with a large recordset how can I do this efficiently.  Note I need to know the record count as I populate a variable with this value.

From MSDN "Using the MoveLast method to populate a newly opened Recordset negatively impacts performance. Unless it is necessary to have an accurate RecordCount as soon as you open a Recordset, it's better to wait until you populate the Recordset with other portions of code before checking the RecordCount property."
0
Comment
Question by:upobDaPlaya
  • 5
  • 5
  • 3
  • +3
16 Comments
 
LVL 33

Assisted Solution

by:Mike Eghtebas
Mike Eghtebas earned 50 total points
ID: 40498583
If you use ADODB instead of DAO, then you don't need to use move last.

Dim rs As ADODB.Recordset
0
 
LVL 26

Expert Comment

by:Nick67
ID: 40498590
I suppose you could, in code, do a DCount() of the underlying query.
Or you could construct a second query that ALL that it does and returns is a Count of some field that will be equivalent to the recordset.count

Domain aggregate functions (Dmax, DCount, and the badly named Dfirst) are to be avoided though.
Performance pigs.
0
 
LVL 26

Expert Comment

by:Nick67
ID: 40498593
If you use ADO instead of DAO, then you don't need to use move last.
1. ADO is no longer MS's recommended manner of doing anything in new versions of Access
2. ADO will report not always report the exact number of records -- just how does that help the Asker get the value for his variable if conditions aren't right?

The RecordCount property returns a long value that indicates the number of records in a Recordset object.

If the Recordset object supports AbsolutePosition and AbsolutePage properties or bookmarks (if Supports(adApproxPosition) or Supports(adBookmark) returns true), this property will return the exact number of records in the Recordset.

Note: This property will return -1 for a forward-only cursor; the actual count for a static or keyset cursor; and -1 or the actual count for a dynamic cursor.

Note: The Recordset object must be open when calling this property. If this property is not supported it will return -1.

And the ADO cursors that do return exact recordcounts are performance pigs too:
http://www.adopenstatic.com/faq/recordcountalternatives.asp
The use of .RecordCount involves the creation of Recordsets using relatively expensive cursors (click here for details). There are a number of alternative methods that can be used. Listed below are three preferred methods, and two methods that suck (and why they suck).

The preferred methods are:
•Query the SysIndex table (SQL Server only)
•Use of SELECT Count() when you only need a record count by not the data
•Use of .getRows when you need a record count and the data

The methods that suck are (courtesy of David L Penton):
•incrementing a counter variable while iterating the recordset
•populating the recordset via .movelast
0
 

Author Comment

by:upobDaPlaya
ID: 40498617
Hi nick. this is a parameter query and thus I'm using a QDEF. Thus is it possible to do your suggested select COUNT in order to get the record count if my recordset is a parameter query
0
 
LVL 75

Assisted Solution

by:DatabaseMX (Joe Anderson - Access MVP)
DatabaseMX (Joe Anderson - Access MVP) earned 75 total points
ID: 40498633
How many records ARE in this Recordset ?

With today's FAST systems, It's unlikely the MoveLast is going to have any noticeable impact on performance, unless you have several hundred records ... and even then ... it may not. So ... use the MoveLast method.

As as a side note, before you execute a MoveLast, you should check for a RecordCount=0 ... because if it is zero, then the MoveLast will generate an error.

mx
0
 
LVL 49

Expert Comment

by:Gustav Brock
ID: 40498817
If you don't do updates on the recordset, you can open it read-only as a snapshot. That will read in all records at once, thus obtaining the exact recordcount:

    Set rstEmployees = qdy.OpenRecordset(dbOpenSnapshot)
    lngRecCount = rstEmployees.RecordCount

That said, using MoveLast, MoveFirst may be faster if you don't need the record data.

/gustav
0
 
LVL 57

Assisted Solution

by:Jim Dettman (Microsoft MVP/ EE MVE)
Jim Dettman (Microsoft MVP/ EE MVE) earned 75 total points
ID: 40499007
Read-only, but not snapshot!   That's the worst performance hog of all.

You asking the db engine to make a complete copy of every record.

DCount("*") or Count(*) is the fastest way to get an accurate count.

Jim.
0
 
LVL 49

Expert Comment

by:Gustav Brock
ID: 40499011
Who knows? If upop needs the complete recordset after the recordcount, nothing is wasted.

/gustav
0
Backup Your Microsoft Windows Server®

Backup all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

 
LVL 26

Expert Comment

by:Nick67
ID: 40499039
For a change, I had posted what wad asked instead of inferred.  I do think there are only a very few instances where .MoveLast isn't the best idea in the whole scope of an RS call.  Perhaps the asker had one.  In that case a new querydef with Count is the best way to go.
0
 

Author Comment

by:upobDaPlaya
ID: 40499149
Thanks for all the feedback.  It has been very educational.  When people are commenting if I will use the recordset after the count I am not sure what that means..

I am creating the recordset via qdf since it is a paramater query
Once I create the rs I count the recordset
I then do ws.Range ("A2") .CopyFrom Recordset rs

I then copy a formula down from a cell that already exist in the excel spreadsheet and copy it down to the very last cell of the data in the worksheet which is why I need to do the record count...

Does the above that I just outlined constitute "If upop needs the complete recordset after the recordcount"

If so I will follow Nicks suggestion and "In that case a new querydef with Count is the best way to go"
0
 
LVL 26

Accepted Solution

by:
Nick67 earned 175 total points
ID: 40499158
For a copyfromrecordset you will be using all the data from the recordset.  .MoveLast is the best and most appropriate way to populate the recordset and get its count.  There will be no wastage involved.
0
 
LVL 49

Assisted Solution

by:Gustav Brock
Gustav Brock earned 125 total points
ID: 40499193
>  if I will use the recordset after the count I am not sure what that means..

That means if you just want to count the records or if you afterwards are reading all the data for any other purpose.
And you do that. You read all the data into the WorkSheet.

> I then do ws.Range ("A2") .CopyFrom Recordset rs

Thus, you can use a snapshot and have the recordcount at once.
However, you are not using the recordcount before but after you have read the records. Then the RecordCount does reflect the actual count of records as you, of course, have read the recordset from first to last records. Doing a MoveLast before or after this is moot.

> Does the above that I just outlined constitute "If upop needs the complete recordset after the recordcount"

Yes.

/gustav
0
 

Author Comment

by:upobDaPlaya
ID: 40499269
Makes total sense.  Once I get my record count and then move to the next line of code
(ws.Range ("A2") .CopyFrom Recordset rs ) I assume then the total recordset is available to me and I do not need to do a MoveFirst...May seem like a dumb question, but its worth asking...
0
 

Author Comment

by:upobDaPlaya
ID: 40499365
I think I have answered my own question as if I do not MoveFirst then only the last record gets copied over to MS Excel...
0
 
LVL 26

Expert Comment

by:Nick67
ID: 40499367
Now I know that too
0
 

Author Closing Comment

by:upobDaPlaya
ID: 40499594
Great discussion and the solution works great...thx
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

Suggested Solutions

Title # Comments Views Activity
Send a .ZPL file to a network printer from Access 8 52
Why can't I get my report to SORT correctly? 5 27
Tags from access to excel 3 28
Syntax Error in Query 7 30
In the article entitled Working with Objects – Part 1 (http://www.experts-exchange.com/Microsoft/Development/MS_Access/A_4942-Working-with-Objects-Part-1.html), you learned the basics of working with objects, properties, methods, and events. In Work…
Introduction The Visual Basic for Applications (VBA) language is at the heart of every application that you write. It is your key to taking Access beyond the world of wizards into a world where anything is possible. This article introduces you to…
Using Microsoft Access, learn some simple rules for how to construct tables in a relational database. Split up all multi-value fields into single values: Split up fields that belong to other things into separate tables: Make sure that all record…
In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…

910 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

25 Experts available now in Live!

Get 1:1 Help Now