Autonumeric field should not increment if operation cancelled

[jpinto1167]

I know the reason why autonumber fields get created no matter that you cancel an append query (basically to keep clear of id's conflict in a multiuser environment). Here is my problem. I do have one application that is NOT multiuser. The user manualy runs some queries to append records to a table. If the user decides to cancel the number still gets incremented. Do exist anyway to tell Access to not increment the autonumber field if the transaction is cancelled (without having to code VBA)?

Thank you

 

[RickSpr]

No, and it's for the same underlying reason. In order to prevent deadlocks in a multiuser environment, Access/Jet locks the table header, retrieves the Autonumber, increments it, stores it back, and unlocks the table header. That is a transaction by itself, and it must be, because keeping the table header locked until the user was finished editing and saving a record would block other users from being able to get an Autonumber. (I realize you understand this already, but I'm explaining it for others who might read this thread.)

You might think that Access could back out the Autonumber change in the table heading if the update was cancelled, but that would be a disaster if some other user had come along after you and incremented it. In backing out your change, the other user's Autonumber would be made reusable, which would eventually result in a duplicate Autonumber being assigned.

In a sense, from Access' point of view there is no such thing as a non-multiuser database. Even if you have the database open with an exclusive lock, nothing prevents you from having two separate forms trying to insert records in the same table concurrently. You could even open the second form after the Autonumber was assigned in the first one, so Access can't know at the time it assigns an Autonumber whether it might have multiuser issues to avoid. It has to assume a multiuser conflict is always possible, and therefore it has to unlock the table header very quickly, without waiting for the user to complete the update.

Rick Sprague
Want the best answers? See faq181-2886
To write a program from scratch, first create the universe. - Paraphrased from Albert Einstein