Solved

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

Posted on 2014-12-13
16
576 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
Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

 

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
 
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

The Eight Noble Truths of Backup and Recovery

How can IT departments tackle the challenges of a Big Data world? This white paper provides a roadmap to success and helps companies ensure that all their data is safe and secure, no matter if it resides on-premise with physical or virtual machines or in the cloud.

Question has a verified solution.

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

In the previous article, Using a Critera Form to Filter Records (http://www.experts-exchange.com/A_6069.html), the form was basically a data container storing user input, which queries and other database objects could read. The form had to remain op…
Overview: This article:       (a) explains one principle method to cross-reference invoice items in Quickbooks®       (b) explores the reasons one might need to cross-reference invoice items       (c) provides a sample process for creating a M…
Basics of query design. Shows you how to construct a simple query by adding tables, perform joins, defining output columns, perform sorting, and apply criteria.
What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.

785 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