ACC: RecordCount Property Incorrect Using OpenTable Method (117167)


The information in this article applies to:

  • Microsoft Access 1.0
  • Microsoft Access 1.1
  • Microsoft Access 2.0
This article was previously published under Q117167

SYMPTOMS

Advanced: Requires expert coding, interoperability, and multiuser skills.

The RecordCount property of a table may be incorrect.

CAUSE

If you open a table, begin a transaction, add a new record, and then roll back the transaction, the record count returned on the table may be incorrect because the previous value of the RecordCount property is not restored. This behavior can occur when you use the OpenTable method, or when you use the DB_OPEN_TABLE Type property setting with the OpenRecordset method on a TableDef object using data access objects (DAO).

RESOLUTION

To retrieve the correct record count, create a dynaset based on the table, use the MoveLast method to move to the last record in the dynaset, and print the dynaset's RecordCount property. The following example demonstrates how to do this:
  1. Start Microsoft Access and open the sample database NWIND.MDB.
  2. Open a new module and type the following sample code.

    NOTE: In the following sample code, an underscore (_) at the end of a line is used as a line-continuation character. Remove the underscore from the end of the line when re-creating this code in Access Basic. 
    
      ' ****************************************************************
      ' Declarations section of the module
      ' ****************************************************************

      Option Compare Database
      Option Explicit

      ' ****************************************************************
      ' The RightCount() function creates a dynaset based on the
      ' Employees table, prints the table's record count, starts a new
      ' transaction, adds a record, rolls back the transaction, and then
      ' prints the dynaset's record count.
      ' ****************************************************************

      Function RightCount ()
         Dim db As Database
         Dim MyDyna As Dynaset
         Set db = CurrentDB()
         Set MyDyna = db.CreateDynaset("Employees")
         MyDyna.MoveLast
         Debug.Print "BEFORE Transaction: Employee Record Count = " & _
                                        MyDyna.recordcount
         BeginTrans
             MyDyna.AddNew
             MyDyna![Last Name] = "Doe"
             MyDyna![First Name] = "John"
             MyDyna.Update
         Rollback
         MyDyna.Close
         Set MyDyna = db.CreateDynaset("Employees")
         MyDyna.MoveLast
         Debug.Print "AFTER Transaction: Employee Record Count = " & _
                                        MyDyna.recordcount
         MyDyna.Close
      End Function
  3. From the View menu, choose Immediate Window.
  4. In the Immediate window, type the following line, and then press ENTER:

    ?RightCount()

    Compare the record count returned after the transaction is rolled back with the actual number of records in the table and note that they are the same.

STATUS

This behavior is by design.

MORE INFORMATION

When you use the OpenTable method, or the DB_OPEN_TABLE Type property setting with the OpenRecordset method, the base table is opened directly. The RecordCount property retrieved from the table is only an approximate value, and is not always accurate. When a transaction is rolled back, the previous value of the RecordCount property is not restored.

Steps to Reproduce Behavior

  1. Start Microsoft Access and open the sample database NWIND.MDB.
  2. Open a new module and type the following sample code.

    NOTE: In the following sample code, an underscore (_) at the end of a line is used as a line-continuation character. Remove the underscore from the end of the line when re-creating this code in Access Basic. 
          ' ****************************************************************
      ' Declarations section of the module
      ' ****************************************************************

      Option Compare Database
      Option Explicit

      ' ****************************************************************
      ' The WrongCount() function opens the Employees table, prints the
      ' record count, starts a new transaction, adds a record, rolls
      ' back the transaction, and then prints the table's record count.
      ' ****************************************************************

      Function WrongCount ()
         Dim db As Database
         Dim MyTable As Table
         Set db = CurrentDB()
         Set MyTable = db.OpenTable("Employees")
         MyTable.MoveLast
         Debug.Print "BEFORE: Employee Record Count = " & _
                                       MyTable.RecordCount
         BeginTrans
             MyTable.AddNew
             MyTable![Last Name] = "Doe"
             MyTable![First Name] = "John"
             MyTable.Update
         Rollback
         MyTable.Close
         Set MyTable = db.OpenTable("Employees")
         MyTable.MoveLast
         Debug.Print "AFTER: Employee Record Count = " & _
                                       MyTable.RecordCount
         MyTable.Close
      End Function
  3. From the View menu, choose Immediate Window.
  4. Type the following line in the Immediate window, and then press ENTER:

    ?WrongCount()

    Compare the record count returned after the transaction is rolled back with the actual number of records in the table and note that they are different. The record count that is returned after the transaction is rolled back is not correct.
Modification Type:MajorLast Reviewed:5/6/2003
Keywords:kbprb kbprogramming KB117167
©2002 Microsoft Corporation. All rights reserved.