VBA/Excel/Access/Word/Access/Recordset Count
Counting the Number of Returned Records
<source lang="vb">
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
</source>
Get the Number of Returned Records
<source lang="vb">
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
</source>