How to get the AutoNumber generated by an SQL insert

Question

I am trying to write a function to check the minimum value in an inventory table then generate and send a purchase request if the value is below the minimum level.

If the conditions are met an append sql statement like the one below is run.

DoCmd.RunSQL "INSERT INTO [table1]('field 1', 'field 2')" & _
             "VALUES ('value 1', 'value 2')"

The linked tables I am appending to use an auto number field as primary key and I need to reference its value to link the various items being requested. I do not know how to store the auto number that is being generated for the new record. I feel like I am missing something really simple, but none of my searches have brought anything up.

I would like to be able to write a second statement like the one below with the auto number value included.

DoCmd.RunSQL "INSERT INTO [table2]( 'ID' , 'field 3')" & _
             "VALUES (" & TempVars!autonumber & ", 'value 3')"

This has a lot more to do with SQL Server than MS Access.so i will tag it so. — Nick.Mc, Sep 29 '15 at 22:37
@ctall Are you using SQL Server with Access? Or does SQL in the title refer to an Access SQL `INSERT`? — HansUp, Sep 30 '15 at 00:01
Then SQL Server is not involved? If not, suggest you remove the sql-server tag which another member added. I think that tag is why you received a suggestion about `SCOPE_IDENTITY` ... that is not available in Access. — HansUp, Sep 30 '15 at 00:08

score 3 · Accepted Answer · answered Sep 30 '15 at 00:56

Use a DAO.Database object variable to execute your INSERT and again when you fetch the value from SELECT @@identity The key here is use the same Database variable for both. If you try to get SELECT @@identity from CurrentDb (instead of a Database object variable), the value you retrieve will always be zero.

I tested this code in Access 2010.

Dim db As DAO.Database
Dim strInsert As String

Set db = CurrentDb
strInsert = "INSERT INTO [table1]([field 1], [field 2])" & vbCrLf & _
    "VALUES ('value 1', 'value 2')"
db.Execute strInsert, dbFailOnError

TempVars.Add "itemNum", db.OpenRecordset("SELECT @@identity")(0).Value
MsgBox "TempVars!itemNum: " & TempVars!itemNum

You are a God among mortals HansUp. This works flawlessly. Thank you for your help. — ctall, Sep 30 '15 at 01:12

ctall · Answer 2 · 2015-09-29T22:22:18.753

0

So it turns out I was using the wrong search terms.

SELECT @@identity FROM table1 should work just fine.

as it turns out though I still need to store @@Identity as a variable so that it can be used in as future looped sql "insert into".

the snippet below is what I have cobbled together but it tells me that recSet!answer is not found in this collection.

    Dim recSet As DAO.Recordset
    Set recSet = CurrentDb.OpenRecordset("SELECT @@identity")

    TempVars.Add "itemNum", recSet!answer

    recSet.Close
    Set recSet = Nothing

I have updated my code to the following

Dim recSet As DAO.Recordset
Set recSet = CurrentDb.OpenRecordset("SELECT @@identity")

Dim test As Long
test = recSet.Fields(0).Value
Debug.Print test

recSet.Close
Set recSet = Nothing

Now it returns a value, but the value is always "0". It does not appear to be grabbing the autonumber generated when the previous record was entered.

edited Sep 29 '15 at 22:22

answered Sep 19 '15 at 00:42

ctall

57
3
10

1

Even though `SELECT @@IDENTITY FROM TableName` runs without error you should not use the FROM clause because it can be misleading. `SELECT @@IDENTITY` is scoped to the session (connection) and the FROM clause is ignored. If you INSERT into TableA, then INSERT into TableB, then do `SELECT @@IDENTITY FROM TableA` you will actually get the AutoNumber value from TableB. `SELECT @@IDENTITY` always returns the most recent AutoNumber value created for the current connection. – Gord Thompson Sep 19 '15 at 16:09
Super helpful tip! That would have thrown me for a loop if I would have had to figure it out on my own. – ctall Sep 19 '15 at 18:08
I tried your suggested method, but get Run-time error '32538': TempVars can only store data. They cannot store objects. So I assume that is is trying to store as a record set instead of as a value/string/variable/whatnot. – ctall Sep 29 '15 at 21:45
Tried: `Dim recSet As DAO.Recordset Set recSet = CurrentDb.OpenRecordset("SELECT @@identity") Dim test As Long test = recSet.Fields(0).Value Debug.Print test recSet.Close Set recSet = Nothing` but it does not seem to grab the value out of the autonumber field. I also realized that the autonumber is not set as primary key. – ctall Sep 29 '15 at 22:16
The SELECT SCOPE_IDENTITY() command will be more stable with the way you are using it. If you are calling @@identity outside of the command that issued the insert then you may run into concurrency issues (inserts made via other connections). @@IDENTITY is global to all inserts while SCOPE_IDENTITY() will return the last generated value of the current scope/session. – Ross Bush Sep 29 '15 at 23:13

How to get the AutoNumber generated by an SQL insert

2 Answers2

Linked