Recordcount and DAO

[Delta34]

Goal: To enter data into field N2 while the program counts the records and automatically enters the current number into N1.

Problem: I start the program and entered a single piece of data, then exit and restart and enter another single piece of data. When I look in the database the first entry is 0 and the second is 7.
I've used debug and verified the .recordcount is at seven immediately after "with data2.recordset" is executed, in other words no other module could be contributing to this problem. The only time it counts correctly is the first time through weather it's one entry or ten as a matter of fact after ten entry's I got a record count of 0 - 9. stop/restart and entered one piece of data and the .recordcount jumped to seventeen!!

Question: WHAT'S THE DEAL?!?!?!

Below is the code!!

Private Sub Form_Activate()
TotalRecords% = 0
With Data2.Recordset
If .BOF = True And .EOF = True Then GoTo Quit
.MoveLast
TotalRecords% = .RecordCount
.MoveFirst
TotalRecords% = .RecordCount
End With
Quit:
End Sub

[Lethal]

If dao is anything like ado, not sure, use ado exclusively, there should be a reposition event for the data control

[Vit]

Are you using autonumber field as counter?

[Delta34]

I’ m using MS works for the spreadsheet, and saving as a CSV file, so I will check to see if AutoNumber is automatically turned on. Also I have access to a computer with excel that I may try to run the program on.

[Delta34]

The information below simply states that there is a known problem when trying to do a record count of text (ie csv files,Semicolon delimited etc.) and DAO 3.5X

From Microsoft:
PRB: DAO Recordset RecordCount Incorrect When Based on Text File

--------------------------------------------------------------------------------
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
Issue type : kbprb
Technology :



Last Reviewed: November 11, 1999
© 2000 Microsoft Corporation. All rights reserved. Terms of Use.