One of the most misunderstood features of ADO is the RecordCount
property. I've heard from many frustated users on the topic and
quite a few of them have decided that it must just be something
that's not supported with their DB. Well I'm going to try and set
the record straight as best I can.
The RecordCount property is supported by both Access and SQL
Server. I can't speak for sure on other platforms, but I've been
told it's a pretty widely supported feature and I'd assume most
vendors have some way of doing it.
So why isn't it working for you? Well it's supported only if the
recordset supports approximate positioning (adApproxPosition) or
bookmarks (adBookmark). I only mention this so that if you feel
like testing for support you can do so using code like this:
If objRecordset.Supports(adBookmark) Or objRecordset.Supports(adApproxPosition) Then
' RecordCount will work!
End If
So what the heck does that mean?
Basically it's supported if you use the right kind of cursor. For
the most part all client-side cursors support it as do server-side
static and server-side keyset cursors. So the only two that don't
are server-side dynamic and server-side forward only cursors.
Unfortunately, most people don't specify the cursor type
to use when they open a recordset and what you usually get when you
don't specify one is a server-side, forward only cursor which is
one of the ones that doesn't support a record count! So the trick
is to specify what type of cursor you want.
If for some reason you need one of the ones that doesn't support
RecordCount, you can get around this by fully populating the
recordset before asking for the RecordCount. This can be
accomplished by doing a MoveLast, but again only if this is
supported by the recordset. Otherwise you're basically out of
luck unless you can wait until you've read all the records to get
an accurate count.
Oh and one last thing... If you're just trying to get the count
and don't care about the records instead or using ADO, try this SQL
syntax instead:
SELECT COUNT(field_name) FROM table_name;
It tends to be easier on the server and you can even use the WHERE
clause to limit the results to match whatever criteria you want.
If you want to get really creative, try adding the GROUP BY or
other clauses. With some thought and a little bit of trial and
error, you'll soon be counting anything you want in the database!
