@@IDENTITY

I ran into a wonderful issue with the way MS Access handles getting the last insert id from SQL Server. The application would fail saying ‘record was deleted’ when trying to access the newly created object. I had determined that this was because of some logging triggers I had created. Removing the triggers solved the problem.

I could not see how my trigger would be affecting the insert statement, it fired after and operated on a separate table. After a bit of testing I determined that MS Access was using the @@IDENTITY variable to get the id of the newly created record. This is a poor choice. There is IDENT_CURRENT(`table_name`) as well as SCOPE_IDENTITY(), both of which would avoid the issue. @@IDENTITY is set on any insert statement for the connection, this includes any triggers. The logging trigger inserted a new record, overwriting @@IDENTITY. So then MS Access would get that id back, try to look up the record and fail. Or worse, look up the wrong record.

My lame solution to this lame problem was to set @@IDENTITY back to the original insert id. However, the only way to do this is by inserting a new record with the table’s identity column set to the original insert id.

To do this I created a new table.

CREATE TABLE insert_id_fix (id INT IDENTITY)

Then before I do my logging insert, save the value of @@IDENTITY to a local variable.

DECLARE @insert_id INT
SET @insert_id = @@IDENTITY

Then after inserting the log, insert a new record into the insert_id_fix table.

    SET IDENTITY_INSERT insert_id_fix ON
    INSERT INTO insert_id_fix (id) VALUES(@insert_id)
    SET IDENTITY_INSERT insert_id_fix OFF
    DELETE FROM insert_id_fix

Notice we have to SET IDENTITY_INSERT ON for the table to insert our value into the IDENTITY column. I also cleared the table since we don’t want to actually keep the data.

Another solution would be to change the trigger to be INSTEAD OF and process the insert yourself after doing the logging. This might be a better solution, but has its issues too.

As far as I know this is a problem with MS Access itself, not the application. I could be wrong as I didn’t write the application myself. The error occurred when accessing the newly created object, which should’ve been handled by Access. If anyone has further insight into how Access works, please comment.