Return an autonumber field after adding a new record.

[Guest]

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.

 

[Allen Browne]

This 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
End Function

 

[Tim Ferguson]

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

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

My understanding is that @@IDENTITY only returns the last identity number
allocated -- to any table, to any user --, so that this method may not be
safe in a multi-user scenario. Someone else could run the same code (or
similar on another table) and you would get his new number instead of
your own.

I'm not completely convinced that running it as a stored procodure is
guaranteed atomic.

If you are paranoid about these things, the only _completely_ safe method
is using DAO (or ADO equivalent) sic:-

strSQL = "SELECT AutonumFld, ReqdFld FROM MyTable WHERE FALSE"
set rs = db.Openrecordset(strSQL, dbOpenDynaset, etc)
With rs
.AddNew ' create the new record
!ReqFld = "Default Value" ' fill in required fields
dwNewRecord = !AutonumFld ' get the new value
.Update ' save it all
End With
rs.Close

and then nobody else can interfere with it.

Hope that helps


Tim F

 

[Van T. Dinh]

In SQL Server, I tend to use @@SCOPE_IDENTITY rather than @@IDENTITY.

However, Allen's answer was for JET and I used both methods (Allen and
yours) and never had any problem in JET/Access.

 

[Tim Ferguson]

In SQL Server, I tend to use @@SCOPE_IDENTITY rather than @@IDENTITY.

However, Allen's answer was for JET and I used both methods (Allen and
yours) and never had any problem in JET/Access.

Fairy nuff... I'll go and look up @@scope_identity.

all the best


Tim F