How to retain the AutoNumber of a Primary Key when executing a query in MS Access?

Question

I am trying to do something like the following in a query:

Dim rs As RecordSet
Dim NewPrimaryKey as Long

Set rs = Currentdb.OpenRecordset("SELECT * FROM MyTable WHERE MyPrimaryKey Is Null;")

With rs
      .AddNew
      NewPrimaryKey = !MyPrimaryKey
      !DateValue = Now()
      ...
      .Update
End With

Any pointers on how to do t his using a query that I can execute in MS Access 2003 using the JET engine would be greatly appreciated.

What is the primary key field type? Is it autonumber? Will http://stackoverflow.com/questions/1628267/autonumber-value-of-last-inserted-row-ms-access-vba help? — THEn, Jul 25 '11 at 23:16
It is not clear what you are trying to do. In your code here you are pulling all records where "MyPrimaryKey" field is Null. Then you are setting your new primary key field to the value of MyPrimaryKey which I am pretty sure will be Null in every case. Is that what you intended? — Dean Davids, Jul 25 '11 at 23:51
Sorry for the delayed response, you are correct up until setting the NewPrimaryKey to Null. Although that is what it looks like is happening, since the !MyPrimaryKey is an AutoNumber, it will populate the automatically incremented value of !MyPrimaryKey into the field NewPrimaryKey. — Curtis Inderwiesche, Sep 13 '11 at 19:58

HansUp · Accepted Answer · 2011-09-13T21:29:16.340

You can use two SQL statements to accomplish what I think you want. First an INSERT. Then "SELECT @@Identity" to get the last added autonumber value. Use an object variable for the database connection with both SQL statements.

Dim db As DAO.Database
Dim NewPrimaryKey As Long
Dim strInsert As String

strInsert = "INSERT INTO MyTable ([DateValue])" & vbCrLf & _
    "VALUES (Now());"
Set db = CurrentDb
db.Execute strInsert, dbFailOnError
NewPrimaryKey = db.OpenRecordset("SELECT @@Identity")(0)
Debug.Print NewPrimaryKey
Set db = Nothing

I enclosed the field name DateValue in square brackets because it is a reserved word.

Edit: If you insert multiple records with one SQL statement, SELECT @@Identity will still give you the last autonumber. It's the last autonumber for inserts performed through that connection instance. And you don't get a sequence of the autonumbers used; only the last one.

strInsert = "INSERT INTO MyTable3 ([some_text])" & vbCrLf & _
    "SELECT TOP 3 foo_text FROM tblFoo" & vbCrLf & _
    "WHERE foo_text Is Not Null ORDER BY foo_text;"

Would this work even if the INSERT was performing over multiple records? I can see how it works in a single instance, but can't see how it would work for many records at once. — Curtis Inderwiesche, Sep 13 '11 at 20:01

How to retain the AutoNumber of a Primary Key when executing a query in MS Access?

1 Answers1