Recordcount problem with DAO Recordsets

[Pete]

I am working with Access 2000 Databases in a Windows 2000
environment. This week, VBA code that previously worked
began acting strangely.

The only network upgrades that we have noticed are the
Internet Explorer 6.0.2800 patch and our version of VB has
upgraded from VB5 to VB6.

Specifically, Recordsets created in VBA code that have
multiple records only return a recordcount value of 1.
When the fields are referenced, the values are from
records in the middle, not the beginning or end of the
recordset. The recordset returns an accurate recordcount
only if MoveLast or an EOF loop is used. I am uncertain of
the implications of this, but recoding/checking our
existing databases would prove to be a challenge.

Here are the VBA References we are using:

Visual Basic For Applications
Microsoft Access 9.0 Object Library
Microsoft DAO 3.6 Object Library
OLE Automation

Here is some sample code that once displayed a msgbox "3"
and now displays a msgbox "1"

Private Sub Command0_Click()
Dim rst As Recordset
Set rst = CurrentDb.OpenRecordset("SELECT * FROM Tub",
dbOpenDynaset)
MsgBox rst.RecordCount
End Sub

Has anyone else encountered this problem ?
Thanks in Advance.

 

[Allen Browne]

This behavior is typical for DAO recordsets of type dbOpenDynaset.

The count represents the number of records accessed so far. Immediately
after opening the recordset, this will be zero (if there are no records) or
1 (if there are any records). A MoveLast is the correct approach if you need
the real count.

dbOpenTable recordsets do show the correct count, but you cannot do that
with a SQL statement (as in your example) nor with an attached table.

 

[Marshall Barton]

I am working with Access 2000 Databases in a Windows 2000
environment. This week, VBA code that previously worked
began acting strangely.

The only network upgrades that we have noticed are the
Internet Explorer 6.0.2800 patch and our version of VB has
upgraded from VB5 to VB6.

Specifically, Recordsets created in VBA code that have
multiple records only return a recordcount value of 1.
When the fields are referenced, the values are from
records in the middle, not the beginning or end of the
recordset. The recordset returns an accurate recordcount
only if MoveLast or an EOF loop is used. I am uncertain of
the implications of this, but recoding/checking our
existing databases would prove to be a challenge.

Here are the VBA References we are using:

Visual Basic For Applications
Microsoft Access 9.0 Object Library
Microsoft DAO 3.6 Object Library
OLE Automation

Here is some sample code that once displayed a msgbox "3"
and now displays a msgbox "1"

Private Sub Command0_Click()
Dim rst As Recordset
Set rst = CurrentDb.OpenRecordset("SELECT * FROM Tub",
dbOpenDynaset)
MsgBox rst.RecordCount
End Sub

This has always been the situation, all types of recordsets,
except dbOpenTable, only return a RecordCount of the records
accessed since the recordset was opened. Using MoveLast is
the standard way of accessing all the records so that
RecordCount returns the total number of records in the
recordset.

Without MoveLast, RecordCount does not always return a 1.
It's more a timing issue than anything else. If the table
has a moderate number of records and resides on a local
drive, then a newly opened recordset's RecordCount will
frequently (but not always) return the total number of
records, while a large table on a network server will
commonly return less that the total number of records (often
just the 1 you're seeing). You can rely on a newly opened
recordset's RecordCount being >0 if there are any records in
the recorset and =0 if the recordset returns no records.

 

[Guest]

-----Original Message-----
This behavior is typical for DAO recordsets of type dbOpenDynaset.

The count represents the number of records accessed so far. Immediately
after opening the recordset, this will be zero (if there are no records) or
1 (if there are any records). A MoveLast is the correct approach if you need
the real count.

dbOpenTable recordsets do show the correct count, but you cannot do that
with a SQL statement (as in your example) nor with an attached table.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.




.
Thanks.