Retrieving output parameter from passthrough query in MS Access VBA? PT query execs. a stored procedure inserting a new record, I need to get its id

Is there any way to run a stored procedure, using a passthrough query, that has input and output parameters? If so, how can I retrieve the output parameter value in MS Access using VBA?

The stored procedure in question inserts a new record and I need to get the newly created record id.

This is my stored procedure:

ALTER PROCEDURE [dbo].[sp_two_Insert]
    @woTitle nvarchar(255),
    @woWriter int,
    @woDate date,
    @woid int = 0 OUTPUT
AS
BEGIN
	SET NOCOUNT ON;

    INSERT INTO dbo.two (woTitle, writtenby, wodate)
	VALUES (@woTitle, @woWriter, @woDate);
    
	SELECT @woid = SCOPE_IDENTITY();
END

Thank you all in advance!

I tried these snippets of code:

Attempt #1:
https://stackoverflow.com/a/46086366/9604983
from Gord Thompson

VBA crashes every time at the line: Set rs = ptq.OpenRecordset, it gets stuck creating new records indefinitely, so I have to exit the program through the task manager.

Attempt #2:
https://answers.microsoft.com/en-us/msoffice/forum/all/pass-through-query-with-output-parameter/8d0c69dd-846d-4a02-8844-28d0e05dbf6a

But, equally to attempt #1, MS Access crashes at the Set rs = rs.NextRecordset, with the same consequences.

I don't think the solution given by galla_ under "Replies(1)" section, will work for me , since I need the id of the record created by the insert query, and running a second stored procedure, right after the insert, will certainly get the id of the last record created, but not necessarily the id of the record created by the insert stored procedure that run in the first place.

Here is the VBA code that crashes:

Option Compare Database
Option Explicit

Sub ptqTest()

Dim cdb As DAO.Database
Dim rs As DAO.Recordset

Set cdb = CurrentDb
Set rs=cdb.OpenRecordset("qPtMyPassThroughQ") ‘this is where Ms 
Access crashes

rs.Close
Set rs = Nothing
Set cdb = Nothing

End Sub

Ok, you have some errors here.

If you want to use or set an output parameter, then your code should be written like this:

CREATE PROCEDURE [dbo].[AddHotel]
	@FirstName nvarchar(50),
	@LastName nvarchar(50),
	@HotelName nvarchar(50),
	@newPK int OUTPUT

AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;

	INSERT INTO tblHotelsA (FirstName, LastName, HotelName)
						  VALUES (@FirstName, @LastName, @HotelName)

	SET @newPK = SCOPE_IDENTITY()

END

Note how the output value IS NOT created by a select. So, the above stored procedure does an insert and THEN sets the OUTPUT parameters.

However, really, I don't see the need, nor advantage of using output values, since it is far easier to return values using SELECT.

So, the only use case I can think of for using (and returning) OUTPUT parameters to MS-access?

That would be a "use case" in which the stored procedure was written by someone else and thus is part of some set of existing T-SQL code routines (and those other routines expect and use OUTPUT parameters). In other words, I would not write the stored procedure that way when it is YOUR choice.

However, let's still show how you can get/use/enjoy that return value, since you may well in your software travels encounter the above use case.

So, say the above stored procedure.

So, the MS-Access VBA code can be written this way:

The work around involves "wrapping" the stored procedure call in 2 extra lines of code. (a T-SQL variable declare, and a SELECT statement to turn that output value into a DAO recordset return).

So, this will work for above:

Dim newPK       As Long

Dim strSQL      As String
Dim sFirstName  As String
Dim sLastName   As String
Dim sHotelName  As String

sFirstName = "Albert"
sLastName = "Kallal"
sHotelName = "My Cool Hotel"

 strSQL = "declare @MyPK int;" & _
          "exec dbo.AddHotel " & _
          "@FirstName = '" & sFirstName & "'," & _
          "@LastName = '" & sLastName & "'," & _
          "@HotelName = '" & sHotelName & "'," & _
          "@newPK = @MyPK OUTPUT;" & _
          "SELECT @MyPK AS MyNewPKTest;"

With CurrentDb.QueryDefs("qryPassR")

    .ReturnsRecords = True
    .SQL = strSQL
    newPK = .OpenRecordset()(0)

End With

Now, NOTE BEYOND CLOSE in above:

Note how the T-SQL syntax for a return value is "backwards". This is not a MS-Access face plant, but is the confusing syntax required for T-SQL. The parameter value returned remains on the LEFT SIDE of the above expression!

As I noted, the above is a workaround ONLY required if you are stuck with an existing stored procedure that you can't change.

However, one would write the above stored procedure this way:

CREATE PROCEDURE [dbo].[AddHotel]
	@FirstName nvarchar(50),
	@LastName nvarchar(50),
	@HotelName nvarchar(50)

AS
BEGIN
	SET NOCOUNT ON;

	INSERT INTO tblHotelsA (FirstName, LastName, HotelName)
						  VALUES (@FirstName, @LastName, @HotelName)

	select SCOPE_IDENTITY()

END

So, in above, we don't use an output parameter (and don't need one). And in place of a SET command to set the return (output) value?

We use a simple SELECT statement, and that results in a single standard DAO data row being returned.

So, our VBA code now looks like this:

 strSQL = "exec dbo.AddHotel " & _
          "@FirstName = '" & sFirstName & "'," & _
          "@LastName = '" & sLastName & "'," & _
          "@HotelName = '" & sHotelName & "'"
              
With CurrentDb.QueryDefs("qryPassR")

    .ReturnsRecords = True
    .SQL = strSQL
    newPK = .OpenRecordset()(0)

End With

Or, it could even be written in this format, but then caution is required since parameter order matters.

So, this syntax:

 strSQL = "exec dbo.AddHotel '" & _
          sFirstName & "','" & sLastName & "','" & sHotelName & "'"
              
With CurrentDb.QueryDefs("qryPassR")

    .ReturnsRecords = True
    .SQL = strSQL
    newPK = .OpenRecordset()(0)
    

End With

And note the short hand I am using for the return recordset.

This line of code:

    newPK = .OpenRecordset()(0)

could be written in long format as this:

 dim rstReturn    as DAO.RecordSet

 ... same above code here, and then

     set rstReturn = .OpenRecordSet
End With

Then to get the value, you use:

 rstReturn(0)   ' first column.

Or, you could use the returned column name from the T-SQL statement.

rstReturn!newPK, or rstReturn("newPK")

And again if you going to use a column name for the return value, then make sure you set that column in the T-SQL

eg this:

	select SCOPE_IDENTITY() as newPK

So, VBA DAO does not directly support T-SQL return (output) parameters.

However, it is rare they are warranted, and the above first work around only is required when you can't change the T-SQL routines.

Note also how I use an exiting created pass-though query, and thus the connection setup is assumed to have already been created and setup for that Access pass-though query.