ACC: RecordCount Property Returns Incorrect Number of Records

MORE INFORMATION

Steps to Reproduce Behavior

1. Open the sample database Northwind.mdb (or NWIND.MDB in versions 1.x and 2.0).
2. Create a module and type the following line in the Declarations section if it is not already there:

   Option Explicit

3. Type the following procedures:
   
   In Microsoft Access 7.0 and 97:

        '===========================================================
         ' The following function, MyWrongRecordCount(), demonstrates
         ' the incorrect way to use the RecordCount property to count
         ' records in a dynaset.
         '===========================================================
         Function MyWrongRecordCount ()
            Dim MyDB As Database
            Dim MyRS as Recordset
            Set MyDB = CurrentDB()
            Set MyRS = MyDb.OpenRecordset("Customers", dbOpenDynaset)
            MyWrongRecordCount = MyRS.RecordCount
            MyRS.Close
         End Function
   
         '===========================================================
         ' The following function, MyRightRecordCount(), demonstrates
         ' the correct way to use the RecordCount property to count
         ' records in a dynaset.
         '===========================================================
         Function MyRightRecordCount ()
            Dim MyDB As Database
            Dim MyRS as Recordset
            Set MyDB = CurrentDB()
            Set MyRS = MyDb.OpenRecordset("Customers", dbOpenDynaset)
            MyRS.MoveLast
            MyRightRecordCount = MyRS.RecordCount
            MyRS.Close
         End Function
   
      In Microsoft Access 2.0:
   
         '===========================================================
         ' The following function, MyWrongRecordCount(), demonstrates
         ' the incorrect way to use the RecordCount property to count
         ' records in a dynaset.
         '===========================================================
         Function MyWrongRecordCount ()
            Dim MyDB As Database
            Dim MyRS as Recordset
            Set MyDB = CurrentDB()
            Set MyRS = MyDb.OpenRecordset("Customers", DB_OPEN_DYNASET)
            MyWrongRecordCount = MyRS.RecordCount
            MyRS.Close
         End Function
   
         '===========================================================
         ' The following function, MyRightRecordCount(), demonstrates
         ' the correct way to use the RecordCount property to count
         ' records in a dynaset.
         '===========================================================
         Function MyRightRecordCount ()
            Dim MyDB As Database
            Dim MyRS as Recordset
            Set MyDB = CurrentDB()
            Set MyRS = MyDb.OpenRecordset("Customers", DB_OPEN_DYNASET)
            MyRS.MoveLast
            MyRightRecordCount = MyRS.RecordCount
            MyRS.Close
         End Function
   
      In Microsoft Access 1.x:
   
         '===========================================================
         ' The following function, MyWrongRecordCount(), demonstrates
         ' the incorrect way to use the RecordCount property to count
         ' records in a dynaset.
         '===========================================================
         Function MyWrongRecordCount ()
            Dim MyDB As Database
            Dim MyRS as Dynaset
            Set MyDB = CurrentDB()
            Set MyRS = MyDb.CreateDynaset("Customers")
            MyWrongRecordCount = MyRS.RecordCount
            MyRS.Close
         End Function
   
         '===========================================================
         ' The following function, MyRightRecordCount(), demonstrates
         ' the correct way to use the RecordCount property to count
         ' records in a dynaset.
         '===========================================================
         Function MyRightRecordCount ()
            Dim MyDB As Database
            Dim MyRS as Dynaset
            Set MyDB = CurrentDB()
            Set MyRS = MyDb.CreateDynaset("Customers")
            MyRS.MoveLast
            MyRightRecordCount = MyRS.RecordCount
            MyRS.Close
         End Function
   						

4. To test these functions, type the following lines in the Debug window (or Immediate window in versions 1.x and 2.0), and then press ENTER after you've entered each one:

         ?MyWrongRecordCount()
   						

   Note that the function returns 1.

         ?MyRightRecordCount()
   						

   Note that the function returns the correct number of records in the Customers table.