In this article I will explain how you can get the number of records in a Recordset object.

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

Note: Previously I’ve explained how you can get the number of records in a Recordset using the Recordset.Recordcount property. Depending on the cursor type chosen when opening the Recordset object this may or may not work. For more information about using this property please see the link below:

• VBA Access, Recordset.Recordcount

Example:

consider the table below. It has 20 records:

The code below will return the number of records in the table:

Sub Example1()
Dim objRecordset As ADODB.Recordset
Set objRecordset = New ADODB.Recordset
Dim intCount As Integer
Dim flag As Boolean

'initated recordset obejct
objRecordset.ActiveConnection = CurrentProject.Connection
Call objRecordset.Open("MyTable1")
flag = True
intCount = 0
'loops until end of recordset is reached
While objRecordset.EOF = False
    'increment record counter
    intCount = intCount + 1
    'move to next record
    objRecordset.MoveNext
Wend
MsgBox (intCount)
End Sub


Result:

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

• Get Record Count.Zip

See also:

• VBA Access, Recordset.Recordcount

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