I have the below sub, in vba. It will run the stored procedure successfully, but I want to know if the stored procedure ran successfully by viewing the return value. I can’t get the return value so far using vba alone. The store procedure is dropping and creating a table.

Public Sub New_Report_2()
    
        Dim strConnection As String, db As DAO.Database, rs As Recordset, qd As QueryDef
        
        Set db = CurrentDb
        
        strConnection = "ODBC;DRIVER=SS;SERVER=x;DATABASE=y;Trusted_Connection=Yes"
        
       ' Create a temporary QueryDef object to retrieve data from a Microsoft SQL Server database.
        Set qd = db.CreateQueryDef("")
        With qd
            .Connect = strConnection
            .ODBCTimeout = 200
            .sql = "exec myschema.month_end_01"
            .ReturnsRecords = True
            Set rs = .OpenRecordset()
            If (rs.BOF And rs.EOF) Then
                Debug.Print "1 - Stored Procedure Return Value: " & rs![Return Value]
            Else
                Debug.Print "2 - Stored Procedure Return Value: " & rs![Return Value]
            End If
       End With
    
    End Sub

I get this error:

I tried it another way:

Public Sub New_Report_2()

    Dim strConnection As String, db As DAO.Database, rs As Recordset, qd As QueryDef
    
    Set db = CurrentDb
    
'   strConnection = fnstrConnection
    strConnection = "ODBC;DRIVER=SQL Server;SERVER=x;DATABASE=y;Trusted_Connection=Yes"
    
   ' Create a temporary QueryDef object to retrieve data from a Microsoft SQL Server database.
    Set qd = db.CreateQueryDef("")
    With qd
        .Connect = strConnection
        .ODBCTimeout = 200

        .sql = "declare @return_value int" & vbCrLf
        .sql = .sql + "exec @return_value = myschema.my_sp" & vbCrLf
        .sql = .sql + "select 'Return Value' = @return_value"
        
        .ReturnsRecords = True
        
        Debug.Print .sql
        
        Set rs = .OpenRecordset()
        If (rs.BOF And rs.EOF) Then
            Debug.Print "1 - Stored Procedure Return Value: " & rs![Return Value]
        Else
            Debug.Print "2 - Stored Procedure Return Value: " & rs![Return Value]
        End If
   End With

End Sub

and get this error:

If I call the stored procedure inside of a pass-thru query like so, then I can get the return value, but I did not want to create a pass-thru query if I don’t need to.

Public Sub New_Report_3()

    Dim db As DAO.Database, rs As Recordset
    
    Set db = CurrentDb
    
    Set rs = db.OpenRecordset("Test Pass Thru")
    
    If (rs.BOF And rs.EOF) Then
        Debug.Print "1 - Stored Procedure Return Value: " & rs![Return Value]
    Else
        Debug.Print "2 - Stored Procedure Return Value: " & rs![Return Value]
    End If

End Sub 

Thank you all for your time, I appreciate it.

Guess what I figured it out. My second example had a typo of a missing ‘=’ in this line:

Before: 
.sql = .sql + "exec @return_value myschema.my_sp" & vbCrLf
After:
.sql = .sql + "exec @return_value = myschema.my_sp" & vbCrLf

Now the return value is returned.
However if there is a more elegant way to do this I would love to know. Thank you all again.