What are AutoNumber Dangers
in [Tables]
|
Prev: Auto populate field in table
Next: Skydrive
From: David W. Fenton on 28 Apr 2010 20:44 =?Utf-8?B?RnJlZA==?= <Fred(a)discussions.microsoft.com> wrote in news:38736AAF-BC0B-4D48-92E9-0B719B0FDCFC(a)microsoft.com: > When it's said to never show it to the outside world, that's to > guard against anybody ever trying to change one. Er, what? Autonumbers aren't editable, so there's no danger even *if* somebody is tempted to change one. The problem is that people invest MEANING in Autonumber values and assume they are sequential and have no gaps. An Autonumber is really just a special type of default value for a Long Integer field (you can append any value to it you like that doesn't violate an existing index). It can, in fact, be random (and *is* random in a replicated Jet database). The values of an Autonumber field have no meaning. Exposing them to the user tempts them to invest the values with meaning, and that's the flaw. -- David W. Fenton http://www.dfenton.com/ usenet at dfenton dot com http://www.dfenton.com/DFA/
From: John W. Vinson on 28 Apr 2010 20:57 On Wed, 28 Apr 2010 13:11:01 -0700, Barry A&P <BarryAP(a)discussions.microsoft.com> wrote: >I recently viewed a post that said "autonumber is fine as long as you dont >display it to the user..." Everything in my database references >autonumbers!! did i make a big mistake somewhere?? i am even marking parts >with ID numbers that are generated by the autonumber field on a few of my >tables... > >Should i be worried?? > >Do i need to seriously look into a new approach?? > >any comments are greatly appreciated.. > >Barry Just one other caveat in addition to the good points made by the other responders: if you ever Replicate your database (which you won't if it's in 2007 or 2010, that feature has been removed), all your autonumbers will become random. The next three records might have autonumber values of -228412113, 1924443671 and -889225013. If that won't freak out your users nothing will! -- John W. Vinson [MVP]
From: Rick Brandt on 28 Apr 2010 17:37 Barry A&P wrote: > I recently viewed a post that said "autonumber is fine as long as you dont > display it to the user..." Everything in my database references > autonumbers!! did i make a big mistake somewhere?? i am even marking > parts with ID numbers that are generated by the autonumber field on a few > of my tables... > > Should i be worried?? > > Do i need to seriously look into a new approach?? > > any comments are greatly appreciated.. In my opinion "never expose AutoNumbers to users" is vastly overstated. It is based on an assumption that users will make assumptions that might confuse them (like the values should not have gaps). Well, amongst users the stupid ones will make assumptions that confuse them about LOTS of things. The smart ones not so much. Many situations will never expose a long continuous block of records to a user at one time where an assumption about gaps comes into play anyway. There are cases where the "business rules" dictate no gaps. In those cases avoid AutoNumbers. There is little reason to avoid them (and show them to users) when business rules don't care.
From: Barry A&P on 29 Apr 2010 12:09 I am in fact using the autonumber field because i Need a Unique Identifier for each item. I tried the Consecutive number approach but had many cases where using DMax+1 then the last record was deleted and the number was re-created-- I am in Access 2007 and dont know if i will ever need to replicate (whatever that does) so for now i will cross my fingers and hope i dont ever see -32481927 as a Unique Identifier for one of my parts.. Cross that bridge when i get there i guess... Thank you everybody for your input.. Barry "Armen Stein" wrote: > On Wed, 28 Apr 2010 14:59:31 -0700, "Jeff Boyce" > <nonsense(a)nonsense.com> wrote: > > >What Daniel and Fred said ... > > Me too! > > >Access Autonumbers are designed to serve as unique row identifiers, nothing > >else. As such, they are rarely suitable for human consumption. > > Rarely, but not never. > > >If you want your users to care about some number, use Daniel's approach and > >create a new field, in addition to your autonumber field. Use that new > >field to hold a "sequence number". That one's OK to show the users... > > > >(and all things in moderation... a fellow whose experience I greatly respect > >quite often displays autonumbers to his user ... but he has a very high > >class of users ...<g>) > > We sometimes let the users see an Autonumber, when they need to > correctly identify one record among many similar records - > transactions for example. Of course they can't change it, and we tell > them not to worry about gaps in the sequence. Once we explain that > it's just a unique "Reference Number" (we actually sometimes call it > that in the UI), they seem to do just fine with it. It's easier than > building our own sequence number. > > Armen Stein > Microsoft Access MVP > www.JStreetTech.com > > . >
From: Barry A&P on 29 Apr 2010 12:10
Thanks Jeff I needed to see this line "(and all things in moderation... a fellow whose experience I greatly respect quite often displays autonumbers to his user ... but he has a very high class of users ...<g>) " Barry "Jeff Boyce" wrote: > Barry > > What Daniel and Fred said ... > > Access Autonumbers are designed to serve as unique row identifiers, nothing > else. As such, they are rarely suitable for human consumption. > > Keep using them as unique row identifiers, but don't expect your users to > understand ... OR CARE! > > If you want your users to care about some number, use Daniel's approach and > create a new field, in addition to your autonumber field. Use that new > field to hold a "sequence number". That one's OK to show the users... > > (and all things in moderation... a fellow whose experience I greatly respect > quite often displays autonumbers to his user ... but he has a very high > class of users ...<g>) > > Regards > > Jeff Boyce > Microsoft Access MVP > > -- > Disclaimer: This author may have received products and services mentioned > in this post. Mention and/or description of a product or service herein > does not constitute endorsement thereof. > > Any code or pseudocode included in this post is offered "as is", with no > guarantee as to suitability. > > You can thank the FTC of the USA for making this disclaimer > possible/necessary. > > "Barry A&P" <BarryAP(a)discussions.microsoft.com> wrote in message > news:30984979-018A-4DB1-AE3F-9BEDADF0833A(a)microsoft.com... > >I recently viewed a post that said "autonumber is fine as long as you dont > > display it to the user..." Everything in my database references > > autonumbers!! did i make a big mistake somewhere?? i am even marking > > parts > > with ID numbers that are generated by the autonumber field on a few of my > > tables... > > > > Should i be worried?? > > > > Do i need to seriously look into a new approach?? > > > > any comments are greatly appreciated.. > > > > Barry > > > . > |