How do I get the unique identifier value of a record inserted into an Autonumber field?
It depends what DBMS you are using. The simplest way is to write an ON INSERT trigger which returns the value of @@IDENTITY when you do the insert. This is only possible in SQL Server. You may want to use a stored procedure instead (see below). If you are using another DBMS which supports stored procedures, eg Oracle, you can wrap the insert into a stored procedure and return the ID from that. If the DBMS does not support stored procedures (eg Access), you have to make another query to the database immediately after the INSERT in order to retrieve the ID. One way is to SELECT MAX(ID). The problem here is that if another user has added a record between the two SQL operations, you will get the wrong ID back. This can be avoided by wrapping the two operations inside a database transaction. Alternatively, you can SELECT ID …WHERE (all the field values match). This is less efficient but could be necessary if the DBMS does not support transactions. An alternative to all these is to avoid u
Related Questions
- Im using the debugger for VBA code and get the message "The value in the field or record violates the validation rule for the record or field". What does this mean?
- Is there a space in the permitted value of the Version Description field contained in the Header Record?
- What is the best value to record in the Anaesthetic field on the student medical tab?
