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:

• VBA Access Recordset, Object Library

Example:

consider the following 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:

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:

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

• VBA Access Get Record Count

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

• RecordCount.Zip

See also:

• Microsoft MSDN: Cursor Type Property (ADO)
• VBA Access Get Record Count

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