Get last insert ID from Access

Question

I am familiar with the MySQL function LAST_INSERT_ID; is there a similar function for performing the same query with a MS Access database via ODBC?

In my specific case, I am using PHP+PDO to insert rows into an Access database, and would like to know the last primary key value of each insert as they are performed.

If this functionality is not available, are there any alternatives? (without changing the database)

Thank you.

Solution 1

It seems that Access 2000 or later supports the @@IDENTITY property. So, you would only need to select its value after an INSERT:

select @@IDENTITY from myTable

Please see the MSDN link: Retrieving Identity or Autonumber Values

In short:

[...] Microsoft Access 2000 or later does support the @@IDENTITY property to retrieve the value of an Autonumber field after an INSERT. Using the RowUpdated event, you can determine if an INSERT has occurred, retrieve the latest @@IDENTITY value, and place that in the identity column of the local table in the DataSet.

Solution 2

As others have said, SELECT @@IDENTITY works with Jet 4 and the ACE.

A new consideration has been introduced with Access 2010, and that's because the new ACE version supports table-level data macros, which are the equivalent of triggers. Thus, an insert in one table might trigger an insert in another, so that @@IDENTITY might be the value for the second table instead of the top-level one. So far as I know, there is no equivalent to SQL Server's SCOPE_IDENTITY() for this scenario.

I have asked about it in other Access forums and nobody seems to know. It's something to watch for should you be using an ACCDB with table-level data macros.

Solution 3

Try running "SELECT @@IDENTITY FROM MyTable" after your insert.

Solution 4

I've never attempted to use access with php, but two ideas come to mind, The first one is simple. And that's to simple select max(id) from table after your insert, since it is auto incrementing you will get the highest value which should be the insertted value. Secondly you can try using odbc_cursor (http://au2.php.net/manual/en/function.odbc-cursor.php).

View more solutions

Comments

[David-W-Fenton]

No, the FROM clause has no meaning here at all. You can only find the last value for the database connection.

[David-W-Fenton]

The FROM clause has no meaning here at all. You can only find the last value for the database connection. And the cited MSDN article does not use a FROM clause: it's merely SELECT @@IDENTITY.

[Veign]

Thank you for the info. I have't used Access in many, many years