PRB: Using RecordCount with VB Dynasets, Snapshots, and Tables

ID: Q109053

The information in this article applies to:

Microsoft Visual Basic Professional Edition for Windows, version 3.0

SYMPTOMS

The RecordCount property, when used with a Dynaset or Snapshot, can sometimes return an incorrect number of records. This applies to the Microsoft Access database engine that is built into Visual Basic version 3.0.

CAUSE

For Dynasets and Snapshots, the RecordCount property does not automatically return the number of records that exist in the recordset. RecordCount returns the number of records accessed. If you don't do a MoveLast method immediately before checking the record count, you will get an incorrect, smaller count.

RESOLUTION

To determine the number of records in a Dynaset or Snapshot, use the MoveLast method before checking the RecordCount property. This requirement is by design.

NOTE: If you add or delete records to a table within a transaction, and then roll back the transaction, the value of the RecordCount property is not adjusted accordingly.

STATUS

This behavior is by design because otherwise, Visual Basic would have to do an implicit MoveLast. This would be very slow with large record sets and especially with remote databases, so the decision is left up to the programmer.

MORE INFORMATION

NOTE: If your data is displayed in a Grid control, the RecordCount will be one greater than the last line number in the grid because the grid starts at zero.

How to Count Records in Whole Table Quickly by Using ListTables Method

The following steps count the number of records in a table without opening the table:

Create a Snapshot of the Tabledefs collection using the ListTables method. The ListTables method creates a Snapshot with one record for each Table or QueryDef in a specified database.

Examine the RecordCount field of the record corresponding to your table in that Snapshot. That RecordCount field is not a property; it is a field in a record in a Snapshot that is returned by the ListTables method.

The following sample program performs the above two steps:


   Sub Form_Load ()
   Const DB_TABLE = 1  ' Constant taken from DATACONS.TXT file.
   Dim db As Database
   Dim snap As Snapshot
   Set db = OpenDatabase("C:\VB3\BIBLIO.MDB")
   Set snap = db.ListTables()  ' Copy Table information to Snapshot.
   Do While Not snap.EOF
      If snap("TableType") = DB_TABLE Then
      'Enter the Table name for which you want a record count:
         If snap("Name") = "Authors" Then
            MyRecordCount = snap("RecordCount")
         End If
      End If
      snap.MoveNext  ' Move to next record.
   Loop
   snap.Close
   form1.Show  ' Must Show form1 in Load event before Print works.
   Print MyRecordCount
   End Sub

How to Count Records in Snapshot, Dynaset, or Data Control

NOTE: A MoveLast will be slow on a large table or set. Only use the method in the section below for counting subsets of the table. To count the number of records that comprise the whole table, use the ListTables Method given above.

If you are using a Snapshot, Dynaset, or the data control, you can count the records in the current recordset by first doing a MoveLast. Then use the RecordCount property. This count is only accurate for that instant, because another user could be simultaneously adding or deleting records to the underlying table. By design, a data control is linked to a Dynaset.

A MoveLast on a recordset variable (a Dynaset or Snapshot) is faster than MoveLast on a data control. You can create a separate Snapshot variable of your data control's recordset and invoke a MoveLast on that Snapshot.

The following program shows how to use MoveLast and the RecordCount property to count the number of records in a Dynaset.


   Sub Form_Load ()
      Dim MyDB As Database, MyDyna As Dynaset
      Set MyDB = OpenDatabase("C:\VB3\BIBLIO.MDB")
      Set MyDyna = MyDB.CreateDynaset("Authors")
      MyDyna.MoveLast
      MyRecordCount = MyDyna.RecordCount
      MyDyna.Close
      form1.Show
      Print MyRecordCount
   End Sub

Records Must Be Properly Added Before They Are Counted

The Addnew method allocates space for a new record in your database. You then add data to the various table fields in the new record. You then do an Update method to write the new record to the table.

The Update method saves the contents of the copy buffer to a specified Table or Dynaset. Use Update to save any changes to a record after using Edit or AddNew. With a data control, if an Edit or AddNew operation is pending when you move to another record or close the recordset, Update is automatically invoked if not stopped during the Validate event.

NOTE: In the Professional Edition, if you are not using a data control and move to another record or close the recordset while an Edit or AddNew operation is pending, any existing changes will be lost and no error will occur.

Loops and RecordCount

Use EOF instead of RecordCount in loops. For example, don't use this:


   For i = 1 to ds.RecordCount ' Bad code
   ...
   Next

Use the following instead:


   Do Until ds.EOF             ' Good code
   ...
   Loop

REFERENCES

"Microsoft Visual Basic Version 3.0: Professional Features Book 2: Data Access Guide," page 24 (RecordCount field) and pages 61-63 (RecordCount property).

Additional query words: 3.00

Keywords : kbcode
Version : 3.00
Platform : WINDOWS
Issue type :