Search code examples
mysqlvbams-accessodbc

Get last Auto Increment from MySQL with Access and ODBC driver

I have a MS Access front end and a MySQL back end connected through ODBC.

I checked online, and there are lots of people who have the same problem but I still couldn't find a proper answer. I checked stackoverflow and also official documentation: http://dev.mysql.com/doc/connector-odbc/en/connector-odbc-usagenotes-functionality-last-insert-id.html

SELECT LAST_INSERT_ID(); on access simply returns undefined function last_insert_id()

When using: SELECT * FROM tbl WHERE auto IS NULL; I replaced tbl for my table_name and auto for my tableID (the PK of the table), and it says: The setting you entered isn't valid for this property. Is there any way to get the last ID inserted? or should I give up and find another way to get that ID?

Thanks

Solution

  • If the ID's are auto incremented fields then the following would work:

    SELECT TOP 1 ID FROM tbl ORDER BY ID DESC