ACC: RecordCount Property Incorrect Using OpenTable Method

• Microsoft Access versions 1.0, 1.1, 2.0

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.

Additional query words:

Keywords          : kbprg PgmObj 
Version           : WINDOWS:1.0,1.1,2.0
Platform          : WINDOWS 
Issue type        : kbprb 

Last Reviewed: July 22, 1999