|
Custom Search
| |
|
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.
Private Sub cmdCnnExecute_Click() On Error Goto Catch Dim strSql As String Dim objCnn As ADODB.Connection Dim objRst As ADODB.Recordset '' Get all categories strSql = "select CategoryID, CategoryName from Categories" Set objCnn = CurrentProject.Connection Set objRst = New ADODB.Recordset '' This implicitly creates a recordset object with forward only cursor by default. Set objRst = objCnn.Execute(strSql) '' Display the number of records in Immediate window. Returns -1 Debug.Print "objRst (adOpenForwardOnly) record count: " & objRst.RecordCount '' Clean up Set objCnn = Nothing objRst.Close Set objRst = Nothing Exit Sub Catch: MsgBox "cmdRecordCount_Click(): " & vbCrLf & vbCrLf _ & "Error#: " & Err.Number & vbCrLf & vbCrLf & Err.Description End Sub (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.
Private Sub cmdRstOpen_Click() On Error Goto Catch Dim strSql As String Dim objRst As ADODB.Recordset '' Get all categories strSql = "select CategoryID, CategoryName from Categories" Set objRst = New ADODB.Recordset '' This creates a recordset object with forward only cursor by default because '' we didn't specify the cursor type enum. objRst.Open strSql, CurrentProject.Connection '' Display the number of records in Immediate window. '' Returns -1 Debug.Print "objRst (adOpenForwardOnly) record count: " & objRst.RecordCount '' Clean up objRst.Close Set objRst = Nothing Exit Sub Catch: MsgBox "cmdRecordCount_Click(): " & vbCrLf & vbCrLf _ & "Error#: " & Err.Number & vbCrLf & vbCrLf & Err.Description End Sub 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. objRst.CursorLocation = adUseClient 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. objRst.CursorType = adOpenStatic '' Or specify adOpenStatic in the Open method directly. objRst.Open strSql, CurrentProject.Connection, adOpenStatic The following code opens a keyset cursor. objRst.CursorType = adOpenKeyset '' Or specify adOpenKeyset in the Open method directly. objRst.Open strSql, CurrentProject.Connection, adOpenKeyset 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.
Private Sub cmdRecordCountClientSide_Click() On Error Goto Catch Dim strSql As String Dim objRst As ADODB.Recordset '' Get all categories from local Northwind database. strSql = "select CategoryID, CategoryName from Categories" Set objRst = New ADODB.Recordset '' Client-side cursor objRst.CursorLocation = adUseClient objRst.Open strSql, CurrentProject.Connection '' Returns the actual count (8 records) Debug.Print "Client-side objRst record count: " & objRst.RecordCount '' Clean up objRst.Close Set objRst = Nothing Exit Sub Catch: MsgBox "cmdRecordCount_Click()" & vbCrLf & vbCrLf _ & "Error#: " & Err.Number & vbCrLf & vbCrLf & Err.Description End Sub
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.
Private Sub cmdRecordCount_Click() On Error Goto Catch Dim strSql As String Dim objRst1 As ADODB.Recordset Dim objRst2 As ADODB.Recordset Dim objRst3 As ADODB.Recordset Dim objRst4 As ADODB.Recordset '' Get all categories from local Northwind database. strSql = "select CategoryID, CategoryName from Categories" '' In code below, because we didn't specify cursor location, it's server-side by default. '' Returns -1 Set objRst1 = New ADODB.Recordset objRst1.Open strSql, CurrentProject.Connection, adOpenForwardOnly '' Returns -1 Set objRst2 = New ADODB.Recordset objRst2.Open strSql, CurrentProject.Connection, adOpenDynamic '' Returns valid count Set objRst3 = New ADODB.Recordset objRst3.Open strSql, CurrentProject.Connection, adOpenKeyset '' Returns valid count Set objRst4 = New ADODB.Recordset objRst4.Open strSql, CurrentProject.Connection, adOpenStatic '' Display the number of records in Immediate window. Debug.Print "objRst1 (adOpenForwardOnly) record count: " & objRst1.RecordCount Debug.Print "objRst2 (adOpenDynamic) record count: " & objRst2.RecordCount Debug.Print "objRst3 (adOpenKeyset) record count: " & objRst3.RecordCount Debug.Print "objRst4 (adOpenStatic) record count: " & objRst4.RecordCount '' Clean up objRst1.Close objRst2.Close objRst3.Close objRst4.Close Set objRst1 = Nothing Set objRst2 = Nothing Set objRst3 = Nothing Set objRst4 = Nothing Exit Sub Catch: MsgBox "cmdRecordCount_Click()" & vbCrLf & vbCrLf _ & "Error#: " & Err.Number & vbCrLf & vbCrLf & Err.Description End Sub
Immediate Window shows four values. Please note: (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.
Happy Coding!
|
Copyright © 2024 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. |
Home | Feedback | Terms of Use | Privacy Policy |