The information in this article applies to:
- 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:
- 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.
- 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
- From the View menu, choose Immediate Window.
- 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.
|