ADO recordset.recordcount -1 ?

[jestes]

I've got a recordset that I KNOW has records in it, but I keep getting a -1 as recordcount... help!

[mdake]

The recordset couunt property is not set until you have navigated to the end of the recordset.

So do
rs.MoveLast
rs.MoveFirst

Then check the RecordCount.

This only work for recordsets that allow backward navigation, not forward only.

You can also get a count by using the SQL query Count that matches your criteria. This works for any recordset.

[ccazes]

If you want to use the .recordcount property for an ADO recordset you need to specify a clientside cursor location when opening your connection.

Set dbsSubs = New ADODB.Connection
dbsSubs.CursorLocation = adUseClient

[mdake]

Yes that is true most times, but if you use the Connection.Execute to return a Recordset even that wont help.

[rudgej]

Here's a comment from http://www.able-consulting.com/ADO_Faq.htm#Q2 :

By default, ADO creates a server-side recordset with a CursorType of forward-only (adOpenForwardOnly). A forward-only cursor does not support the RecordCount property, even if you move to the last record in the recordset after you've opened it.

If you need the RecordCount to be correct, then set the CursorType to something other than forward-only (e.g. adOpenKeyset or adOpenStatic).
Using a dynamic cursor (adOpenDynamic) will not help in this case since the number of records in a dynamic cursor may change.

If you create a client-side recordset (adUseClient), then ADO automatically sets the CursorType to a static cursor (adOpenStatic), and hence the RecordCount property will always be correct for "client-side" recordsets.

[mdake]

Yes again you are right, we are pretty much talking about the same thing. what I was getting at is that there are situation where you can not ascertain the recordcount from it's property.

If you use
set rs = Connection.Execute(blah blah blah)

This is ReadOnly, ForwardOnly, see below from MSDN

Remarks

Using the Execute method on a Connection object executes whatever query you pass to the method in the CommandText argument on the specified connection. If the CommandText argument specifies a row-returning query, any results that the execution generates are stored in a new Recordset object. If the command is not a row-returning query, the provider returns a closed Recordset object.

The returned Recordset object is always a read-only, forward-only cursor. If you need a Recordset object with more functionality, first create a Recordset object with the desired property settings, then use the Recordset object's Open method to execute the query and return the desired cursor type.

The only way to get a count from this rs is to use a SQL query with COUNT in it then use the value stored in the first filed.
Like this....

    Set pRst = pConn.Execute("SELECT COUNT(*) FROM T_Bank_Reason")
    Dim cnt As Integer
    cnt = pRst(0)

Or if you are using SQL or a database that supports multiple SQL statements in a query then you can do something like this....

    Set pRst = pConn.Execute("SELECT COUNT(*) FROM T_Bank_Reason;" & "SELECT * FROM T_Bank_Reason")
    cnt = pRst(0)
    Set pRst = pRst.NextRecordset
 
 ' the second Recordset contains the actual rows

regards


Ooops originally pasted the wrong examples, fixed it now.

[mdake]

I know this post is dealing with ADO recordsets but, it seems this RecordCount is the topic of discussion in another recent post.

http://www.vbforums.com/showthread.p...threadid=89801

regards,

[Edneeis]

Damn mdake you are like the recordcount master. Did you have some crazy problem with it once and go on a quest to learn all there is or what? I'm not even being sarcastic, I'm impressed.

[mdake]

Edneeis,
thanks, I think.
No I learnt DAO stuff first then going to ADO the RecordCount was so different. I just have had a lot of playing around with both versions and I have used Execute Method to get recodsets that I just want to read them real fast then close them and RecordCount is pretty useless in that situation.

best regards,