ADO RecordCount Property

[JimmyJam]

When I use the SQLOLEDB Provider I cannont get a record count. All the documentation that I have read says I need to use Keyset or static cursor type. And then read through the recordset to get a count. I tried this and it does not work.

Has anyone found a way to get a record count using the SQLOLEDB and using either server side or client side cursors.

Thanks

[RogerH]

use

Recordset.MoveLast

and then

Recordset.RecordCount

Roger

[MartinLiss]

RogerH: I haven't tried it myself, but I believe that when using ADO you no longer need to do the movelast to get the recordcount like you need to do when using DAO.

------------------
Marty

[RogerH]

Marty,

you will receive -1 as long as the recordset doesn't know the number of records. Alot became better in ADO, unfortunately not that matter.

Tip: If you want to know if the recordset is empty use

IF recset.eof and recset.bof THEN
MSGBOX "Empty!"
ENDIF

This will speed things up.

Roger

[Clunietp]

You need to specify to open the recordset as adOpenKeyset or adOpenStatic cursor to be able to get the recordcount property of the ADO Recordset, or you could use a counter and count until EOF if you are using a forward only cursor

[JimmyJam]

Clunietp

I already tried those cursor types. They don't work. The Keyset and the Static Cursor Types do not work. I still get -1 Record Count. Our other programmers have had the same problem.

Do you have any ideas

Jimmy

[vladimir]

That's the code that works for me:

Dim cn As New ADODB.Connection
Dim rs As New ADODB.Recordset
cn.CursorLocation = adUseClient
cn.Open "DSN=Ship2000"
rs.Open "Select ssn from history", cn, adOpenStatic
MsgBox rs.RecordCount

The History table has 200013 records. For client-side cursor everything is ok. For server-side cursor it says "Timeout expires" on Open method (too many records) but with the smaller subset of records server-side keyset cursor works.

And, i think

Dim lngCounter As long
Dim cn As New ADODB.Connection
Dim rs As New ADODB.Recordset
cn.CursorLocation = adUseClient
cn.Open "DSN=Ship2000"
rs.Open "Select ssn from history", cn, adOpenStatic
Do While Not rs.eof
lngCounter = lngCounter+1
rs.MoveNext
Loop
MsgBox lngCounter

should work in any case.

Regards, Vlad

[JimmyJam]

Thanks Vlad

You have been the most helpful

JimmyJam

[gravyboy]

This works for me though . . .

rst.Open "m:\adonew\gerbil.rst", _ & "Provider=MSPersist", adOpenKeyset, _ & adLockBatchOptimistic, adCmdFile

rst.MoveFirst

StatusBar1.Panels(3).Text = rst.RecordCount


I know I'm using a saved recordset but it also works using a currently open one.

??Matt

[Serge]

If you want to see the record count, then you HAVE to specify the CursorLocation as Client:

cn.CursorLocation = adUseClient


------------------

Serge

Software Developer
[email protected]
ICQ#: 51055819