Recordcount in ADO

[Anand]

Hello, using A2k and new to ADO.

Am trying to convert a DAO based app to ADO. Looking up help for the
..recordcount property of the Recordset object, Access says the recordcount
property returns -1 when it is unable to determine the number of recrds.

Does a -1 imply that records are returned but access is not able to
determine how many? Or is it that Access simply does not know if any records
were returned - could be none were returned!

In most cases all I am testing only for existing of records - not really
concerned about how many.

TIA
Anand

 

[RDub]

If you are only want to test for the existence of records:

if not rs.bof or not rs.eof then
' Have records
else
' No records
end if

Rdub

 

[Dirk Goldgar]

Hello, using A2k and new to ADO.

Am trying to convert a DAO based app to ADO. Looking up help for the
.recordcount property of the Recordset object, Access says the recordcount
property returns -1 when it is unable to determine the number of recrds.

Does a -1 imply that records are returned but access is not able to
determine how many? Or is it that Access simply does not know if any
records
were returned - could be none were returned!

Frankly, I'm not sure what ADO does in practice. All the help file says is
that -1 is returned if the nature of the recordset is such that the number
of records can't be determined. So I would not depend on getting 0 if there
are no records.

In most cases all I am testing only for existing of records - not really
concerned about how many.

If you've just opened the recordset, and therefore haven't done any record
movement, you can test for .EOF:

rs.Open ...
If rs.EOF Then
' There are no records
Else
' There's at least one record.
End If

In the more general case, when you aren't sure that the recordset has just
been opened, you can test for both .EOF and .BOF:

If rs.EOF And rs.BOF Then
' There are no records
Else
' There's at least one record.
End If

Is there any special reason you're converting to ADO from DAO? Even though
Access 2000 was released with a lot of hooplah about ADO, it has since
developed that DAO is really the better choice if you're working with Jet
(Access) databases. You can still use DAO in Access 2000; all you have to
do is add a reference to the Microsoft DAO 3.6 Object Library, and (for
simplicity) remove the reference to the Microsoft ActiveX DataObhects 2.x
Library.

 

[Paul Shapiro]

You could also try .moveLast and then .moveFirst before checking the record
count. The count may not be available until the recordset has been
traversed.

 

[Dirk Goldgar]

You could also try .moveLast and then .moveFirst before checking the
record count. The count may not be available until the recordset has been
traversed.

But those methods will raise an error if the recordset is empty. You only
want to do that if you know the recordset isn't empty, and then want to know
how many records are in it.

 

[Anand]

Hey,
Thanks to both Dirk and Paul.
To respond:
I am moving to ADO because the DB is being ported to SQL Server 2005 with
Access FE.

Using .movenext was raising an error. Hence the question/post. Would using
rs.eof/rs.bof cause any performance issues - ill be using these extensively
and in some cases where the recordset returns several records.

Anand

 

[Dirk Goldgar]

Hey,
Thanks to both Dirk and Paul.
To respond:
I am moving to ADO because the DB is being ported to SQL Server 2005 with
Access FE.

Will you be using an ADP? Or will it be an MDB file with linked tables? If
the latter, you can still use DAO if you're more familiar with that.

Using .movenext was raising an error. Hence the question/post. Would using
rs.eof/rs.bof cause any performance issues - ill be using these
extensively
and in some cases where the recordset returns several records.

I can't think of any performance issues you would experience, just checking
BOF and EOF as needed.

 

[Paul Shapiro]

Agreed- no performance issue with checking .eof/.bof. Doing the .moveLast to
get the record count will force Access to load the entire recordset, which
could be a performance issue if you're using very large recordsets. But if
you need the count, that's the only way to get it from the recordset. If the
recordsets are truly huge, you could consider running a first query to
return just the row count without the data. If you're going to read all the
data anyway then there's again no impact from forcing the full data
retrieval to get the count.

I think Dirk already gave the corrections necessary to get the recordcount
without errors, something like:

Dim lngRecordCount as Long
rst.open strSQL
If rst.eof Then
lngRecordCount = 0
Else
rst.moveLast 'Without this statement Access does not necessarily
load the complete recordset, so it can't get the count
lngRecordCount = rst.RecordCount
rst.moveFirst
End If

 

[Anand]

Thanks again,
Dirk,
I will be using ADP files and not MDB files for the FE.

Will post again if I have any further queries on this.

Anand

 

[Dirk Goldgar]

Thanks again,
Dirk,
I will be using ADP files and not MDB files for the FE.

Will post again if I have any further queries on this.

Good luck!

 

[Maury Markowitz]

Does a -1 imply that records are returned but access is not able to
determine how many? Or is it that Access simply does not know if any records
were returned - could be none were returned!

The -1 means that ADODB was asked to open the recordset with a dynamic
cursor (the default). In that case it doesn't return a linecount when
you do the SELECT, so it doesn't return a value. With this switch set,
you'll always get a -1, even if there are zero rows returned.

If you open the RecordSet with a static cursor type, it will return
the number you'd expect, 0 if there's nothing returned, and the
correct number of records if there is. For this reason I always use
static cursors - they're also faster in theory and have other
advantages.

Set rstTest = New ADODB.Recordset
rstTest.Open "SELECT * FROM tblPrices where 1=0", cnn,
adOpenDynamic, adLockOptimistic
Debug.Print rstTest.recordCount

returns "-1"

Set rstTest = New ADODB.Recordset
rstTest.Open "SELECT * FROM tblPrices where 1=0", cnn,
adOpenStatic, adLockOptimistic
Debug.Print rstTest.recordCount

returns "0"

As others have noted, the proper way to test for pure existence is to
use the .EOF property of the RecordSource.

Maury