title: Execute SQL Server stored procedure from VBA and retrieve all messages and result sets
body: <p>I want to be able to execute a SQL Server stored procedure from MS Access VBA, in such a way that I can read (1) all the resulting result sets, not just the first one; and (2) any messages produced by PRINT statements or similar.</p>
  
  <p>I have a test stored procedure with one input parameter, which produces 3 distinct result sets and about 90 messages. It calls several sub-stored procedures, I can EXEC it perfectly well from SSMS, but it isn’t clear (to me) how best to do it from Access VBA. I have tried the following so far:</p>
  
  <ol>
  <li><p>DAO. Using SQL pass-through queries, I can get a lot of what I want in DAO, though it is a little clunky. It returns the first of the 3 result sets as a recordset, and by using the LogMessages attribute I can get a table (“Admin – NN”) containing the emitted messages.</p></li>
  <li><p>ADO. Using Connection and Command objects, I can obtain a single recordset representing the first result set from the stored procedure. However, I can’t seem to persuade it to produce anything but a forward-only recordset. Regarding messages, at one point, all of them (at least, the first 127 of the approx. 150 I expected) were going into the connection’s Errors collection (!), but when I cut the number down to about 90, none of them appeared anywhere at all that I could find.</p></li>
  </ol>
  
  <p>What I really want, as I said at first, is the output from <strong>all result sets</strong>, <strong>plus the messages</strong>. Is this possible? </p>
  
  <p>Here is a listing of the routine I am currently using for executing a stored procedure :</p>
  
  <pre class="lang-vb prettyprint-override"><code>Function ExecuteStoredProcedureADO(SPName As String, Connect As String, ReturnsRecords As Boolean, _
     ParamArray Params() As Variant) As ADODB.Recordset
     ' v1.0 2018/06/26
     ' execute stored procedure SPName on a SQL Server database specified by the string in Connect
  
     Dim strErr As String
     Dim i As Integer
     Dim lngRecsAffected As Long
  
     Dim cnn As ADODB.Connection
     Dim cmd As ADODB.Command
     Dim errCurr As ADODB.Error
     Dim rst As ADODB.Recordset
  
     On Error GoTo Catch
     Set ExecuteStoredProcedureADO = Nothing
  
     Set cnn = New ADODB.Connection
     cnn.Errors.Clear
     cnn.mode = adModeRead
     cnn.CommandTimeout = 300
     cnn.Open Connect
  
     Set cmd = New ADODB.Command
     With cmd
        .ActiveConnection = cnn
        .CommandText = SPName
        .CommandType = adCmdStoredProc
  
        For i = 0 To UBound(Params) Step 4
           .Parameters.Append .CreateParameter(Params(i), Params(i + 1), adParamInput, Params(i + 2), Params(i + 3))
        Next i
        Set rst = New ADODB.Recordset
        rst.CursorType = adOpenStatic
        If ReturnsRecords Then
           '''Set rst = .Execute(lngRecsAffected)
           rst.Open cmd, , adOpenStatic, adLockReadOnly
        Else
           Set rst = .Execute(, , adExecuteNoRecords)
        End If
     End With
     If ReturnsRecords Then Set ExecuteStoredProcedureADO = rst
  
  Final:
     On Error Resume Next
     If Len(strErr) &gt; 0 Then Call AppendMsg(strErr)
     Set rst = Nothing
     Set cmd = Nothing
     Exit Function
  
  Catch:
     If cnn.Errors.Count &gt; 0 Then
        With cnn
           For Each errCurr In cnn.Errors
              strErr = strErr &amp; "Error " &amp; errCurr.Number &amp; ": " &amp; errCurr.Description _
                 &amp; " (" &amp; errCurr.Source &amp; ")" &amp; vbCrLf
           Next errCurr
           strErr = Left(strErr, Len(strErr) - 2) ' truncate final CRLF
        End With
     Else
        strErr = "Error " &amp; Err.Number &amp; ": " &amp; Err.Description &amp; " (" &amp; Err.Source &amp; ")"
     End If
     MsgBox strErr, vbOKOnly, gtitle
     Resume Final
  
  End Function
  </code></pre>
  
  <p>Addendum: Regarding the multiple result sets, I am hoping that <a href="http://msdn.microsoft.com/en-us/library/ms677569%28VS.85%29.aspx/" rel="nofollow noreferrer" title="this example using the NextRecordset method of the recordset object">http://msdn.microsoft.com/en-us/library/ms677569%28VS.85%29.aspx</a> 
  will be of some help.</p>