Return an Autonumber after inserting 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.

 

[PC Datasheet]

The autonumber for a new record that has been saved is the highest
autonumber in your table. Get it like this:
HighestAutonumber = DMax("[NameOfAutonumberField]","NameOfTable")

 

[Allen Browne]

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

 

[PC Datasheet]

Allen,

What is @@IDENTITY in your code? What does the @@ do?

Steve
PC Datasheet

 

[Allen Browne]

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.

 

[Douglas J. Steele]

Not necessarily. What if he's using random autonumbers?

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

The autonumber for a new record that has been saved is the highest
autonumber in your table. Get it like this:
HighestAutonumber = DMax("[NameOfAutonumberField]","NameOfTable")

--
PC Datasheet
Your Resource For Help With Access, Excel And Word Applications
(e-mail address removed)
www.pcdatasheet.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.

 

[Guest]

Many thanks Allen.

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