upobDaPlaya
asked on
From within MS Access VBA How do I Count records in a recordset not using MoveLast
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."
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."
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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?
And the ADO cursors that do return exact recordcounts are performance pigs too:
http://www.adopenstatic.com/faq/recordcountalternatives.asp
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
ASKER
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
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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(dbOpenSn
lngRecCount = rstEmployees.RecordCount
That said, using MoveLast, MoveFirst may be faster if you don't need the record data.
/gustav
Set rstEmployees = qdy.OpenRecordset(dbOpenSn
lngRecCount = rstEmployees.RecordCount
That said, using MoveLast, MoveFirst may be faster if you don't need the record data.
/gustav
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Who knows? If upop needs the complete recordset after the recordcount, nothing is wasted.
/gustav
/gustav
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.
ASKER
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"
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"
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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...
(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...
ASKER
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...
Now I know that too
ASKER
Great discussion and the solution works great...thx
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.