Retrieve The ID of the newly inserted Record

[Samuel]

When using SQL Server I add SELECT @@IDENTITY at the end of the insert
query, what can I do in Access

Thank you,
Samuel

 

[Allen Browne]

In Access 2000 or later, you can do this:

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
rs.Close

Set rs = Nothing
Set db = Nothing
End Function

In any version of Access, you can OpenRecordset and AddNew instead of
executing an append query. It's trivial to get the AutoNumber value that
way.

 

[Samuel]

1. Why is this 'SELECT 'nuffin' AS Expr1' in the first query

2.In a multi user environment is there no a chance that another user will
add a record between the 2 calls


Thank you,
Samuel

 

[Allen Browne]

Re 1:
It's just a silly example of an INSERT statement - one returns a piece of
text rather than referring to some table. From your post, I assume you know
how to create an append query statement. (Append on Query menu in query
design view, if you want Access to generate the statement for you.)

Re 2:
Yes: it may be possible. The OpenRecordset and AddNew is safer.

 

[Samuel]

I use ODBC provider of .NET
What would be the equivlant of the OpenRecordset and AddNew ?

Regards,
Samuel

 

[Allen Browne]

Perhaps someone who uses .NET can answer this.

OpenRecordset works with several providers, but it may depend on the context
you are working from.