Assign a stored procedure return value to a VBA variable
ms-access, sql-server, stored-procedures, vba
Solution
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).
Problem
This should be real easy, but I haven't found a real concise answer yet. I have a very simple stored procedure in sql server that returns an integer value. All I want to do is get that return value into a variable for use in Access. Stored Procedure: ``` ALTER PROCEDURE [dbo].[out_GetNextID] @NextSumID integer AS BEGIN -- SET NOCOUNT ON added to prevent extra result sets from -- interfering with SELECT statements. SET NOCOUNT ON; -- Insert statements for procedure here SELECT @NextSumID = IDENT_CURRENT('Outage Summary')+IDENT_INCR('Outage Summary') RETURN @NextSumID END ``` I am using ADODB to execute the stored procedure, and I feel dumb for having to ask this, but how do I access the return value in Access after I run cmd.Execute? Thanks in advance and sorry for the lame question.