[AccessD] Custom autonumbering

I've used Autonumbers as actual values (like Ticket numbers in a request
system) where the number doesn't have to truly be sequential.  But in
the case you are talking about, it seems kind of odd to create a routine
that mimics the AutoNumber field type, instead of just using that field
type (in a separate table to keep it sequential).  Hope it works out.

Drew

-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Asger Blond
Sent: Saturday, June 27, 2009 7:02 AM
To: 'Access Developers discussion and problem solving'
Subject: Re: [AccessD] Custom autonumbering

Thanks Drew - your explanation corresponds my reading your first
posting.
I always use AutoNumber exclusively for surrogate primary keys, never
for
alternate natural keys like OrderNumber.
In my opinion AutoNumber (and the equivalent in SQL Server: Identity) is
designed for surrogate keys, not for natural keys.
You could use AutoNumber for natural keys, but then you are inviting
troubles. In Access pressing Esc on entering a new record will increment
the
AutoNumber, leaving gaps in the numbering. And depending on which
version of
Access you use, a Compact Database will reset the autonumber to the next
value after your records INcluding deleted trailing records, or it will
reset to the next record after your records EXcluding deleted trailing
records. Bottom line: You simply can't predict the value of an
AutoNumber
field.
This is what makes AutoNumber an excellent candidate for surrogate
primary
keys because this kind of keys is supposed to be meaningless.
You can ignore the special behaviour of AutoNumber if you use it as a
surrogate key like OrderID, but you certainly can't ignore it if you
want a
natural key value like OrderNumber, which is supposed to be meaningful.

Asger

The information contained in this transmission is intended only for the person or entity 
to which it is addressed and may contain II-VI Proprietary and/or II-VI Business 
Sensitive material. If you are not the intended recipient, please contact the sender 
immediately and destroy the material in its entirety, whether electronic or hard copy. 
You are notified that any review, retransmission, copying, disclosure, dissemination, 
or other use of, or taking of any action in reliance upon this information by persons 
or entities other than the intended recipient is prohibited.