Get ID of Last Inserted Record - Access DAO, ODBC, SQL Server 2008 Identity Field -

it's common question i'm having trouble getting id of last inserted record. i'm using dao odbc linked tables duplicate record , it's child records. tables in sql server 2008 , have identity fields id fields.

here's i've tried far. first bit of code here results in error 3167, record deleted. if debug.print recordset contains 3 records.

dim r dao.recordset, db dao.database set db = currentdb set r = db.openrecordset("select top 2 * item order datetimemodified desc", dbopendynaset, dbseechanges) r.addnew 'set field values here r.update 'completes without error r.bookmark = r.lastmodified debug.print r("itemid") 'error 3167, record deleted 

here's next thing tried:

debug.print db.openrecordset("select @@identity item")(0) 

this last 1 completes without problem value returned incorrect. actual new itemid 321 returns value 614. value returning appear incremental (it changes keep testing this) not appear relate @ table. there no field value 614. i've double checked make sure i'm looking correct table.

i know use dlookup or dmax don't think considered bullet proof in multi-user environment.

i suppose use stored procedure ado around problem. i'm wondering if option?

edit1:
i'm using following code , doing need/want to. suspect same using dmax.

dim r dao.recordset, db dao.database set db = currentdb set r = db.openrecordset("select top 1 * item order itemid desc", dbopendynaset, dbseechanges) r.addnew 'set field values here r.update r.requery r.movefirst debug.print r("itemid") 

as far i'm aware @@identity doesn't work cursor-based inserts. dao , ado both use cursors behind scenes.

after .update record should able identity value reading value.

the following works fine me via ado recordset opened keyset semantics:

r.update debug.print r("itemid") 

the following works fine me via dao recordset opened dynaset semantics:

r.update r.bookmark = r.lastmodified debug.print r("itemid") 

you should avoid .requery , .movefirst, you're introducing concurrency problems. consider:

dim r dao.recordset, db dao.database set db = currentdb set r = db.openrecordset("select top 1 * item order itemid desc", dbopendynaset, dbseechanges) r.addnew ''// set field values here r.update ''// @ point user adds new record r.requery r.movefirst ''// order itemid desc means you're going see new user's row debug.print r("itemid")