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

Microsoft SQL ServerVisual Basic Classic

Avatar of undefined
Last Comment
sidwelle

8/22/2022 - Mon
chapmandew

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

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 ?
VK

I only know about that problem when using DAO. Then it helps when you do
 
oRec.MoveLast
oRec.MoveFirst
Msgbox orec.RecordCount
All of life is about relationships, and EE has made a viirtual community a real community. It lifts everyone's boat
William Peck
ASKER
sidwelle

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
FER_G

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
See how we're fighting big data
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
ASKER
sidwelle

Works, thanks for the code and the comments.