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.

--
Allen Browne - Microsoft MVP. Perth, Western Australia
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"Samuel" <samuel....@ntlworld.com> wrote in message
news:%23CAmOK0...@TK2MSFTNGP02.phx.gbl...

[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" <Allen...@SeeSig.Invalid> wrote in message
news:eOlRBp0i...@TK2MSFTNGP03.phx.gbl...

[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.

--
Allen Browne - Microsoft MVP. Perth, Western Australia
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"Samuel" <samuel....@ntlworld.com> wrote in message

news:OHVA3AB...@TK2MSFTNGP04.phx.gbl...

> 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" <Allen...@SeeSig.Invalid> wrote in message
> news:eOlRBp0i...@TK2MSFTNGP03.phx.gbl...
>> 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]

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

Regards,
Samuel

"Allen Browne" <Allen...@SeeSig.Invalid> wrote in message

news:eLMboAF...@TK2MSFTNGP06.phx.gbl...

[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.

--
Allen Browne - Microsoft MVP. Perth, Western Australia
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"Samuel" <samuel....@ntlworld.com> wrote in message

news:OKqqAAKj...@TK2MSFTNGP04.phx.gbl...

>I use ODBC provider of .NET
> What would be the equivlant of the OpenRecordset and AddNew ?
>
> Regards,
> Samuel
>
> "Allen Browne" <Allen...@SeeSig.Invalid> wrote in message
> news:eLMboAF...@TK2MSFTNGP06.phx.gbl...
>> 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.
>>