This table currently has 9 records in it!

Show the table

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!