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

Posted by Andrew Gould on 05 July 2019

This video shows you how to call and pass values to stored procedures from a SQL Server database using ADO commands. You'll learn how to reference parameters by name, how to pass values to parameters and how to retrieve a result set using a recordset object. You'll also see how to extract information from a stored procedure using output parameters.

You can download any files that you need to follow the video here.

You can increase the size of the video:

Full screen mode for YouTube

You can view the video in full screen mode as shown on the left, using the icon at the bottom right of the frame.

You can also increase the quality of the video:

Changing resolution

You can improve the resolution of the video using another icon at the bottom right of the frame. This will slow down the connection speed, but increase the display and sound quality. This icon only becomes visible when you start playing the video.

Finally, if nothing happens when you play the video, check that you're not using IE in compatibility view.

This page has 1 thread Add post
18 Mar 20 at 08:01

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

 

19 Mar 20 at 08:17

Hi Duggie,

Here's the documentation for the GetRecords method you're using https://docs.microsoft.com/en-us/sql/ado/reference/ado-api/getrows-method-ado?view=sql-server-ver15

The important part is:

After you call GetRows, the next unread record becomes the current record, or the EOF property is set to True if there are no more records.

Rather than doing that, you can set your recordset to use a client side cursor and then just just the RecordCount property to determine how many rows you have retrieved:

Sub GetDataFromStoredProcedure()

    Dim cn As ADODB.Connection
    Dim rs As ADODB.Recordset
    
    Set cn = New ADODB.Connection
    
    cn.ConnectionString = _
    "Provider=SQLNCLI11;Server=.\SQL2017;Database=Movies;Trusted_Connection=yes"
    
    cn.Open
    
    Set rs = New ADODB.Recordset
    
    rs.ActiveConnection = cn
    rs.Source = "spFilmList"
    rs.CursorLocation = adUseClient
    rs.Open
    
    Debug.Print rs.RecordCount
    
    If rs.RecordCount > 1000000 Then
        'code to apply recordset filters
    Else
        WriteToSheets rs
    End If
    
    rs.Close
    cn.Close
    
End Sub

Hope that helps!

08 Apr 20 at 10:16

Andrew,

Thanks for your suggestion.

Apologies for the delay in replying, been side tracked with job-hunting, successfully started but then cut owing to a certain worldwide incident!