Run Stored Procedure and Return Values from Vba

How to return values from a SQL Server Stored Procedure and Utilise them in Access VBA

Set cnn = New adodb.Connection
cnn.ConnectionString = 
   "DRIVER=SQL Server;SERVER=SERVER\SERVER;DATABASE=a_db;Trusted_Connection=Yes"

cnn.Open cnn.ConnectionString

Set cmd = New adodb.Command
cmd.ActiveConnection = cnn
cmd.CommandType = adCmdStoredProc
cmd.CommandText = "stprMoveDataSet"

Set param1 = cmd.CreateParameter
               ("@DataSetID", adInteger, adParamInput, , stDataSet)
cmd.Parameters.Append param
Set param2 = cmd.CreateParameter
               ("@Destination", adChar, adParamInput, 1, stDestination)
cmd.Parameters.Append param
Set param3 = cmd.CreateParameter
               ("@errStatusOK", adBit, adParamOutput, , adParamReturnValue)
cmd.Parameters.Append param

rs.CursorType = adOpenStatic
rs.CursorLocation = adUseClient
rs.LockType = adLockOptimistic
rs.Open cmd

Assign a stored procedure return value to a VBA variable

There are two approaches, using output parameters or setting a ReturnValue (discussed below). For OUTPUT parameters, quoting from this SO link:

essentially you just need to create a SqlParameter, set the Direction
to Output, and add it to the SqlCommand's Parameters collection. Then
execute the stored procedure and get the value of the parameter.

See the code from that page.

However, you also need to include the word OUT (or OUTPUT) in your variable declaration:

@NextSumID integer OUT

When you declare the variable as OUT (or OUTPUT) it will be returned automatically, with whatever value it has when the procedure finishes, so you could just use RETURN.

Return Data from a Stored Procedure :MSDN

You can, instead, use RETURN @NextSumID because you are just returning a single, integer, value. For this approach, you need to specify the parameter as the ReturnValue:

theParameter.Direction = ParameterDirection.ReturnValue

This approach is discussed further here (MSDN).

My vba code is designed return a value from a stored procedure (in SQL) using an output parameter, but I am unable to get a value to return

I figured it out. For anyone who is curious whenever you are looking to return a single value from a database within SQL from vba don't use a recordset. (although I guess you can) The reason is Recordsets are designed to return data in the form of a table - which caused a lot problems for me.

To return a single value all you have to do is execute your SQL command in vba, and then set your return variable equal to the value of the output parameter from you're stored procedure. If that doesn't make sense I have put all my working code below from vba and my stored procedure.

TEST SUB - I used this to test one example of possible inputs from excel.

Private Sub TestSub()

SQL_StoredProcedure "Stainless Steel", "DENSITY", "WIRE_TYPE", "WIRE_INDEX"

End Sub

VBA MODULE

Option Explicit

Private output As String

' Parameters are passed in that will be used to create a SQL statement
' for a stored procedure.
Public Sub SQL_StoredProcedure(ByVal sql_ui As String, _
                                ByVal sql_pci As String, _
                                ByVal sql_sci As String, _
                                ByVal sql_dtn As String)

    On Error GoTo RunTimeError

    Dim sqlconxn As ADODB.connection ' Connection between vba and SQL
    Dim sqlcmd As ADODB.Command ' Operates as a command between SQL and vba
    Dim sqlfld As ADODB.field ' Used to refer to the records or fields in SQL

    Dim output As String ' The output retrieved from the SQL stored procedure
    Dim conxnString As String ' Connection string is used to make the connection between vba and SSMS

    Application.ScreenUpdating = False

    ' String used to establish a connection to the database
    conxnString = '{your connection string}'

    ' Every time the SQL_StoredProcedure function is called a new instance of the
    ' connection, command, and recordset are made and then immediately closed once the function finishes
    Set sqlconxn = New ADODB.connection
    Set sqlcmd = New ADODB.Command

    sqlconxn.ConnectionTimeout = 30
    sqlconxn.Open conxnString ' makes the connection between SQL

    MsgBox "Connection 1 state: " & GetState(sqlconxn.state) ' Checks the status of the connection

    sqlcmd.CommandType = adCmdStoredProc ' sets command to a stored procedure
    sqlcmd.CommandText = "GET_ADDITIVE_DATA" ' name of the stored procedure
    sqlcmd.ActiveConnection = sqlconxn ' makes the sql connection

    ' Parameters that need to be called and defined whenever the stored procedure is called
    sqlcmd.Parameters.Append _
        sqlcmd.CreateParameter("@user_index", adVarChar, adParamInput, 255, sql_ui)
    sqlcmd.Parameters.Append _
        sqlcmd.CreateParameter("@primary_col_index", adVarChar, adParamInput, 255, sql_pci)
    sqlcmd.Parameters.Append _
        sqlcmd.CreateParameter("@secondary_col_index", adVarChar, adParamInput, 255, sql_sci)
    sqlcmd.Parameters.Append _
        sqlcmd.CreateParameter("@data_table_name", adVarChar, adParamInput, 255, sql_dtn)
    sqlcmd.Parameters.Append _
        sqlcmd.CreateParameter("@record_value", adVarChar, adParamOutput, 255)

    ' Executes the sql command with all parameters already passed in
    sqlcmd.Execute

    ' output string is set equal to the output parameter of the stored procedure
    output = sqlcmd.Parameters("@record_value").Value

    Debug.Print "OUTPUT: " & output ' prints whatever was returned from the SP

    sqlconxn.Close ' Closes the sqlconxn

    Exit Sub

RunTimeError: ' Reportd any errors that might occur in the system and

    Dim strError As String
    strError = "ERROR: " & Err.Number & vbCrLf & Err.Description
    MsgBox strError
    Debug.Print strError

Exit Sub

End Sub

CHECK STATE FUNCTION - this was just connection confirmation

Private Function GetState(state As Integer) As String

    Select Case state
        Case adStateClosed
            GetState = "Closed"
        Case adStateOpen
            GetState = "Open"

    End Select

End Function

SQL STORED PROCEDURE

USE AFCD
GO

ALTER PROCEDURE dbo.GET_ADDITIVE_DATA (@user_index nvarchar(max),
                                       @primary_col_index nvarchar(max),
                                       @secondary_col_index nvarchar(max), 
                                       @data_table_name nvarchar(max),
                                       @record_value nvarchar(max) output
                                       ) AS
BEGIN

    DECLARE @output_value nvarchar(max)
    DECLARE @query nvarchar(max)

    SET @query = 'SELECT @record_value = ' + @primary_col_index  + 
                 ' FROM ' + @data_table_name +
                 ' WHERE ' + @secondary_col_index + ' = ''' + @user_index + ''''

    EXEC sp_executesql @query, 
                       N'@record_value nvarchar(max) output', 
                       @record_value = @record_value output

END

OUTPUT

OUTPUT: 0.284

VBA: How to capture a stored procedure return value

To store the results you can use a recordset.

Dim adoRs As ADODB.Recordset
Set adoRs = ADOQD.Execute

Then you can ask whether the recordset is empty.

isEmpty = (adoRs.BOF And adoRs.EOF) 

run stored procedure and return values from VBA

I believe all you need is this

Dim Rst As ADODB.Recordset

and this

Set Rst = cmd.Execute

How to return a value from a stored procedures in a large global CORP SQL Server 13.xxx from MS Access 2007-2016 with VBA

  Dim strFrom       As String
  Dim strTo         As String
  Dim SumResult     As Currency
  
  strFrom = "'2021-01-22 05:00:00'"
  strTo = "'2021-01-22 13:15:00'"
  
  With CurrentDb.QueryDefs("qryPT")
     .SQL = "EXEC MyGetSum" & strFrom & "," & strTo
     SumResult = .OpenRecordset()(0)
  End With

Now of course the above assumes that you have a stored procedure saved as MyGetSum.

It would look like this:

CREATE PROCEDURE MyGetSum
   @FROM DATETIME2,
   @TO DATETIME2
AS
BEGIN
   SET NOCOUNT ON;
   DECLARE @dt AS TABLE([Timestamp] DATETIME, [Value] INT, [Format] INT, [EquipmentCounterID] NVARCHAR(255)) 

   INSERT INTO @dt 
   EXEC Biop_ProductionCountersInUTC '62','OutputGood', @FROM , @TO 

   SELECT 
   SUM([Value]) AS 'OUTPUT' 
   FROM @dt
END

Now, of course it is VERY possible that you do NOT have a saved stored procedure server side, and for some reason (like world poverty), you have to have all of the code, lines that you have as per above?

Ok, then I would create Two pt queries in Access. The first one is our base t-sql + sql code. You grab that, set the values, and then run it in the 2nd pt query. We again do this, since writing all that in-line t-sql in VBA is a real mess (trying to save world poverty here).

So, I would create the first PT query in Access. You can (have to) type in free form text into the query (but in MS-ACCESS SQL view mode).
So you have this:

DECLARE @FROM AS DATETIME2 
DECLARE @TO AS DATETIME2 

SET @FROM = '@@FROM'
SET @TO = '@@TO'

DECLARE @dt AS TABLE([Timestamp] DATETIME, [Value] INT, [Format] INT, [EquipmentCounterID] NVARCHAR(255)) 

INSERT INTO @dt 
EXEC Biop_ProductionCountersInUTC '62','OutputGood', @FROM , @TO 

SELECT 
SUM([Value]) AS 'OUTPUT' 
FROM @dt

So we place the above in our PT query. We can "think of" this query as our source query.

So, it will look like this in MS-access:

Ok, we save the above as ptSumS (we tag a "s" on the end for source).

In fact the above does not really have to be a PT query, but that will prevent the access query editor from messing with the sql text as we have above.

Now, create the 2nd PT query (that is the query we will actually run).

So, now our code looks like this:

  Dim strFrom       As String
  Dim strTo         As String
  
  Dim SumResult     As Currency
  Dim strSQL        As String
  
  strSQL = CurrentDb.QueryDefs("ptSumS").SQL
  
  Debug.Print strSQL
  
  
  strFrom = "2021-01-22 05:00:00"
  strTo = "2021-01-22 13:15:00"
  
  strSQL = Replace(strSQL, "@@FROM", strFrom)
  strSQL = Replace(strSQL, "@@TO", strTo)
  
  
  With CurrentDb.QueryDefs("qrySum")
     .SQL = strSQL
     SumResult = .OpenRecordset()(0)
  End With

So, either way, using a PT query is the way to go.

The first example - calls a existing stored procedure (so if you have the ability to write + create + save that server side stored procedure, then you can call + use + consume it with the first example.

However, if you don't have the ability to save + create a stored procedure, then we use the 2nd approach. I created the T-SQL as a pt query, since trying to write all that garbage in the VBA editor is too much pain - just type in the t-sql you have into that "handy dandy" pt query. We never run that first PT query - but are using it as a place holder for the t-sql code. It also means we can modify that t-sql - and not have to change the VBA code.

So, we replace the two date parameters , and then run the 2nd example. And by using pt-queries, then we eliminate all the need for connection strings in code (again - really messy in code if try and spew connection string code all over the place).

And while I shoved the results into the currency VBA type, we could for example do this:

  With CurrentDb.QueryDefs("qrySum")
     .SQL = strSQL
  End With

Now RIGHT after above - since we setup the t-sql, we can now run a report, or even a simple access query that is BASED on the above pt "qrySum". From Access point of view, that query will work just like ANY other plane jane query. So right after above, we could launch a report based on qrySum
eg:

docmd.OpenReport "rptMyReport",acViewPreview.

Or we could shove the results into a record set like this:

  Dim rst        As DAO.Recordset
  
  With CurrentDb.QueryDefs("qrySum")
     .SQL = strSQL
     Set rst = .OpenRecordset()
  End With

  debug.print "sum result = " & rst(0)

So you could build up the strSQL in code, and still use the above final code snip, but might as well just type in the whole sql mess into a query, execute a replace on the two values and then shove the resulting sql into that 2nd PT query. We don't modify the first one, since if we "replace" the two date parameters, then after that, how can we know what the last values were - and thus can't use the source query over and over.

However, REGARDLESS, you MUST ADOPT the native 17 or later drivers, since WHEN sql server is using datetime2 (as opposed to datetime), then the standard legacy odbc drivers return datetime2 values as strings - and that is a mess of epic proportions. Same goes for reports and just general use of the data in Access - you REALLY have to ensure that you using the newer native 17 odbc drivers. Or I suppose you can modify the server side table(s) and change the datetime2 columns types to datetime (but that in near all cases is not a practical suggestion as it would mess with existing server side code and queries).

Edit:
Setting up the connection from/inside of Access.

So we have two pass-though queries in Access. The FIRST query as noted is just a place holder for the t-ssql code. We don't need (nor even want) a conneciton string.
To create the first PT query:

It will now prompt for a table - close that dialog.

So, now click on this:

Now change the view to SQL view - with this:

Ok, so now we can paste in our first (source t-sql). That will give us the original screen shot I have in above.

so we have this:

Save the above - we calling it qrySumS

Now, close that, and create our 2nd query.

Again, PT query, but this time do this:

So we display the property sheet. This is WHERE we will setup the conneciton string. (as noted, we could do/have/set the connection string in code - but hey, the UI is less work.

So in the property sheet, click on the connection builder:

This will launch the ODBC connector dialogs - and that is WHERE you can setup + enter the same previous information you shared.

Select new, and then select the ODBC driver.

AS WARNED - try and select a native 17 driver.

You CAN try using "SQL Server" from that list (if you don't have native 17 installed). But that can cause issues with datetime2. "sql server" as a odbc driver is built into ALL copies of windows. If you choose native 17, then you have to install the native 17 driver on all workstations that use the access application.

Ok, so work though the dialogs.

In your case, the server name + sql instance looks to be:

SQLDKBA185

and then user + password

And DO NOT skip the database selection - often so many do!!!

This one:

When all is said and done, you wind up back to the original starting point, but with the name of the connection you typed in

And along the way, there is a "test connection" - do try it.

so, you COULD type in, or even cut+paste in the connection string, but the above connection builder (gee, I use it 10 times a day), should result in this:

Now, this 2nd query can be left blank, since our VBA code fills it out (copies from the first query - replaces the params. We save and called this qrySum in our example code.

So you can type/paste in the connection string. and you can of course use VBA code, but it makes the most sense to just use the connection builder. Note the syntax is VERY similar to the .net connection - but is often a wee bit different - so that's why I suggested to use the connection builder. (we using ODBC as opposed to .net sql provider - but you can often look at one, and translate - re-type to the format required by Access (ODBC format).

Edit #2
If you can determine that the datetime columns are NOT datetime2, then change the t-sql code to use datetime, and you CAN USE the "sql driver" and thus not have to adopt + install native 17 (or later) odbc drivers on each workstation that is to use this application. So, if you use "sql server", then this should/could work on all workstations WITHOUT having to install the later native 17 drivers. This however IS REQUIRED if the table(s) in question on the server ARE using datetime2 as opposed to datetime columns. Do NOT ignore this advice here - much pain and suffering will result if you don't address the datetime2 vs datetime issue.

Executing an SQL Server stored procedure using VBA in Excel and passing a variable

Add a parameter to the command

Option Explicit

Sub reverse_posted()

    Const PROC = "ashcourt_balfour_reverse_posting"

    Dim con As ADODB.Connection, cmd As ADODB.Command, i As Long
    i = InputBox("Invoice Number to be re-posted")
     
    Set con = New ADODB.Connection
    con.Open "Provider=SQLOLEDB;Data Source=ashcourt_app1;" & _
             "Initial Catalog=ASHCOURT_Weighsoft5;" & _
             "Integrated Security=SSPI;Trusted_Connection=Yes;"
    
    Set cmd = New ADODB.Command
    With cmd
        .ActiveConnection = con
        .CommandType = adCmdStoredProc
        .CommandText = PROC
        .Parameters.Append .CreateParameter("P1", adInteger, adParamInput)
        .Execute , i
    End With

    con.Close
    Set con = Nothing
End Sub

How to get Return Value of a Stored Procedure

Add a parameter, using ParameterDirection.ReturnValue. The return value will be present in the paramter after the execution.

---

Related Topics