ADO - Recordcount

[accessman2]

Dim conn As ADODB.Connection
Dim rs As ADODB.Recordset
Set conn = CurrentProject.Connection

Set rs = conn.Execute("table1")

MsgBox rs.RecordCount

it gives out -1.

How can I make it correct number?

Thanks.

 

[boblarson]

I believe you have to use
rs.MoveLast before you can use rs.RecordCount.

 

[RoyVidar]

The ADO recordcount is in my view not reliable, and I would recommend using a SELECT Count(*) on the table/query with the same where condition if it is really, really necessary to get the actual count.

If you only need to check whether an opened recordset contains records, then use

If ((Not rs.Bof) And (Not rs.Eof)) Then
' there are records
End If

ADO .RecordCount will retun -1 in a lot of different situations, depending on Provider, Cursor Location and Cursor. While the cursor is whitin the .RecordCount keyword, hit F1 and investigate the help file. Usully, you'd get valid .RecordCount with a client side cursor.

boblarson,
you're thinking DAO, right? I don't think that approach will work with ADO.

 

[boblarson]

I wasn't thinking DAO, but apparently I never use the record count object in ADO as I ran a test to check and you're right it does return -1 regardless of whether it is used on a local table, or if it is used on a linked table.

 

[Moniker]

It returns a -1 if you open the recordset dynamically (adOpenDynamic). If you open it as a keyset (adOpenKeyset), the record count is right. However, you almost always want to open dynamically (or read only), so the correct way to get the count is to open the recordset and then use the MoveLast method. That forces the entire recordset to load, thereby giving you an accurate count.

rs.open <recordset info here>
rs.movelast
rs.recordcount (will now be accurate)

~Moniker

 

[RoyVidar]

You won't get dynamic cursor on Jet tables, it will be coerced to static or keyset, depending on specified locktype. Test it by using debug.print adOpenDynamic, .CursorType after accessing the opened recordset.

I still believe the method of "accessing" the whole recordset, as boblarson confirmed, is a DAO method that won't work on ADO. Either the .RecordCount is populated from the start, or it isn't. According to the help files "The property returns -1 when ADO cannot determine the number of records or if the provider or cursor type does not support RecordCount. ... The cursor type of the Recordset object affects whether the number of records can be determined. The Recordcount property will return -1 for a forward-cursor; the actual count for a static or keyset cursor; and either -1 or the actual count for a dynamic cursor, depending on the data source."

Do you have any sources/samples?

And then, even under circumstances where it should give an accurate count, it will sometimes still give -1, which is why I recommend a SELECT Count(*)... if the count is essential.

 

[deBassMan]

re: incorrect record count

Hi there

I have just wrestled with this issue.

Here's what worked for me ..

I am using VBA and ADO to access a remote MySQL db.

It appears that a server-side cursor has difficulties returning an accurate record count.

Solution: Switch to a client-side cursor

'set client-side cursor to ensure correct record count
rs.CursorLocation = adUseClient

good luck!

 

[deBassMan]

re: incorrect record count

Hi there

I have just wrestled with this issue.

Here's what worked for me ..

I am using VBA and ADO to access a remote MySQL db.

It appears that a server-side cursor has difficulties returning an accurate record count.

Solution: Switch to a client-side cursor

'set client-side cursor to ensure correct record count
rs.CursorLocation = adUseClient

good luck!

 

[RoyVidar]

Yes, that's what I said in my first reply. And the reason you'd usually get accurate .RecordCount with client side cursor, is probably because when you specify client side cursor, the cursor type is coerced to static, which according to the help files, will usually give accurate count (except in the few cases where it won't work at all).