Microsoft KB Archive/117167

= ACC: RecordCount Property Incorrect Using OpenTable Method =

Article ID: 117167

Article Last Modified on 5/6/2003

-

APPLIES TO


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

-



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:

 Start Microsoft Access and open the sample database NWIND.MDB.  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  From the View menu, choose Immediate Window. 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
 Start Microsoft Access and open the sample database NWIND.MDB.</li>  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 </li> From the View menu, choose Immediate Window.</li> 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.</li></ol>

Keywords: kbprogramming kbprb KB117167

-

[mailto:TECHNET@MICROSOFT.COM Send feedback to Microsoft]

© Microsoft Corporation. All rights reserved.