Returning Value on INSERT

[Patrick Pohlmann]

Hi,

I am using a Stored Procedure to insert a new record into a table. I am
accessing this Stored Procedure via VBA in my Access adp like this:

cnn.Execute "exec MyProcedure MyParameter1, MyParameter2"

Can anybody tell my how to get the new Indentity ID after INSERT to use it
within Access?

Thank you for your help.

Regards

Patrick

 

[Paul Shapiro]

I am using a Stored Procedure to insert a new record into a table. I am
accessing this Stored Procedure via VBA in my Access adp like this:

cnn.Execute "exec MyProcedure MyParameter1, MyParameter2"

Can anybody tell my how to get the new Indentity ID after INSERT to use it
within Access?

Your stored procedure can return the new ID as an output parameter. Here's
an example from Books OnLine:
CREATE PROCEDURE dbo.InsertCategory
@CategoryName nvarchar(15),
@Identity int OUT
AS
INSERT INTO Categories (CategoryName) VALUES(@CategoryName)
SET @Identity = SCOPE_IDENTITY()

BOL has more info under SCOPE_IDENTITY.

 

[Patrick Pohlmann]

Hi Paul,

thanks a lot. Yes, SCOPE_IDENTITY is what I need.

But how can I get the value into VBA? I presume cnn.Execute will not return
any values, would it?

Thanks again for help.

Best regards

Patrick

 

[Sylvain Lafontaine]

Two possible solutions. The first one would be to simply a Select statement
at the end of the SP in order to return the desired values and you could
catch this result into a Recordset on the VBA side. Note that you will add
to add the statement « SET NOCOUNT ON » at the very beginning of your SP.

The second possibility would be to use an ADO Command object to pass and
retrieve the parameters. This would be the most normal way of doing this.
There are multiple examples on the Internet on how to use the Command object
of ADO.

--
Sylvain Lafontaine, ing.
MVP - Windows Live Platform
Blog/web site: http://coding-paparazzi.sylvainlafontaine.com
Independent consultant and remote programming for Access and SQL-Server
(French)

 

[Bob McClellan]

I agree with Sylvain. ADO is the way to go..
something like this should work..

dim ReturnScopeID as long

Dim oCmd As Command, param As Parameter
Dim cn As New ADODB.Connection, sqlString As String

sqlString = "ReturnValExample"
Set oCmd = New ADODB.Command
Set cn = CurrentProject.Connection
Set oCmd.ActiveConnection = cn
oCmd.CommandText = sqlString
oCmd.CommandType = adCmdStoredProc
oCmd.CommandTimeout = 15

'Param1
Set param = New ADODB.Parameter
param.Type = adInteger
param.Direction = adParamInput
param.Value = Me.p1
param.Name = "Param1"
oCmd.Parameters.Append param

'Param2
Set param = New ADODB.Parameter
param.Type = adInteger
param.Direction = adParamInput
param.Value = Me.p2
param.Name = "Param2"
oCmd.Parameters.Append param

'Return ScopeID
Set param = New ADODB.Parameter
param.Type = adInteger
param.Direction = adParamOutput
param.Name = "scID"
oCmd.Parameters.Append param

oCmd.Execute , , adExecuteNoRecords
ReturnScopeID = ocmd.parameters("scID")
cn.Close
set cn = nothing

hth,
...bob

 

[Dave Engle]

I have been looking all over the place for a working example. All I needed was the return value from the SPROC since all it does it assigns the next client id no. Thank you Bob!

Have A Great Thanksgiving Every One! Mine, I know will be better since this part is done in my ADP