Record Count returning -1

[sam2601]

Hi guys,

The record count for the code below always returns -1

adoCmd.CommandType = adCmdText
adoCmd.CommandText = g_clsTables.SelJoinQuery(XYZ)
rsRec.Open adoCmd, , adOpenKeyset, adLockOptimistic

Any Suggestions as to how to change it so that it returns me the record count

Thanks!!! Any help surely appreciated!!!

 

[chiph]

You can't trust the RecordCount property. It doesn't always return a sensible value (as you've found out).

If you need to find out how many records you have, do a separate query: SELECT COUNT(*) FROM tbl_Foo

Chip H.


____________________________________________________________________
If you want to get the best response to a question, please read FAQ222-2244 first

 

[sam2601]

Thanks!!!

 

[billy71]

This works for me.

Private Sub Form_Activate()
Label1 = Adodc1.Recordset.RecordCount
Text2 = Adodc1.Recordset.RecordCount
End Sub


The record count is shown in a Label and a Text box but you could move it to an integer.
Hope this helps.

 

[Droops]

RecordCount will always return -1 when the CursorLocation defined for the Connection object is either left blank or set to adUseServer.

set it to adUseClient when opening the connection and test the RecordCount property once the Recordset is opened.
Careful however, it may have an impact on performance when the Recordset returns large amounts of data.

I have heard of strange results as chiph wrote.
I read somewhere that it is better to run a MoveLast, MoveFirst on the RecordSet (if its type allows it) to ensure the RecordCount property returns the correct value.
But that was some time ago and may concern specific versions af ADO. Frankly, I can't remember.

Hoping this helps.

Droops.