?
Solved

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

Posted on 2014-12-13
16
Medium Priority
?
848 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
[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
  • 5
  • 3
  • +3
16 Comments
 
LVL 34

Assisted Solution

by:Mike Eghtebas
Mike Eghtebas earned 200 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
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 

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 - Microsoft MVP, Access and Data Platform)
DatabaseMX (Joe Anderson - Microsoft MVP, Access and Data Platform) earned 300 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 52

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 58

Assisted Solution

by:Jim Dettman (Microsoft MVP/ EE MVE)
Jim Dettman (Microsoft MVP/ EE MVE) earned 300 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 52

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

Assisted Solution

by:Gustav Brock
Gustav Brock earned 500 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

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

This article describes two methods for creating a combo box that can be used to add new items to the row source -- one for simple lookup tables, and one for a more complex row source where the new item needs data for several fields.
Instead of error trapping or hard-coding for non-updateable fields when using QODBC, let VBA automatically disable them when forms open. This way, users can view but not change the data. Part 1 explained how to use schema tables to do this. Part 2 h…
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …
Have you created a query with information for a calendar? ... and then, abra-cadabra, the calendar is done?! I am going to show you how to make that happen. Visualize your data!  ... really see it To use the code to create a calendar from a q…

649 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