RecSet.RecordCount = -1. Why?

[jal]

In some situations I find that a well-populated recordset reports RecordCount of -1.

Is there a method to this madness? I mean, is there a known set of circumstances where I can expect this value, so that it won't take me by surprise anymore?

And is this a bug?

 

[gemma-the-husky]

are you sure you arent miscasting a boolean into a numeric somehow

-1 is "true" in a boolean sense, and this is likely to result in the behaviour you describe

 

[Calvin]

Long standing issue with recordsets has been the issue of them not having a more intuitive RecCount. The solution is before you perform the RecSet.Recount, insert a RecSet.MoveLast method to allow for a more accurate RecCount value.

Example:

Dim lngTotal As Long
Dim rst As New adodb.Recordset
rst.Open "Select * From MyTable", CurrentProject.Connection, , adOpenKeyset, adLockReadOnly
If Not rst.BOF And Not rst.EOF Then
    [COLOR="Red"]rst.MoveLast[/COLOR]
    lngTotal = rst.RecCount
End If
rst.Close
Set rst = Nothing
Debug.Print lngTotal

 

[jal]

Long standing issue with recordsets has been the issue of them not having a more intuitive RecCount. The solution is before you perform the RecSet.Recount, insert a RecSet.MoveLast method to allow for a more accurate RecCount value.

Example:

Dim lngTotal As Long
Dim rst As New adodb.Recordset
rst.Open "Select * From MyTable", CurrentProject.Connection, , adOpenKeyset, adLockReadOnly
If Not rst.BOF And Not rst.EOF Then
    [COLOR=red]rst.MoveLast[/COLOR]
    lngTotal = rst.RecCount
End If
rst.Close
Set rst = Nothing
Debug.Print lngTotal

In VBA I didn't see a "rst.RecCount" but only recordset.RecordCount. And I can't do a MoveLast due to my forward-only cursor.

 

[jal]

are you sure you arent miscasting a boolean into a numeric somehow

-1 is "true" in a boolean sense, and this is likely to result in the behaviour you describe

I don't believe I'm casting. Here's some of my code.

recSet.Open sql, cn, adOpenForwardOnly, adLockReadOnly
MsgBox (recSet.RecordCount) 'reports -1
Dim count As Integer
Open pathToDesktop & "\Hits.txt" For Output As #2
Do While recSet.EOF = False
count = count + 1
Print #2, recSet("EobText") & vbCrLf & vbCrLf & vbCrLf
recSet.MoveNext
Loop
recSet.Close
cn.Close
Close #2 '
'Next line reports the accurate count. Proves that RecordCount was wrong.
MsgBox ("Found " & count & " hits. See the file Hits on your Desktop.")


In a moment I'll give you scenario to reproduce this problem.

 

[RuralGuy]

From VBA Help on DAO RecordCount:

When you work with linked TableDef objects, the RecordCount property setting is always –1.

 

[RuralGuy]

From VBA Help on ADO.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-only 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.

 

[jal]

are you sure you arent miscasting a boolean into a numeric somehow

-1 is "true" in a boolean sense, and this is likely to result in the behaviour you describe

In this case I was trying to get a list of tables. I thought it had failed due to the record count of -1. But the recordset was indeed populated:

Dim recSet As ADODB.Recordset
Dim cn As New ADODB.Connection
cn.Open ("Provider=Microsoft.Jet.OLEDB.4.0; Data Source=C:\Northwind.mdb")
Set recSet = cn.OpenSchema(adSchemaTables)
msgBox(RecSet.RecordCount) '<--------- reports -1
Do While Not recSet.EOF
MsgBox (recSet("Table_Name"))
recSet.MoveNext
Loop
recSet.Close
cn.Close

 

[Calvin]

In VBA I didn't see a "rst.RecCount" but only recordset.RecordCount. And I can't do a MoveLast due to my forward-only cursor.

Sorry, it was pseudo code, rst.RecordCount would be correct.

Is there any reason why you have to use a forward only connection?

 

[jal]

From VBA Help on ADO.RecordCount:

Thanks, I didn't even see this post.

 

[jal]

Sorry, it was pseudo code, rst.RecordCount would be correct.

Is there any reason why you have to use a forward only connection?

I was trying to learn the basics of recordsets. An expert advised to use forward-only whenever possible in virtue of performance gains.