Record Count

Given its name, you might assume that the RecordCount property actually indicates the number of records returned by a recordset. This assumption is not quite accurate.

Recordsets do not always return their entire dataset immediately; they can take quite some time to populate; the more rows they have to return, the longer they take. DAO returns a pointer to the recordset early, so you can get on with doing whatever it is you want to do, assuming that the latter rows will have been returned by the time you get to them.

The RecordCount property actually returns the number of rows that the recordset has accessed so far.

Of course, if you issue the MoveLast method before checking RecordCount, the recordset does not return until all the records have been accessed, in which case RecordCount then reports the correct number of rows. In fact, that's how you get an accurate record count, by issuing a MoveLast, followed by checking the RecordCount property.

Set rst = dbs.OpenRecordset("SELECT * FROM Table1", dbOpenDynaset) If rst.AbsolutePosition > -1 Then 'Move to the last row rst.MoveLast

'Now get the count lngCount = rst.RecordCount

'If you want, you can now move again rst.MoveFirst

'Continue processing

End If

RecordCount always returns the correct number of rows for table-type recordsets.

In a single-user environment, once RecordCount has the correct number of rows, it keeps synch when rows are added or deleted. In a multiuser environment, however, things get a little trickier.

For example, if two users are modifying records in the same table, additions or deletions made by one user will not be reflected on the other user's computer until they access that record (or the place where a deleted record used to be). To ensure you have an accurate record count in a multiuser environment, you should:

□ Use the recordset's Requery method (see the note below); or

□ Use the MoveLast method again.

The Requery method is not supported on table-type recordsets. The RecordCount property for snapshot-type recordsets will not change once it has been created, and it certainly won't reflect changes made by other users.

0 0

Post a comment