In ADO, when I connect to an Access database I can get the number of
records in a query by using numrecs=rs.RecordCount.  When connecting
to Oracle, rs.RecordCount always returns 0.  Is there a way to find
out the number of records in an Oracle recordset?

Currently I'm sending another query first SELECT COUNT(*) FROM ...,
but this involves querying the database twice (and kills my
performance for complex queries).

---

Clarification of Question by ny_insomniac-ga on 11 Feb 2004 10:45 PST

I tried this but still get a -1 for recordcount.  I am using msdaora
as a provider on Oracle 9.

Set rs = New ADODB.Recordset
rs.Open sqltext, db, adOpenStatic, adLockReadOnly
rs.MoveLast
numrecs = rs.RecordCount

Unless something has changed, the RecordCount property does not work
properly with the Oracle driver using VB/ADO. You do indeed have to do
the separate COUNT query to get the record count.

- Hammer

I sometimes have the problem of recordsets returning a .RecordCount of
-1. To resolve it you do a .MoveLast. Perhaps this will work for you
too.

-Dave

The RecordCount property returns the number of records ACCESSED as
opposed to the number of records present in the recordset. To get the
number of records you must use the following code:

rs.MoveLast
numrecs=rs.RecordCount

The line rs.MoveLast moves to the last record in the recordset,
accessing all of the records in the recordset, so that the RecordSet
property will return the number of records in the recordset, rather
than 0 (0 was returned because no records had been accessed).

Hope this helps...

Sorry, typo in the above comment, it should read:

The line rs.MoveLast moves to the last record in the recordset,
accessing all of the records in the recordset, so that the RecordCount
property will return the number of records in the recordset, rather
than 0 (0 was returned because no records had been accessed).

From my research, the Oracle driver does not support the kind of
cursor needed to get an accurate recordcount. The symptom of this is
that it always returns -1. The MoveLast solution is for a different
kind of problem, which usually shows itself with a recordcount of 0.

To get a recordcount, you can either loop through your entire
recordset incrementing a counter, or you can do a separate COUNT
query. This is a problem with what the driver itself does and does not
support. I believe that this problem will only go away if they update
the driver to support more types of cursors.

- Hammer

ny_insomniac,

It's hard to get the whole story pieced together for the right
versions of everything, but I'm seeing some indication that using a
client-side cursor might get this feature for you. Have you tried
this? You would call rs.CursorLocation = adUseClient before rs.Open.

- Hammer

You have to use rs.reccount for count no. of records in recordset

The cursorlocation worked.  I am able to get my recordcount.
Thank you.

You're welcome. The indicators were too vague to post it as an
official answer without having all the versions of everything you were
running, but I'm glad I was able to point you in the right direction.

- Hammer