Microsoft KB Archive/105129

= ACC: Record Count Incorrect with Multiuser Tables =

Article ID: 105129

Article Last Modified on 10/11/2006

-

APPLIES TO


 * Microsoft Access 1.0 Standard Edition
 * Microsoft Access 1.1 Standard Edition
 * Microsoft Access 2.0 Standard Edition
 * Microsoft Access 95 Standard Edition

-



This article was previously published under Q105129



Moderate: Requires basic macro, coding, and interoperability skills.



SYMPTOMS
When multiple users access the same table and delete records from it, the record count does not always accurately reflect the actual number of records in the table.

For example, say two different users access the same table. User 1 prints a count of the number of records in the table, deletes the last record, and then closes the table. User 2 then opens the same table and prints a count of the number of records in the table. The record count may be the same for the two users even though the record count for User 2 should be one less than for User 1.



CAUSE
Pages are not removed from the page cache when a table is closed. When a table is reopened, it is possible for out-of-date pages to be present in the page cache, which can lead to inaccurate record counts.



RESOLUTION
Although the RecordCount property of a table returns the approximate number of records in the table, the RecordCount property of a snapshot returns the actual number of records in the snapshot. You can use the following user-defined sample function to return a table's record count by creating a snapshot of the table: Function ReturnRecordCount (mytablename As String)

'Define variables. Dim mytable As Table, mydb As Database Dim mysnapshot As Snapshot

'Initialize variables. Set mydb = CurrentDB Set mytable = mydb.OpenTable(mytablename) 'Create a snapshot based on the table. Set mysnapshot = mytable.CreateSnapshot

mysnapshot.MoveLast myrecordcount = mysnapshot.recordcount

mysnapshot.Close mytable.Close mydb.Close

ReturnRecordCount = myrecordcount

End Function

