Stored procedure problem in MS Access caused by ReturnsRecords (accdb)

I have a relatively simply stored procedure that runs an insert, and then attempts to return the last inserted ID. This is done so I can the ID via SCOPE_IDENTITY(). This was working great for me. But then, I got reports that on some machines, the stored proc would cause duplicate results.

After investigating it, I found that the cause was the use of the property ReturnsRecords. When true, it will run a query twice! For a select; who cares. For this c开发者_StackOverflow中文版ase though, it is causing duplicates in my database.

Setting ReturnsRecords to false gets rid of the problem, but then it defeats the purpose of the stored proc (I absolutely must get the proper last inserted ID for the record)!

My question is simply this: How would I go about inserting this record and getting the ID of the new record, while getting around this problem?

Additional Info:

• I am currently using DAO
• I have tried the ADO.Command method, but it is very error prone and doesn't seem to work with output parameters for me.
• I am using the stored proc solely for the purpose of retaining scope. I do not have my heart set on using a stored proc. I simply need a reliable way to get the id of the last inserted row.
• This is an ACCDB
• This is happening in access 2007
• my DB backend is MSSQL Server 2008

Any help or insight is appreciated.

---

One of your parameters in the procedure can be set to output. Still don't return any rows, but set the value of that parameter to Scope_Identity()

create proc ReturnTheNewID 
  @NewValue int
  , @ReturnNewID int output
as

set nocount on

insert ....

set @ReturnNewID = Scope_identity()