Use VBA to get the correct number of records in a Recordset object
Does your Recordset return -1 for its RecordCount property? If yes, this article will be able to help you and explain the cause and fixes.
If you have used Microsoft ADO in your VBA project, you must have used Recordset object. Recordset is just a temporary table in memory that contains rows and columns of data. You can navigate through the recordset and do whatever processing you are legit to do.
Recordset object has a RecordCount property that indicates how many records in the Recordset. When I first used ADO in 1999, it had puzzled me for over a year why the RecordCount always returned -1. After many tests and digs into the Help document, I realized the culprit was the type of cursor I used for the record set.
By default, ADO implicitly creates a forward only cursor for a recordset and returns -1 in the RecordCount property when one of the following methods is used.
The first two code examples below demonstrate how to create recordset with forward only cursor. Then two more examples are given to show how to create recordsets that return the valid record count.
To get the code on this page to work, you need to add a reference to Microsoft ActiveX Data Objects Library (any version from 2.0 to 2.8) in Northwind database.
In VBA editor, click Tools -> References...
How to create Recordset that returns -1 on the RecordCount property
The following two code snippets implicitly create forward only cursor and will return -1 on the RecordCount property.
To see how the code below works, in Access Northwind database, create a form with two buttons and name them as cmdCnnExecute and cmdRstOpen. Copy the code into their relevant On Click event procedure.
(1) Use Execute method of the Connection object - RecordCount returns -1.
The Execute method returns a recordset with forward only cursor type.
(2) Use Open method of the Recordset object with default cursor type - RecordCount returns -1.
In this example, the Open method creates a recordset of a forward only cursor type. This is because the cursor type argument is omitted in the method, so the default argument adOpenForwardOnly is used.
The ways to obtain a valid count
Now we're going to look at how to get the correct count by using RecordCount property.
(1) Client-side cursor
When a recordset's cursor location is specified as client-side, it always returns the valid count for RecordCount property.
Client-side cursor is specified in VBA as below.
When a recordset is client-side, after it's retrieved from the database server (eg. Oracle, MySQL database server, etc.), it's sent to the client tier (eg. your workstation PC for MS Access application, or web server for an internet application) and made disconnected from the database server, so it's static in nature. Any changes in the database after the retrieval won't affect the data in the recordset.
Please note that, for local Access database, the server and client are on the same machine because server is your Access Jet engine and client is your VBA engine.
(2) Server-side Static or Keyset cursor.
When a recordset is opened, by default it's opened as a server-side, forward only cursor. If we change its cursor type to static or keyset cursor, it will return the valid count for RecordCount property.
The following code opens a static cursor.
The following code opens a keyset cursor.
To understand more about the pros and cons for client-side and server-side cursor, refer to Microsoft article Client-Side Cursors Versus Server-Side Cursors.
Example of client-side cursor that returns actual count
Code below shows how a client-side cursor returns the actual record count. In Northwind database, on the form you are using for testing, create a new button named cmdRecordCountClientSide, then copy the code below in its On Click event.
Immediate Window shows the actual count for client-side cursor.
Example of all four server-side cursor types
Code below shows four recordsets with four different cursor types. Static and keyset cursor return the actual count. Forward only and dynamic cursor return -1. Because we didn't specify cursor location, it defaults to server-side. On the other hand, because the records are retrieved from local Access Northwind database, the client-side and server-side are both on the local computer.
In Northwind database, on the form you are using for testing, create a new button named cmdRecordCount, then copy the code below in its On Click event.
Immediate Window shows four values.
(1) If you're (in rare situations) using a static cursor or keyset cursor but still get -1, specifying lock type as adLockOptimistic may fix your problem.
(2) If a dynamic cursor is specified, we may get either -1 or the actual count depending on the circumstances, but forward only cursor always returns -1.
Copyright © 2017 GeeksEngine.com. All Rights Reserved.
This website is hosted by HostGator.
No portion may be reproduced without my written permission. Software and hardware names mentioned on this site are registered trademarks of their respective companies. Should any right be infringed, it is totally unintentional. Drop me an email and I will promptly and gladly rectify it.