ACC: Record Count Incorrect with Multiuser Tables

Last reviewed: August 29, 1997
Article 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 PgmHowTo MdlRcd
Version : 1.0 1.1 2.0 7.0
Platform : WINDOWS
Hardware : x86
Issue type : kbprb
Solution Type : kbcode


THE INFORMATION PROVIDED IN THE MICROSOFT KNOWLEDGE BASE IS PROVIDED "AS IS" WITHOUT WARRANTY OF ANY KIND. MICROSOFT DISCLAIMS ALL WARRANTIES, EITHER EXPRESS OR IMPLIED, INCLUDING THE WARRANTIES OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE. IN NO EVENT SHALL MICROSOFT CORPORATION OR ITS SUPPLIERS BE LIABLE FOR ANY DAMAGES WHATSOEVER INCLUDING DIRECT, INDIRECT, INCIDENTAL, CONSEQUENTIAL, LOSS OF BUSINESS PROFITS OR SPECIAL DAMAGES, EVEN IF MICROSOFT CORPORATION OR ITS SUPPLIERS HAVE BEEN ADVISED OF THE POSSIBILITY OF SUCH DAMAGES. SOME STATES DO NOT ALLOW THE EXCLUSION OR LIMITATION OF LIABILITY FOR CONSEQUENTIAL OR INCIDENTAL DAMAGES SO THE FOREGOING LIMITATION MAY NOT APPLY.

Last reviewed: August 29, 1997
© 1998 Microsoft Corporation. All rights reserved. Terms of Use.