How to retreive the autonumber

If your'e using Access2K, then use the Jet database driver and execute a "SELECT @@Identity" after you create the records and it will return the autonumber:
http://www.adopenstatic.com/Experiments/FastestAutonumber.asp

Cheers,
Andrew

I had the same problem and made this script. I don't have to create a diff recordset this way. It works for me (basically I don't use a SQL statement to create the rs, but I user the AddNew method. After the Update the recordset is still pointing the added row, so u can retrieve the autonumber). Just change connection db name, table name and fields:

Set oConn = Server.CreateObject("ADODB.Connection")
oConn.Open("DRIVER={Microsoft Access Driver (*.mdb)}; DBQ=" & server.mappath("dbpath.mdb") & ";")

 Set oRS=CreateObject("ADODB.Recordset")

 oRS.ActiveConnection = oConn
 oRS.CursorLocation = adUseClient
 oRS.CursorType = adOpenKeyset
 oRS.LockType = adLockOptimistic
 oRS.Source = "tablename"
 oRS.Open

 oRS.AddNew
  oRS("Field1") = "Value1"
  oRS("Field2") = "Value2"
 oRS.Update

 intAutoID = oRS("ID")

From your code, if you use just the last line after the Update, you should be able to retrieve the ID.