How to retrieve the autonumber after an insert?



Hi, in MS SQL after inserting a record in a table, the newly generated
identity value for the record can be retrieved from the @@IDENTITY variable,
and returned to the calling code.

The equivalent of the identity in Access is the autonumber value. How can
the SQL code retrieve the newly generated autonumber after an insert?

Thanks in advance,


Allen Browne

This example works only in Access 2000 and later, and is designed for JET
tables. The important concept is that it is a property of the Database
variable you used to execute the Append query.

Function ShowIdentity() As Variant
Dim db As DAO.Database
Dim rs As DAO.Recordset

Set db = DBEngine(0)(0)
db.Execute "INSERT INTO MyTable ( MyField ) SELECT 'nuffin' AS Expr1;"

Set rs = db.OpenRecordset("SELECT @@IDENTITY AS LastID;")
ShowIdentity = rs!LastID

Set rs = Nothing
Set db = Nothing
End Function

Carl Rapson

Hi Richard,

Allen gave you one example of how to do this above. For myself, I prefer
(when possible) to insert records by opening the table as a recordset and
using the .AddNew method:

set rs = db.OpenRecordset("MyTable")
newID = rs.Fields![ID]
' Set record fields here
rs.Fields![Field1] = "value"

Using this method, right after the .AddNew I fetch the autonumber field from
the newly added record. I also prefer this method because it lets me only
insert those fields I want, without having to use a lot of logic to build an
INSERT string.


Carl Rapson

Tim Ferguson

set rs = db.OpenRecordset("MyTable")

Or, if you want to stay friend with your network manager,

Set rs = db.OpenRecordset( _
"SELECT * FROM MyTable WHERE FALSE", dbOpenDyanaset, etc _

You really don't want to read 10,000 records just in order to add one...

All the best

Tim F

Carl Rapson

Good point, thanks. I pulled that without thinking from a small db I keep on
my system.


Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question