Connection to SQL returning -1 RecordCount

[brusk]

First time trying to get Excel to connect to a SQL 2012 DB to read/write to some tables. I found a decent snippet that seems to get me close as I'm already using a ADO/Jet connection to query internal worksheet tables I have a little experience getting this to work. Below is the code I'm trying, it seems to properly connect as I can see the fields being pulled but for some reason it always shows the RecordCount as -1. I'm trying to find the easiest method to query a table and dump that to an Array so I can use that in other code.

Public Sub SQL(ByVal SQLCommand As String, ByRef Results As Variant)    
    Dim conn As ADODB.Connection
    Dim rs As ADODB.RecordSet
    Dim sConnString As String
 
    ' Create the connection string.
    sConnString = "Provider=SQLOLEDB;Data Source=Tesbed1\XLTest;" & _
                  "Initial Catalog=resource;" & _
                  "User ID=psdbusr;Password=Password"
    
    ' Create the Connection and Recordset objects.
    Set conn = New ADODB.Connection
    Set rs = New ADODB.RecordSet
    
    ' Open the connection and execute.
    conn.Open sConnString
    Set rs = conn.Execute(SQLCommand & ";")
    
    ' Check we have data.
    If Not rs.EOF Then
        ' Transfer result.
        'Sheets(1).Range("A1").CopyFromRecordset rs
        MsgBox rs.RecordCount
        Set Results = rs.GetRows(rs.RecordCount)
    ' Close the recordset
        rs.Close
    Else
        MsgBox "Error: No records returned.", vbCritical
    End If


    ' Clean up
    If CBool(conn.State And adStateOpen) Then conn.Close
    Set conn = Nothing
    Set rs = Nothing
    
End Sub

 

[Fazza]

the RecordCount is nearly always -1, IIRC for nearly all cursor types: so can't be used how you'd expect. there might be one cursor type for which it works as expected? I don't recall. From memory there are 4 cursor types.

instead you can use the locals window to see the record object (& how many records it contains), or loop through the recordset
if the data & query are straightforward, the recordset will have the data you expect

cheers

 

[RoryA]

If you want all results, you don't need the record count anyway- just use:

Results = rs.GetRows

Note: GetRows returns an array not an object so you don't use Set.

 

[brusk]

the rs.GetRows gave me an error and when I try things like rs.MoveFirst or rs.MoveLast they give errors. It really seems like it didn't pull any data properly but it wasn't EOF. This was a straight forward query SELECT * from a table that has 4 columns and 5 records.

 

[RoryA]

What was the actual error message?

 

[Sektor]

You must set the CursorLocation of Connection as "adUseClient"

 

[brusk]

Thanks, that gave me just enough to go on. At least to get it working I'm sure I'll be tweaking some things but at least it pulling records now. Next I'll have to figure out how to write to SQL.

Here's what I ended up with.

Public Sub SQL(ByVal SQLCommand As String, ByRef Results() As String)

    Dim conn As ADODB.Connection
    Dim rs As ADODB.RecordSet
    Dim sConnString As String
    Dim sTemp As Variant
    Dim iLoop1 As Integer
    Dim iLoop2 As Integer
 
    ' Create the connection string.
    sConnString = "Provider=SQLOLEDB;Data Source=10.153.32.235\UIP_Config;" & _
                  "Initial Catalog=resource;" & _
                  "User ID=psdbusr;Password=Gr33nJ@ck3t"
    
    ' Create the Connection and Recordset objects.
    Set conn = New ADODB.Connection
    Set rs = New ADODB.RecordSet
    
    ' Open the connection and execute.
    conn.Open sConnString
    rs.Open SQLCommand, conn, adOpenKeyset, adLockReadOnly
    
    ' Check we have data.
    If Not rs.EOF Then
        ' Transfer result.
        rs.MoveFirst
        ReDim Results(1 To rs.RecordCount, 1 To rs.Fields.Count)
        Do While Not rs.EOF
            iLoop1 = iLoop1 + 1
            For iLoop2 = 1 To rs.Fields.Count
                If Not rs.Fields(iLoop2 - 1) = vbNullString Then Results(iLoop1, iLoop2) = rs.Fields(iLoop2 - 1)
            Next iLoop2
            rs.MoveNext
        Loop
        
    ' Close the recordset
        rs.Close
    Else
        MsgBox "Error: No records returned.", vbCritical
    End If


    ' Clean up
    If CBool(conn.State And adStateOpen) Then conn.Close
    Set conn = Nothing
    Set rs = Nothing
    
End Sub