Return Database ID when inserting record

[philbell]

I need to be able to insert a record into an Access2K table using ADO2.1 and be able to access the new record to retrieve the database ID

Something like: -

set rs = con.execute("INSERT INTO table (fldFieldName) VALUES (fieldvalue)")

dbID = rs!ID

This inserts the record OK but does not return a recordset.

Can anyone tell me what I'm doing wrong.

[Ianpbaker]

hi Phill

Where you using the db.execute with sql string it inserts the new record but WONT update the recordset. There are two way of getting round this. If you want it to automatically go to the record you have entered use the following code.

rs.addnew
rs!fieldvalue = txttest
rs.update

dbID = rs!ID

Providing the recorset is already open and none of your other fields in that table require values, this will work fine

if you still want to do a db.execute then after you have inserted requery your recordset and this will now include your new record

Hope this helps

Ian