Returning Value on INSERT

  • Thread starter Thread starter Patrick Pohlmann
  • Start date Start date
P

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
 
Patrick Pohlmann said:
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?
Click to expand...

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.
 
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
 
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)
 
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
 
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
 
Back
Top