Autonumber value of last inserted row – MS Access / VBA

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
  newRow = db.OpenRecordset("SELECT @@IDENTITY")(0)
  Set db = Nothing

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

Leave a Comment