Re: Returning Value on INSERT

[Paul Shapiro]

"Patrick Pohlmann" <ppent...@entfernendpcon.de> wrote in message
news:055FE503-0F89-44AC...@microsoft.com...
> 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,

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]

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

"Paul Shapiro" <pa...@hideme.broadwayData.com> schrieb im Newsbeitrag
news:u0DmoaIt...@TK2MSFTNGP02.phx.gbl...

[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)

"Patrick Pohlmann" <ppent...@entfernendpcon.de> wrote in message

news:%23zU4ZlI...@TK2MSFTNGP04.phx.gbl...

[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

"Patrick Pohlmann" <ppent...@entfernendpcon.de> wrote in message

news:%23zU4ZlI...@TK2MSFTNGP04.phx.gbl...

[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

>> On Tuesday, February 23, 2010 7:49 AM Paul Shapiro wrote:

>> Your stored procedure can return the new ID as an output parameter. Here is

>> 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.

>>> On Tuesday, February 23, 2010 8:03 AM Patrick Pohlmann wrote:

>>> 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

>>>> On Tuesday, February 23, 2010 1:33 PM Sylvain Lafontaine wrote:

>>>> 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)

>>>>> Submitted via EggHeadCafe
>>>>> Creating a SharePoint Designer workflow
>>>>> http://www.eggheadcafe.com/tutorials/aspnet/4b58a093-2c77-4568-81f0-f31feee2704a/creating-a-sharepoint-designer-workflow.aspx