I am trying to retrieve the last IDENTITY (aut-increment) value inserted into a SQL Server 2000 database.
The INSERT command is run from MS Access VBA code, using ADODB (MDAC 2.7 I think).
I can use a simple "Select @@IDENTITY from table_name" if I don't use transaction processing on the database connection, however I need use transaction processing because several inserts are tried and if any of them fail then the whole thing needs rolling back.
When using transaction processing, calling "Select @@IDENTITY" times out - understandable since the record is lock on the DB until the "CommitTrans" method is called for the connection.
Does anyone have any ideas how to achieve this?
(P.S. dues to this being an upgrade from a legacy system, the INSERT command is run by assembling a string of text and running this against the connection, not from using the ADO "AddNew" method).
Thanks in advance!