Counting the Number of 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