Get SP to return a value to VBA

[LeanneGodney]

Hi there,

I am working with an example stored procedure (will type it out below). The procedure inserts a record into a table and then theoretically returns the ID number that it created when it inserted the new record. I can get this to work just fine in the Query Analyser screen - it runs, and it then spits out what the ID is. But for the life of me I can't figure out how to execute this stored procedure from VBA and have it return the ID to me.

If anyone knows I will forever be in your debt! ;0)

----
ALTER PROCEDURE dbo.procShipperInsert
@CompanyName nvarchar(40),
@Phone nvarchar(24),
@ShipperID int OUTPUT
AS

INSERT INTO Shippers (CompanyName, Phone)
VALUES (@CompanyName, @Phone)

SELECT @ShipperID = @@Identity

 

[LeanneGodney]

I've sort've figured it out myself! Yippeee! Thought I'd pop it in here incase anyone else has this problem.

You simply create your stored procedure as you would usually, but finish it of with "RETURN @@Identity".

Then in VBA when you call it you do this:

Dim c As new ADODB.Command

With c
.ActiveConnection = CurrentProject.Connection
.CommandType = adCmdStoredProc
.CommandText = "sproc_AddClient"
.Parameters("@TradeClientID") = 503
.Execute
TheID = .Parameters(0)
End With

c.Cancel

End Sub

 

[cmmrfrds]

Use the ADO Command object.

There is an example in this thread of returning a recordset.
Thread958-1126074

You don't need a recordset for what you want. Since there is only 1 output parameter, define 1 output parameter and then it can be referred to by name or by relative position.

relative position.
assume cmd= Command.
debug.Print cmd(0)