Solved

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

Posted on 2014-12-13
16
517 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
Find Ransomware Secrets With All-Source Analysis

Ransomware has become a major concern for organizations; its prevalence has grown due to past successes achieved by threat actors. While each ransomware variant is different, we’ve seen some common tactics and trends used among the authors of the malware.

 
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

Highfive + Dolby Voice = No More Audio Complaints!

Poor audio quality is one of the top reasons people don’t use video conferencing. Get the crispest, clearest audio powered by Dolby Voice in every meeting. Highfive and Dolby Voice deliver the best video conferencing and audio experience for every meeting and every room.

Join & Write a Comment

QuickBooks® has a great invoice interface that we were happy with for a while but that changed in 2001 through no fault of Intuit®. Our industry's unit names are dictated by RUS: the Rural Utilities Services division of USDA. Contracts contain un…
I originally created this report in Crystal Reports 2008 where there is an option to underlay sections. I initially came across the problem in Access Reports where I was unable to run my border lines down through the entire page as I was using the P…
Learn how to number pages in an Access report over each group. Activate two pass printing by referencing the pages property: Add code to the Page Footers OnFormat event to capture the pages as there occur for each group. Use the pages property to …
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

747 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

11 Experts available now in Live!

Get 1:1 Help Now