ADO RecordCount Property

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

use

Recordset.MoveLast

and then

Recordset.RecordCount

Roger

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

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

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

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

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

Thanks Vlad

You have been the most helpful

JimmyJam

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

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