Link to home
Start Free TrialLog in
Avatar of sidwelle
sidwelleFlag for United States of America

asked on

recordcount = -1

Why does my recordcount from my record set return the value -1

I can walk through the records and the fields are populated, but Why can't I get the recordcount ?

Thanks

Dim oConn As ADODB.Connection
    Dim oRec As ADODB.Recordset
    Dim oSqlCmd As ADODB.Command
    Dim sConn As String, sCmd As String
    Dim lType As Long, lAlertCount As Long
    Dim I As Long
    Dim J As Long
 
    Set oConn = New ADODB.Connection
    sConn = "Driver={SQL Server};" & _
           "Server=" & SrvIP & ";" & _
           "Database=MyDB;" & _
           "Uid=ID;" & _
           "Pwd=PW"
 
    oConn.Open (sConn)
    
    Dim oCmd As New ADODB.Command  ' Create a new SqlCommand
    oCmd.ActiveConnection = oConn
    
    oCmd.CommandText = "Select ... From ... Where ..." 
        
    Set oRec = oCmd.Execute
    
Msgbox orec.RecordCount
 
    If Not oRec.EOF And Not oRec.BOF Then
        oRec.MoveFirst
        Do Until oRec.EOF
            I = I + 1
            
	'Walk through the records ...
    
            oRec.MoveNext
        Loop
    End If
 
    oConn.Close
    Set oConn = Nothing
    Set oRec = Nothing

Open in new window

Avatar of chapmandew
chapmandew
Flag of United States of America image

making your recordset dynamic or keyset should work for you.
Avatar of sidwelle

ASKER

I checked the type after the query and the type was dynamic.
Is that not the default ?
Can you show me where to declare it ?
I only know about that problem when using DAO. Then it helps when you do
 
oRec.MoveLast
oRec.MoveFirst
Msgbox orec.RecordCount
I used to do that with Access tables, it causes the recordset to populate.
When I try MoveLast, I get a "Fetching not allowed ...  "

ASKER CERTIFIED SOLUTION
Avatar of FER_G
FER_G
Flag of Argentina image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Works, thanks for the code and the comments.