Autonumber value of last inserted row - MS Access / VBA

Question

I have a JET table with an auto-number as the primary key, and I would like to know how I can retrieve this number after inserting a row. I have thought of using MAX() to retrieve the row with the highest value, but am not sure how reliable this would be. Some sample code:

Dim query As String
Dim newRow As Integer
query = "INSERT INTO InvoiceNumbers (date) VALUES (" & NOW() & ");"
newRow = CurrentDb.Execute(query)

Now I know that this wouldn't work, since Execute() won't return the value of the primary key, but this is basically the kind of code I am looking for. I will need to use the primary key of the new row to update a number of rows in another table.

What would be the simplest / most readable way of doing this?

Answer 1

If DAO use

RS.Move 0, RS.LastModified
lngID = RS!AutoNumberFieldName

If ADO use

cn.Execute "INSERT INTO TheTable.....", , adCmdText + adExecuteNoRecords
Set rs = cn.Execute("SELECT @@Identity", , adCmdText)
Debug.Print rs.Fields(0).Value

cn being a valid ADO connection, @@Identity will return the last Identity (Autonumber) inserted on this connection.

Note that @@Identity might be troublesome because the last generated value may not be the one you are interested in. For the Access database engine, consider a VIEW that joins two tables, both of which have the IDENTITY property, and you INSERT INTO the VIEW. For SQL Server, consider if there are triggers that in turn insert records into another table that also has the IDENTITY property.

BTW DMax would not work as if someone else inserts a record just after you've inserted a record but before your Dmax function finishes excecuting then you would get their record.

Answer 2

In your example, because you use CurrentDB to execute your INSERT you've made it harder for yourself. Instead, this will work:

  Dim query As String
  Dim newRow As Long  ' note change of data type
  Dim db As DAO.Database

  query = "INSERT INTO InvoiceNumbers (date) VALUES (" & NOW() & ");"
  Set db = CurrentDB
  db.Execute(query)
  newRow = db.OpenRecordset("SELECT @@IDENTITY")(0)
  Set db = Nothing

I used to do adds by opening an AddOnly recordset and picking up the ID from there, but this is a lot more efficient. And, note, Tony, that it doesn't require ADO.

Answer 3

Hello,

This is an adaptation from my code for you. I've adapted it in this editor, so there may be some syntax error. I was inspired from developpez.com (Look in the page for : "Pour insérer des données, vaut-il mieux passer par un RecordSet ou par une requête de type INSERT ?"). They explain (with little some word in french) this way is very faster than the one upper. In the exemple, this way is 37 times faster. Try it.

Const strTableName As String = "InvoiceNumbers"
Const strColumnIdName As String = "??"
Const strColumnDateName As String = "date"

Dim recordSet As DAO.recordSet

Set recordSet = CurrentDb.OpenRecordset(strTableName)
Call recordSet.AddNew
lngId = CLng(recordSet(strColumnIdName)) ' Read your Id
recordSet(strColumnDateName) = Now()     ' Store your data
recordSet.Update

recordSet.Close

LeCygne