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.

This video has the following accompanying files:

File name Type Description
01 Create Movies Database 2016.sql SQL query
02 Create Movie Procedure Single Parameter.sql SQL query
03 Create Movie Procedure Multi Parameters.sql SQL query
04 Create Movie Procedure Output Parameters.sql SQL query
Stored Procedures and Parameters with ADO.xlsm Excel workbook with macros

Click to download a zipped copy of the above files.

Making a  video bigger

You can increase the size of your video to make it fill the screen like this:

View full screen

Play your video (the icons shown won't appear until you do), then click on the full screen icon which appears as shown at its bottom right-hand corner.

 

When you've finished viewing a video in full screen mode, just press the Esc key to return to normal view.

Improving the quality of a video

To improve the quality of a video, first click on the Settings icon:

Settings icon

Make sure you're playing your video so that the icons shown appear, then click on this gear icon at the bottom right-hand corner.

 

Choose to change the video quality:

Video quality

Click on Quality as shown to bring up the submenu.

 

The higher the number you choose, the better will be your video quality (but the slower the connection speed):

Connection speed

Don't choose the HD option unless you have a fast enough connection speed to support it!

 

Is your Wise Owl speaking too slowly (or too quickly)?  You can also use the Settings menu above to change your playback speed.

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!