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?

Azide

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 one but before your Dmax function finishes excecuting, then you would get their record.

Deguzman

DAO can do SELECT @@IDENTITY, too -- you don't need ADO. I do this all the time: lngID = db.OpenRecordset("SELECT @@IDENTITY")(0), where "db" is the same database variable that was used to execute the insert. I no longer open recordsets and Add for this. – Plantar

This does not work for me. I made an insert into a SQL Server Table with dao. I get a error: "Record is deleted" (3167) – Tema

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 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.

Plantar

and it works even when the recordset is a SQL Server linked table ! Wonderful ! – Lauretta

you might also add dbFailOnError as an option to the Execute. Otherwise Access won't say anything if it fails. ----- db.Execute query, dbFailOnError – Impenitent

@Lauretta SQL Server supports the SELECT @@IDENTITY syntax. It would be interesting to see what happens in other linked table types such as Excel, or other RDBMSs such as Oracle or MySQL. – Deferential

This does not work for me. I made an insert into a SQL Server Table with dao. I always get 0 in newRow. – Tema

Answer 3

This is an adaptation from my code for you. 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 a little French). This way is much faster than the one upper. In the example, this way was 37 times faster. Try it.

Const tableName As String = "InvoiceNumbers"
Const columnIdName As String = "??"
Const columnDateName As String = "date"

Dim rsTable As DAO.recordSet
Dim recordId as long

Set rsTable = CurrentDb.OpenRecordset(tableName)
Call rsTable .AddNew
recordId = CLng(rsTable (columnIdName)) ' Save your Id in a variable
rsTable (columnDateName) = Now()        ' Store your data
rsTable .Update

recordSet.Close

LeCygne

Ideational

Could you please identify which particular example it is "37 times faster" than? – Plantar

This does not work for me. I made an insert into a SQL Server Table with dao. recordId is always the same (1). – Tema

Answer 4

Private Function addInsert(Media As String, pagesOut As Integer) As Long


    Set rst = db.OpenRecordset("tblenccomponent")
    With rst
        .AddNew
        !LeafletCode = LeafletCode
        !LeafletName = LeafletName
        !UNCPath = "somePath\" + LeafletCode + ".xml"
        !Media = Media
        !CustomerID = cboCustomerID.Column(0)
        !PagesIn = PagesIn
        !pagesOut = pagesOut
        addInsert = CLng(rst!enclosureID) 'ID is passed back to calling routine
        .Update
    End With
    rst.Close

End Function

Thousand

I fail to see how this answers the original question. Please add context to see how this fits? – Cromer

This answer is unclear. Please add explanations. Not all of us know everything about vba. – Spinney

The commented line shows where the ID is collected. You would have to change the names to match your table ID. FORMAT: RecordName!IDName – Cordi

Although poorly explained, this format worked for me as it was closest to the structure of the method that I was already using. – Replicate

This does not work for me. I made an insert into a SQL Server Table with dao. addInsert is always the same. – Tema

Answer 5

Both of the examples immediately above didn't work for me. Opening a recordset on the table and adding a record does work to add the record, except:

myLong = CLng(rs!AutoNumberField)

returns Null if put between rs.AddNew and rs.Update. If put after rs.Update, it does return something, but it's always wrong, and always the same incorrect value. Looking at the table directly after adding the new record shows an autonumber field value different than the one returned by the above statement.

myLong = DLookup("AutoNumberField","TableName","SomeCriteria")

will work properly, as long as it's done after rs.Update, and there are any other fields which can uniquely identify the record.

Paraffinic

I made an insert into a SQL Server Table with dao. This is the only thing that works for me. But I Use DMax instead of DLookup. – Tema