How to retrieve the autonumber after an insert?

[Guest]

Hi, in MS SQL after inserting a record in a table, the newly generated
identity value for the record can be retrieved from the @@IDENTITY variable,
and returned to the calling code.

The equivalent of the identity in Access is the autonumber value. How can
the SQL code retrieve the newly generated autonumber after an insert?

Thanks in advance,

Rick

 

[Allen Browne]

This example works only in Access 2000 and later, and is designed for JET
tables. The important concept is that it is a property of the Database
variable you used to execute the Append query.

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

 

[Carl Rapson]

Hi Richard,

Allen gave you one example of how to do this above. For myself, I prefer
(when possible) to insert records by opening the table as a recordset and
using the .AddNew method:

set rs = db.OpenRecordset("MyTable")
rs.AddNew
newID = rs.Fields![ID]
'...
' Set record fields here
rs.Fields![Field1] = "value"
'...
rs.Update

Using this method, right after the .AddNew I fetch the autonumber field from
the newly added record. I also prefer this method because it lets me only
insert those fields I want, without having to use a lot of logic to build an
INSERT string.

HTH,

Carl Rapson

 

[Tim Ferguson]

set rs = db.OpenRecordset("MyTable")

Or, if you want to stay friend with your network manager,

Set rs = db.OpenRecordset( _
"SELECT * FROM MyTable WHERE FALSE", dbOpenDyanaset, etc _
)



You really don't want to read 10,000 records just in order to add one...


All the best


Tim F

 

[Carl Rapson]

Good point, thanks. I pulled that without thinking from a small db I keep on
my system.

Carl