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.

Answer 1

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;"