Counting the Number of Returned Records : Recordset Count « Access « VBA / Excel / Access / Word

1. VBA / Excel / Access / Word
2. Access
3. 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

 

Related examples in the same category