Receive value back from SQL Server Stored Procedure

[Ira]

Calling the store procedure from Access:

Dim qdef As DAO.QueryDef
Set qdef = CurrentDb.CreateQueryDef("")
qdef.Connect = CurrentDb.TableDefs("Clients").Connect
qdef.SQL = "EXEC dbo.AddClient 'one' , 'two', 'three', 'four'" 'is there a better way to pass the argments? Recordset?
qdef.ReturnsRecords = False ''avoid 3065 error
qdef.Execute


''''How do I get back the value of last_id_inserted ??


The stored procedure:

ALTER PROCEDURE [dbo].[AddClient]
-- Add the parameters for the stored procedure here


@BillName varchar(50),
@BillStreet varchar(50),
@BillCity varchar(50),
@BillState varchar(2)
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;


-- Insert statements for procedure here
INSERT INTO Clients
(BillName, BillStreet, BillCity, BillState)
VALUES
(@BillName, @BillStreet, @BillCity, @BillState)

END
DECLARE @last_id_inserted int;


SET @last_id_inserted = SCOPE_IDENTITY();
RETURN @last_id_inserted;

[pbaldy]

I've used an ADO Command object in this type of situation. Your SP does return a value, so an untested idea is to change the ReturnsRecords to true and open the query instead of executing it.