PRB: DAO Recordset RecordCount Incorrect When Based on Text File (242478)


The information in this article applies to:

  • Microsoft Visual Basic Professional Edition for Windows 5.0
  • Microsoft Visual Basic Professional Edition for Windows 6.0
  • Microsoft Visual Basic Enterprise Edition for Windows 5.0
  • Microsoft Visual Basic Enterprise Edition for Windows 6.0
This article was previously published under Q242478

SYMPTOMS

When using the Jet Text ISAM Driver to open a Recordset based on a delimited or fixed width text file, the count returned from the RecordCount property is incorrect.

DAO 3.51 and DAO 3.6 may yield different results, however both are incorrect.

Testing text files with a varying number of rows and types of delimiters yielded the following results:


Type of FileActual Rows3.51 RecordCount*
Tab-delimited1722
CSV2027
Semicolon delimited2942
Fixed Width3032
Fixed Width6065
Semicolon delimited**33


*The RecordCount returned when using DAO 3.6 are different from the values earlier.
**A file with a very small number of rows may return the correct record count.

RESOLUTION

If you require an accurate record count of the Recordset, add a user defined counter within a loop, incrementing it by one as you loop through the entire Recordset.

STATUS

Microsoft has confirmed that this is a bug in the Microsoft products that are listed at the beginning of this article.

MORE INFORMATION

Steps to Reproduce the Problem

  1. Create a standard exe project in Visual Basic.
  2. Choose References from the Project menu and select the Microsoft DAO Library.
  3. Paste the following code into the form code window in the form load event. 
    Dim dbstext As DAO.Database
Dim rst1 As DAO.Recordset
Dim lngRecCount As Long

'C:\MyDir\TextFileDir is a directory where a tab delimited file named Orders.txt resides.
'Orders.txt is a comma delimited file created from exporting the Orders table from Northwind.mdb.
Set dbstext = OpenDatabase("C:\MyDir\TextRecCount", False, True, "Text;")

Set rst1 = dbstext.OpenRecordset("Orders")

rst1.MoveLast
MsgBox rst1.RecordCount

rst1.MoveFirst

lngRecCount = 0

rst1.MoveFirst

Do Until rst1.EOF
   lngRecCount = lngRecCount + 1
   rst1.MoveNext
Loop
MsgBox lngRecCount
  4. Run the code and note the different count in the two message boxes.
If you are currently relying on the value of the RecordCount property to control the number of times a loop executes, use the Recordset's BOF and EOF Properties to determine when the Recordset has reached the first or last record. See the loop in the earlier example.
Modification Type:MajorLast Reviewed:1/11/2001
Keywords:kbDSupport kbprb KB242478
©2002 Microsoft Corporation. All rights reserved.