Re: Return an Autonumber after inserting a new record

Tech Tip: Click here to run a free scan for Windows Errors and optimize PC performance

From: Allen Browne (AllenBrowne_at_SeeSig.Invalid)
Date: 01/21/05 

Date: Fri, 21 Jan 2005 12:29:35 +0800

As per the original post, SQL Server has an @@Identity that returns the
primary key value associated with a record. This feature was quietly
introduced into JET 4.

The implementation is a bit different, but works as in the example, where
you can query the @@Identity associated with the database variable that you
most recently executed an action query on.

In Access 97 and earlier, this was not available, so you had to use DAO to
AddNew, and use the Bookmark of the LastModified record. 

-- 
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.
"PC Data***" <nospam@nospam.spam> wrote in message
news:8d%Hd.1376$YD5.1280@newsread3.news.pas.earthlink.net...
> Allen,
>
> What is @@IDENTITY in your code? What does the @@ do?
>
> Steve
> PC Data***
>
>
> "Allen Browne" <AllenBrowne@SeeSig.Invalid> wrote in message
> news:#7M75q2$EHA.600@TK2MSFTNGP09.phx.gbl...
>> Works in JET 4 (Access 2000 and later):
>>
>> 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
>>
>> --
>> 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.
>>
>> "Donz" <Donz@discussions.microsoft.com> wrote in message
>> news:9AF7AB7C-8052-4048-8D18-23A5CE2D8595@microsoft.com...
>> >I created a query in an Access 2000 database to add a new record.  I 
>> >want
>> >to
>> > return the newly added Primary Key (Autonumber) so I can use it as a
>> > foriegn
>> > key in another table.
>> >
>> > I've done the following in SQL 2000:
>> >
>> > Create Procedure dbo.sp_InsertName(@FName varchar(30), @LName
> varchar(30),
>> > @NameID as int output)
>> > Insert into Name (Fname, LName) Values(@FName, @LName)
>> > select @NameID = @@Identity
>> >
>> > How can I do this in Access?
>> >
>> > Your help would be greatly appreciated.
Quantcast