Returning the id of an inserted record

[Nathan Sokalski]

I am writing an ASP.NET application, and am forced to use Access as my
database. I need a way to return the id (which is an AutoNumber field in the
table I am inserting into) of a record when I insert it. In SQL Server, I am
able to use a statement like the following:

INSERT INTO tablename (fieldnames) OUTPUT inserted.id VALUES(valuestoinsert)

Take note of the OUTPUT inserted.id part of this statement. This returns the
value of the field named 'id' for the record that was just inserted.
Unfortunately, this technique does not work in Access. Is there any way to
retrieve the id of the record that was just inserted when using Access? Any
help would be appreciated. Thanks.

 

[Kerry Moorman]

Nathan,

After the insert statement you need to do a Select @@Identity statement to
retrieve the generated ID value.

Kerry Moorman

 

[Andrew J. Kelly]

I wasn't aware that @@Identity was also in Access so you may want to post
this question in an ACCESS news group not a SQL Server one. And for future
reference you should not use @@IDENTITY in SQL Server, you should use
SCOPE_IDENTITY() instead for this type of situation.

 

[Mike C#]

I wasn't aware that @@Identity was also in Access so you may want to post
this question in an ACCESS news group not a SQL Server one. And for future
reference you should not use @@IDENTITY in SQL Server, you should use
SCOPE_IDENTITY() instead for this type of situation.

Access supports it only through the Jet OLEDB 4.0 provider:
http://support.microsoft.com/kb/815629

You're right though, he'll probably more authoritative answers from the
Access newsgroup.