PRB: DAO Recordset RecordCount Incorrect When Based on Text File
ID: Q242478
|
The information in this article applies to:
-
Microsoft Visual Basic Professional and Enterprise Editions for Windows, versions 5.0, 6.0
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 File |
Actual Rows |
3.51 RecordCount* |
|---|
| Tab-delimited |
17 |
22 |
| CSV |
20 |
27 |
| Semicolon delimited |
29 |
42 |
| Fixed Width |
30 |
32 |
| Fixed Width |
60 |
65 |
| Semicolon delimited** |
3 |
3 |
*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 this to be a bug in the Microsoft products listed
at the beginning of this article.
MORE INFORMATION
Steps to Reproduce the Problem
- Create a standard exe project in Visual Basic.
- Choose References from the Project menu and select the Microsoft DAO Library.
- 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
- 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.
Additional query words:
Keywords : kbDAO kbDAO350 kbVBp500 kbVBp600 kbGrpVBDB kbDSupport kbDAO360
Version : WINDOWS:5.0,6.0
Platform : WINDOWS
Issue type : kbprb