VBA Access, Recordset.Recordcount

The ADODB.Recordset object has a recordcount property. This property returns the number of records in the recordset. This property may or may not work correctly depending on the cursor type chosen. For the following cursor types this property will work correctly:

  • adOpenKeyset
  • adOpenStatic

but for the following cursor types it will always return “-1”:

  • adOpenDynamic
  • adOpenForwardOnly

This article uses the ADODB.Recordset object. In order to use this object you may need to add reference to ADODB object library. For more information on this topic please see the link below:


Example:

consider the following table:
Table

Both example codes below will return the number of records in the table:

Sub Example1()
Dim objRecordset As ADODB.Recordset
Set objRecordset = New ADODB.Recordset
Dim i As Integer
Dim value As Variant

'initated recordset obejct
objRecordset.ActiveConnection = CurrentProject.Connection
Call objRecordset.Open("MyTable1", , adOpenKeyset)
MsgBox (objRecordset.RecordCount)
End Sub

and

Sub Example2()
Dim objRecordset As ADODB.Recordset
Set objRecordset = New ADODB.Recordset
Dim i As Integer
Dim value As Variant

'initated recordset obejct
objRecordset.ActiveConnection = CurrentProject.Connection
Call objRecordset.Open("MyTable1", , adOpenStatic)
MsgBox (objRecordset.RecordCount)
End Sub

Result:
Result

But the codes below will return “-1”:

Sub Example3()
Dim objRecordset As ADODB.Recordset
Set objRecordset = New ADODB.Recordset
Dim i As Integer
Dim value As Variant

'initated recordset obejct
objRecordset.ActiveConnection = CurrentProject.Connection
Call objRecordset.Open("MyTable1", , adOpenDynamic)
MsgBox (objRecordset.RecordCount)
End Sub

and

Sub Example4()
Dim objRecordset As ADODB.Recordset
Set objRecordset = New ADODB.Recordset
Dim i As Integer
Dim value As Variant

'initated recordset obejct
objRecordset.ActiveConnection = CurrentProject.Connection
Call objRecordset.Open("MyTable1", , adOpenForwardOnly)
MsgBox (objRecordset.RecordCount)
End Sub

Result:
Result 2

Note: Another method for getting the record count that will always work has been explained in the article below:

You can download the file and code used in this article from the link below:

See also:

If you need assistance with your code, or you are looking for a VBA programmer to hire feel free to contact me. Also please visit my website  www.software-solutions-online.com

Leave a Reply

Your email address will not be published. Required fields are marked *