What is the pkey of an INSERT into an autonumber?

[Maury Markowitz]

Is there some way to find out the pkey generated by an autonumber after
INSERTing into that table? I need to insert a record and then SELECT a few
fields from it, but I have no idea what the pkey is so I don't know who to
select against.

Does INSERT return a handle, or is there somewhere I can look to find this
number?

 

[Allen Browne]

In Access 2000 and later, you can use @@IDENTITY to get the primary key
value last inserted.

Example:
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

The alternative is to open a recordset and use AddNew and Update. The
recordset then knows about the new record, so you can get the primary key
value.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

message
news:[email protected]...

 

[Maury Markowitz]

Thanks Allen, worked like a champ.

 

[Maury Markowitz]

Actually, check this out:

http://www.databasejournal.com/features/mssql/article.php/10894_3307541_2

I went poking about on the 'net after you gave me that pointer, and it
appears it can lead to problems that this other method avoids.

 

[Van T. Dinh]

If you post questions in these newsgroups, you should specify that you use
MS SQL Back-End. Otherwise, almost all replies will assume JET Back-End and
the answers apply to JET and not necessarily MS SQL.

In fact, you even used Access terminology "AutoNumber" (MS SQL Server uses
"Identity").