Last record inserted ID

ADAC Programming · May 24th, 2006, 02:03 PM

In an access database I have a table with the ID set for autoincrement. I am inserting a record using DoCmd.RunSQL
Is there an easy way to find out the ID number of the record after I insert it? I need to referance the record right after I create it.

in MS SQL I use @@IDENTITY is there something similar.

Thanks

Dwayne http://www.adacprogramming.com
http://www.topwebsitetips.com

BrianWren · June 5th, 2006, 02:41 PM

No there isn't.

To do this, you will need to add information yourself, and look up the record using that info.

For instance, you could add an "INSERT_DATE_TIME" column, and fill it in with NOW() as you add the record.

Then of course you could retrieve the most recent record with something like:

Code:

SELECT ID
FROM  <MyTable>
WHERE  INSERT_DATE_TIME = (  SELECT MAX(INSERT_DATE_TIME)
                             FROM   <MyTable>
                          )