VBA - working with data videos | Excel VBA Part 56.7 - ADO Commands and Stored Procedures

Andrew,

I have followed your video and everything works fine.

However, I would like to find out the number of records in the recordset because if there are more than 1m records, they can't be returned to the worksheet and so I would like to do some filtering to the results.

I added these few lines to your existing code:

        Dim j
        
        j = rs.GetRows(rs.RecordCount)
        
        Dim myarray() As Variant
        
        myarray = j

So altogether, the entire code is as follows:

Sub GetDataFromStoredProcedure()

    Dim cn As ADODB.Connection
    Dim cmd As ADODB.Command
    Dim rs As ADODB.Recordset   
    
    Set cn = New ADODB.Connection
    
    cn.ConnectionString = _
    "Provider=SQLNCLI11;Server=MYLAPTOPNAME\SQLEXPRESS;Database=Movies;Trusted_Connection=yes"
    
    cn.Open
    
    Set cmd = New ADODB.Command
    
    cmd.ActiveConnection = cn
    cmd.CommandType = adCmdStoredProc
    cmd.CommandText = "spFilmList"
    
    Set rs = cmd.Execute   
        
        Dim j
        
        j = rs.GetRows(rs.RecordCount)
        
        Dim myarray() As Variant
        
        myarray = j
        
    WriteToSheets rs
    
    rs.Close
    cn.Close
    
End Sub

Private Sub WriteToSheets(ResultSet As ADODB.Recordset)

    Dim ws As Worksheet
    Dim i As Integer
        
    Set ws = ThisWorkbook.Worksheets.Add
    
    ws.Select
    
    For i = 0 To ResultSet.Fields.Count - 1
        ws.Cells(1, i + 1).Value = ResultSet.Fields(i).Name
    Next i     
    
    ws.Range("A2").CopyFromRecordset ResultSet
    
End Sub

Why is it that adding those few lines makes the code return no results onto the worksheet (though the field names are retirned)?

Thanks