RecordCount

Has this happened to anyone else? I have a table in a database that has about 10 records. In a sub I setup I create a recordset and set it to this table. What I want to do is save the number of records in a variable. When I use rst.RecordCount I get 1. But I know there's more. How's this happening?

Depending on how you open the Recordset, the record pointer usually sits on the first record. That is why the recordcount is 1. To get the correct count, you'll need to execute the following code

Dim numRecords

rst.movelast
numRecords = rst.recordcount
rst.MoveFirst

Keep in mind that moving to the last record in large recordsets can be slow. So use this sparingly.

We have a function, GetRecordCount, shown below that we use whenever we need to get the recordcount of a recordset.


'------
Public Function GetRecordCount(pasRst As DAO.Recordset) As Long
'* Cursors: adOpenKeyset & adOpenDynamic
On Error GoTo Err_Proc


If pasRst.BOF = True And pasRst.EOF = True Then
GetRecordCount = 0
Else
pasRst.MoveLast
GetRecordCount = pasRst.RecordCount
pasRst.MoveFirst
End If

Exit Function

Err_Proc:
msgbox Error
Exit Function

End Function

----------------
Please note this is for a DAO recordset. You could also do one for ADO recordsets.

If you have any questions, please let me know.

What about...

MsgBox Me.RecordsetClone.RecordCount

For a table-type recordset object, the MoveLast operation shouldn't be necessary

Is this table local or linked? If it is linked, (and large) you would need to do the MoveLast in order to bring over the entire recordset. Access will not populate the whole recordset until it deems it necessary.

Rather than passing the entire recordset, use a totals query.

Select Count(*) as CountOfRecords
From yourTableOrQuery;

When run against a table (even an ODBC linked table), Jet only needs to look at the table statistics to determine the count. When run against a query, the query needs to be run and the rows counted so no savings occurs.