VBA/Excel/Access/Word/Access/Recordset Count

Counting the Number of Returned Records

 
Sub CountRecords()
   Dim conn As ADODB.Connection
   Dim myRecordset As ADODB.Recordset
   Dim myarray As Variant
   Dim returnedRows As Integer
   Dim r As Integer "record counter
   Dim f As Integer "field counter
   Set conn = CurrentProject.Connection
   Set myRecordset = New ADODB.Recordset
   myRecordset.Open "SELECT * FROM Employees", conn, adOpenForwardOnly, adLockReadOnly, adCmdText
   myarray = myRecordset.GetRows()
   returnedRows = UBound(myarray, 2) + 1
   For r = 0 To UBound(myarray, 2)
      Debug.Print "Record " & r + 1
      For f = 0 To UBound(myarray, 1)
         Debug.Print "  "; myRecordset.Fields(f).Name & " = " & myarray(f, r)
      Next f
   Next r
   myRecordset.Close
   Set myRecordset = Nothing
   conn.Close
   Set conn = Nothing
End Sub

Get the Number of Returned Records

 
Sub GetCount()
   Dim conn As ADODB.Connection
   Dim myRecordset As ADODB.Recordset
   Dim myarray As Variant
   Dim returnedRows As Integer
   Dim r As Integer "record counter
   Dim f As Integer "field counter
   Set conn = CurrentProject.Connection
   Set myRecordset = New ADODB.Recordset
   myRecordset.Open "SELECT * FROM Employees", _
      conn, adOpenForwardOnly, adLockReadOnly, adCmdText
   myarray = myRecordset.GetRows()
   returnedRows = UBound(myarray, 2) + 1
   MsgBox "Total number of records: " & returnedRows
   myRecordset.Close
   Set myRecordset = Nothing
   conn.Close
   Set conn = Nothing
End Sub