With Access, you are limited to basically a single technique. On a positive note, the same technique works all the way back to Access 97.
First of all, I assume that we are inserting a record into a table where the primary key has an AutoNumber type. The addition of the record must be accomplished by using the AddNew and Update methods of the ADO Recordset object. Then, once the record has been added, store the absolute position of the new record and perform a Requery. Finally, set the cursor back to the bookmarked record and read the Id. Seem like a lot of work? And inflexible to boot? Such is the joy of Access.
Set cn = CreateObject("ADODB.Connection")
Set rs = CreateObject("ADODB.Recordset")
cn.Open "DSN=MyDSN;"
rs.CursorLocation = adUseClient
rs.Open "SELECT CustNo, CustomerName, Contact", cn, adOpenStatic,
adLockOptimistic
rs.AddNew
' CustNo is the AutoNumber field
rs.Fields("CustomerName").Value = "TAG Consulting"
rs.Fields("Contact").Value = "Alisa Johnson"
rs.Update
' The record has been inserted, but rs.Fields("CustNo").Value
is zero
bookmark = rs.absolutePosition
rs.Requery
rs.absolutePosition = bookmark
' Voila
MsgBox rs.Fields("CustNo").Value
'Response.Write rs.Fields("CustNo").Value
Comments