Access 2007 Trap: Life without @@identity()

I came across some interesting behavior in Access 2007 that tripped me up for a little while.I needed to add a record to a table and then get the primary key value of the newly added record.

I wrote the code that I expected to work and I always got back the same number for the primary key every time I ran the code; the value of the primary key value of the first record.But I wanted the primary key value of the last record, the record that I just created. So, I added .MoveLast to get the last record.

   Dim rs As Recordset2
   Dim recordId As Integer
   Set rs = Application.CurrentDb.OpenRecordset("Table_1")
   With rs
       .AddNew
       !field_1 = "Field_1"
       !field_2 = "Field_2"
       ' I expected the the primary key value to be loaded here:
       .Update
       ' When you open a recordset, there is an implied "MoveFirst" call
       ' For whatever reason, Access doesn't refresh values after writing
       ' the record:
       .MoveLast
       ' Without the MoveLast, you get the record_id of the first record:
       recordId = !record_id
       .Close
   End With
   Set rs = Nothing

I guess the thing that screwed me up is that I expected all of the fields in the current record of a RecordSet2 point to the same record. In my mind, when I call Update, the value of the primary key should be retrieved and ready for me to reference.

In ADO.NET, the DataSet has the AcceptChanges() method. This is logical to me because a DataSet is disconnected. I guess a Recordset2 is "loosly" connected.

Access continues to weird me out.