Editor’s Note: This tip is an update to a previous Tip of the Day. Jai Bardhan claims to have a “better solution” for counting records in a recordset.

It is true that the Recordset object, created by the Open or Execute functions of the ADODB object, has a RecordCount property that reports ‘-1,’ if the Recordset has been opened with a forward-only cursor (default). However, you can easily get the proper count using either of the two methods written below. You don’t have to loop through all records and increment a variable, which can be a performance bottleneck. If your recordset has a large number of rows returned, then there’s no reason to iterate through all of them.

The two methods are named Method1 and Method2. With Method1, you can use adUseClient to get the recordcount (utilizing disconnected recordset):

 Private sub Method1          Dim objRecordset As New ADODB.Recordset         Dim RecordCount As Long         Dim objMyConn As New ADODB.Connection         RecordCount = 0             'IMPL Replace with your specific string like "Provider=SQLOLEDB;Data           ' Source=SQLserver;Initial Catalog=yourdb;User Id=sa; Password=;"         objMyConn.ConnectionString = "Your connection string"             ' Added line for disconnected recordset         objMyConn.CursorLocation = adUseClient             objMyConn.Open 
        Set objRecordset = objMyConn.Execute("select * from memberpolicy")         'IMPL Not required         'While Not objRecordset.EOF         'RecordCount = RecordCount + 1         'Debug.Print RecordCount         'objRecordset.MoveNext         'Wend         Debug.Print objRecordset.RecordCount 
end sub

Method2 uses the Movelast method on the recordset instead of looping through:

 Private sub  Method2          Dim objRecordset As New ADODB.Recordset         Dim RecordCount As Long         Dim objMyConn As New ADODB.Connection         RecordCount = 0             'IMPL Replace with your specific string like "Provider=SQLOLEDB;Data           ' Source=SQLserver;Initial Catalog=yourdb;User Id=sa; Password=;"         objMyConn.ConnectionString = "Your connection string"                objMyConn.Open         Set objRecordset = objMyConn.Execute("select * from memberpolicy")         ' IMPL Not required         'While Not objRecordset.EOF         'RecordCount = RecordCount + 1         'Debug.Print RecordCount         'objRecordset.MoveNext         'Wend         ' Add this call instead of looping through the recordset         objRecordset.MoveLast         Debug.Print objRecordset.RecordCount 
end sub