VBA Access Get Record Count

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:

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:


Example:

consider the table below. It has 20 records:
Table

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:
Result

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 *