[AccessD] ADO Access backend Autonumber

On 3 Jul 2003 at 12:14, Griffiths, Richard wrote:

> Hi
> 
> Using ADO and Browser interface we have a system that insert records into
> and A97 BE db table which has an Autonumber field - the users do not have a
> ui to delete records but we have found 'missing' records ie autonumber
> missed.  Has anyone any experience of this or know of jet/access issues re
> autonumber or any other thoughts.
> 
The Autonumber is allocated as soon as they start to enter data into 
a new record. If they then back out without saving the record, the 
Autonumber has already been used and will show up as "missing".

That's the major drawback with using an Autonumber field for 
audit/tracking purposes - it just doesn't work.  If you must have a 
continuous string of numbers with no gaps, the only way to do it is 
to lookup the last used number in the Before_Update event and 
immediately allocate the next one.

Even this is not foolproof in a multi-user environment - you need to 
build in error checking so that two additions do not overlap. If two 
users update almost simultaneously, the second one can get the same 
"last number" after the first one has read it but before the new 
record is saved.  You need to trap the error when the second user's 
save actually takes place and increment the number again.




-- 
Lexacorp Ltd
http://www.lexacorp.com.pg
Information Technology Consultancy, Software Development,System 
Support.