Article ID: 109053
Article Last Modified on 10/20/2003
APPLIES TO
- Microsoft Visual Basic 3.0 Professional Edition
This article was previously published under Q109053
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: kbprb KB109053
