recordset question

  • Thread starter Thread starter Richard
  • Start date Start date
R

Richard

Hi

Why is it that when I open a recordset with an SQL statement, the
recordcount is 1. Code:

set rs=db.openrecordset("select A.* from Table1 As A")
debug.print rs.recordcount

But when I paste the SQL onto a query, all records come up.

Thanks in advance

Richard
 
The default recordset type for a DAO recordset is a dynaset. This does not
retrieve all the records immediately in order to improve performance. This
means that the recordcount property is not guaranteed to equal the total
number of records retrieved.

In many cases all you want to know is whether any records have been
retrieved at all, in which case any non zero value is good enough. However,
if you do need to know the exact number of records then you have two
options, either:

- force all the records in the dyanset to be retrieved by moving to the last
record (rs.MoveLast) before you get the recordcount.
- use a table type recordset - this will only work with a local table and
you will have to change the code that opens the recordset to:

set rs = db.OpenRecordset("Table1",dbOpenTable)

(The dbOpenTable is not strictly necessary as tabletype recordset is the
default for local tables).
 
Thanks Andrew

Richard
Andrew Smith said:
The default recordset type for a DAO recordset is a dynaset. This does not
retrieve all the records immediately in order to improve performance. This
means that the recordcount property is not guaranteed to equal the total
number of records retrieved.

In many cases all you want to know is whether any records have been
retrieved at all, in which case any non zero value is good enough. However,
if you do need to know the exact number of records then you have two
options, either:

- force all the records in the dyanset to be retrieved by moving to the last
record (rs.MoveLast) before you get the recordcount.
- use a table type recordset - this will only work with a local table and
you will have to change the code that opens the recordset to:

set rs = db.OpenRecordset("Table1",dbOpenTable)

(The dbOpenTable is not strictly necessary as tabletype recordset is the
default for local tables).
Click to expand...
 
Back
Top