PRB: Using RecordCount with VB Dynasets, Snapshots, and Tables (109053)

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:

1. 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.
2. 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