DAO vs ADO - different SQL results

[boyshey]

can anyone tell me why i get differing recordcounts from the two
examples below:

Dim rstStats as New ADODB.Recordset
rstStats.Open "Select * From [Party Input]",
CurrentProject.Connection, adOpenKeySet, adLockOptimistic
'RecordCount = 5

AND

Dim rstStats as DAO.Recordset
Set rstStats = dbsStats.OpenRecordset("Select * from [Party Input]")
'RecordCount = 1

Party Input is a query with a simple inner join. When I run the query
in Access I get 5 results. Why is the DAO recordcount 5?

 

[pietlinden]

can anyone tell me why i get differing recordcounts from the two
examples below:

Dim rstStats as New ADODB.Recordset
rstStats.Open "Select * From [Party Input]",
CurrentProject.Connection, adOpenKeySet, adLockOptimistic
'RecordCount = 5

AND

Dim rstStats as DAO.Recordset
Set rstStats = dbsStats.OpenRecordset("Select * from [Party Input]")
'RecordCount = 1

Party Input is a query with a simple inner join. When I run the query
in Access I get 5 results. Why is the DAO recordcount 5?

you forgot

rstStats.MoveLast

in the DAO code. Just the way DAO works...

 

[Allen Browne]

pietlinden is correct.

If you want to read further, see:
VBA Traps: Working with Recordsets
at:
http://allenbrowne.com/ser-29.html#RecordCount_without_MoveLast

It's the 4th trap listed in that article.

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

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

can anyone tell me why i get differing recordcounts from the two
examples below:

Dim rstStats as New ADODB.Recordset
rstStats.Open "Select * From [Party Input]",
CurrentProject.Connection, adOpenKeySet, adLockOptimistic
'RecordCount = 5

AND

Dim rstStats as DAO.Recordset
Set rstStats = dbsStats.OpenRecordset("Select * from [Party Input]")
'RecordCount = 1

Party Input is a query with a simple inner join. When I run the query
in Access I get 5 results. Why is the DAO recordcount 5?

you forgot

rstStats.MoveLast

in the DAO code. Just the way DAO works...

 

[boyshey]

cheers.

 

[david]

Servers don't return a recordcount unless you ask for it.
There are two ways to get a recordcount: (1) run a separate
query that requests the record count, (2) Get all the records
or keys, then count them. I don't know which method
adOpenKeySet, adLockOptimistic
is using, but it is documented somewhere, or you could
examine a server trace to see what is happening. For small
recordset (like your 5 records) it is convenient to get all the
keys and count them. For large recordsets (1000s) it may
make more sense to get a count from the server (which gets
a count from the index if it is well indexed). You need to
learn about the behaviour of the different connection methods
to choose which is the most appropriate, and if the method
you choose will work for large recordsets.

(david)