code to get records does not return the same records as a direct qyery on the data

  • Thread starter Savvoulidis Iordanis
  • Start date
S

Savvoulidis Iordanis

Hello there.
My problem (this time) is that the following code ( just as i write it
below), does not return the same records as if it was a query


dim rs as dao.database

Set rs = CurrentDb.OpenRecordset("select * from print_positions where
report_name = ""rpt_1""")
MsgBox rs.RecordCount ' returns only the 1st record

I tried that directly as a query:
select * from print_positions where report_name = "rpt_1"
and I got all the rows i wanted OK.

Why ?

PS. I use Access 2000 with all the latest office/dao updates, and the
program is linked to the data file
 
J

John Vinson

Hello there.
My problem (this time) is that the following code ( just as i write it
below), does not return the same records as if it was a query


dim rs as dao.database

Set rs = CurrentDb.OpenRecordset("select * from print_positions where
report_name = ""rpt_1""")
MsgBox rs.RecordCount ' returns only the 1st record

Actually it returns the entire recordset - but it returns control to
the code immediately, and then populates the recordset in the
background. The RecordCount property will not be accurate until you've
forced this (possibly slow) process to complete. If you need a count
of the records, put

rs.MoveLast

between the OpenRecordset and the MsgBox line.

John W. Vinson[MVP]
 
S

Savvoulidis Iordanis

It worked. But isn't it slow to get to the last record and then back to the
first one, for every openrecordset command?
Is it possible to know whether this OpenRecordset behavior happens?
 
D

Dirk Goldgar

Savvoulidis Iordanis said:
It worked. But isn't it slow to get to the last record and then back
to the first one, for every openrecordset command?

If all you want to know is the count of records meeting your criteria,
then you can query that directly:

Dim rs As DAO.Recordset

Set rs = CurrentDb.OpenRecordset( _
"SELECT Count(*) As MatchCount FROM print_positions " & _
"WHERE report_name = ""rpt_1""")

MsgBox rs!MatchCount
Is it possible to know whether this OpenRecordset behavior happens?

Sorry, I don't know what you mean by that question.
 
S

Savvoulidis Iordanis

I mean if we can predict if Access will give program control immediately
after the 1st record retrieval, or wait for the entire recordset to be
filled
 
D

Dirk Goldgar

Savvoulidis Iordanis said:
I mean if we can predict if Access will give program control
immediately after the 1st record retrieval, or wait for the entire
recordset to be filled

The DAO OpenRecordset method will always return control as soon as the
first record (if any) is available. Unless the recordset is a
table-type recordset (which can only be the case if you are opening a
local table, and not a query or a linked table), the recordset's
RecordCount property will not reflect the actual number of records in
the recordset at that point. The RecordCount property won't be accurate
until all recoirds in the recordset have been visited.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top