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

  • Thread starter Thread starter Savvoulidis Iordanis
  • Start date 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
 
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
Click to expand...

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]
 
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?
 
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?
Click to expand...

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?
Click to expand...

Sorry, I don't know what you mean by that question.
 
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
 
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
Click to expand...

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.
 
Back
Top