ACC: Record Count Incorrect with Multiuser Tables
ID: Q105129
|
The information in this article applies to:
-
Microsoft Access versions 1.0, 1.1, 2.0, 7.0
SYMPTOMS
Moderate: Requires basic macro, coding, and interoperability skills.
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
REFERENCES
For more information about snapshots, search for "snapshot," and then
"Snapshot Object--Summary" using the Microsoft Access Help menu.
For more information about the RecordCount property, search for
"RecordCount," and then "RecordCount Property" using the Microsoft Access
Help menu.
Additional query words:
multiuser
Keywords : kbusage kbdta AccCon MdlRcd
Version : WINDOWS:1.0,1.1,2.0,7.0
Platform : WINDOWS
Issue type : kbprb