how to pass the scope_identify value back to Access form?

[childrenfirst]

Hi,

I have a stored procedure that runs after Access form passes the global variables to SQL. New records will be inserted into a SQL table at the end of the stored procedure and the primary key for the table is an auto-id. I would like to pass the auto-id inserted into this SQL table back to the Access form, but I am not sure how to do it.

Right now, my stored procedure looks like this:

CREATE PROCEDURE s_SelectRandomClaim

@globalvariable1 int,
@globavariable2 varchar(20)

AS

@reportnum

blah... blah...
blah... blah...


--return value of the newly inserted row in identify field in ClickonAuditlist table
SELECT @ReportNum = SCOPE_IDENTITY()

GO

My problem is that how do I retrieve the value of @Reportnum from vba Access? I have never written any vba code to do this job before and really have no idea. Any help will be greatly appreciated!!

Thanks a bunch!

 

[ESquared]

This question needs to be posted in the Access or VB database forum. It is not specific to SQL Server. No more replies here, please.

What you need is help using and returning Parameters from an ADO Command object. The answer is probably already in those forums somewhere. Search for the Parameter object's Direction property, specifically adParamReturnValue. If you don't find what you're looking for, then ask your question in the Access or VB database forum!

I hope this helps you.

 

[mrdenny]

Your stored procedure has not retured the value back to the calling code. You have only placed the SCOPE_IDENTITY() value into a variable. You either need to return it as a recordset by adding a SELECT @ReportNum to the bottom of the stored procedure then pull that record set from you calling access code (not very pretty to do that way) or add @ReportNum as a paramater of the stored procdure but as an OUTPUT variable. Then when you call the procedure you need to tell your calling code about the variable and that it's direction is outbound. Then when the procedure is done the calling code's variable will have the value of the @ReportNum variable.

The T/SQL procedure will then look like this.

CREATE PROCEDURE s_SelectRandomClaim

@globalvariable1 int,
@globavariable2 varchar(20)[red],
@ReportNum int OUTPUT[/red]

AS

blah... blah...
blah... blah...


--return value of the newly inserted row in identify field in ClickonAuditlist table
SELECT @ReportNum = SCOPE_IDENTITY()

GO

As for the calling code, you'll need to check with the Access folks on that. This should take care of the SQL Server part of your question. You'll want to ask them how to get the value of an OUTPUT variable when calling a SQL Server stored procedure.

Denny
MCSA (2003) / MCDBA (SQL 2000) / MCTS (SQL 2005) / MCITP Database Administrator (SQL 2005)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com

 

[ESquared]

I should have been more clear. Since the return value in question is an int, instead of using OUTPUT parameters you can use the RETURN keyword.

RETURN Scope_Identity()

or

RETURN @ReportID

This kind of value is returned as a parameter in ADO. If you examine the parameter collection of your command object after executing it, you'll find one parameter of type adParamReturnValue with this in it.

Be aware that sometimes you will need to use mrdenny's method of using an OUTPUT parameter if the program you're using interprets non-zero RETURN values as errors, which some, very stupidly, do. (I think MS Access sometimes has a problem with this, but not always.) In any case, make sure to use the OUTPUT keyword in your command or make sure to identify the parameter as OUTPUT before executing.