Heenan, Lambert
Lambert.Heenan at AIG.com
Thu May 6 09:26:03 CDT 2004
According to Microsoft, when this was a 'feature', you had to delete all the records in a table and then compact the database. Doing so would reset the AutoNumber field to start at 1 the next time records were added. I've never heard of any other circumstance resetting them. Lambert > -----Original Message----- > From: John W. Colby [SMTP:jwcolby at colbyconsulting.com] > Sent: Thursday, May 06, 2004 10:01 AM > To: Access Developers discussion and problem solving > Subject: RE: [AccessD] AutoNumber To Start From 900120 > > >if some specific value of an AutoNumber field is important then the > AutoNumber field is not being used properly (this is an old hobby horse of > mine). > > Amen. > > OTOH I have had to "reset" the autonumber many times, to a value larger > than > the last one used, because something as happened to reset the autonumber > back down to an invalid value. I was simply answering the question. I > figured I'd leave the soap box to someone else for once. ;-) > > John W. Colby > www.ColbyConsulting.com > > -----Original Message----- > From: accessd-bounces at databaseadvisors.com > [mailto:accessd-bounces at databaseadvisors.com]On Behalf Of Heenan, > Lambert > Sent: Thursday, May 06, 2004 9:42 AM > To: 'Access Developers discussion and problem solving' > Subject: RE: [AccessD] AutoNumber To Start From 900120 > > > Quite a few replies to this question, but I'm surprised that nobody has > mentioned that if some specific value of an AutoNumber field is important > then the AutoNumber field is not being used properly (this is an old hobby > horse of mine). > > An AutoNumber field is supposed to uniquely identify a specific row in a > table. That's all. The user's need not ever see or know the value of such > an > AutoNumber field, it's used by the database to link related tables > together. > If the value of an AutoNumber field is important to a user then it is > being > given some other meaning, and that's only going to cause problems down the > line. In effect you are storing two pieces of information in one field, > and > that's contrary to the normalization principals we should all at least be > aware of. > > Even Microsoft seem to have woken up to this as A2K+ no longer reset > AutoNumber's on compacting (thanks for testing that John). > > Just my 2 cents. > > Lambert > > > -----Original Message----- > > From: Jim Hewson [SMTP:JHewson at karta.com] > > Sent: Thursday, May 06, 2004 9:22 AM > > To: Access Developers discussion and problem solving > > Subject: RE: [AccessD] Autonumber To Start From 900120 > > > > > > Issue? I thought that was feature. > > > > -----Original Message----- > > From: accessd-bounces at databaseadvisors.com > > [mailto:accessd-bounces at databaseadvisors.com]On Behalf Of John W. Colby > > Sent: Thursday, May 06, 2004 8:23 AM > > To: Access Developers discussion and problem solving > > Subject: RE: [AccessD] Autonumber To Start From 900120 > > > > > > Uhh... yep. At least in A2K and earlier. AXP doesn't seem to do that. > > In > > fact I just tested in A2K SR1 and it isn't resetting the autonumber > > either, > > so a service pack may have fixed that. But it definitely used to be an > > issue so be careful. > > > > John W. Colby > > www.ColbyConsulting.com > > > > -----Original Message----- > > From: accessd-bounces at databaseadvisors.com > > [mailto:accessd-bounces at databaseadvisors.com]On Behalf Of Jim DeMarco > > Sent: Thursday, May 06, 2004 8:47 AM > > To: Access Developers discussion and problem solving > > Subject: RE: [AccessD] Autonumber To Start From 900120 > > > > > > Yes this will work. One caveat though: do not compact the db until > you've > > got the seed number you want inserted. Compacting will reset the > > autonumber. > > > > Jim DeMarco > > > > -----Original Message----- > > From: John W. Colby [mailto:jwcolby at colbyconsulting.com] > > Sent: Thursday, May 06, 2004 8:45 AM > > To: Access Developers discussion and problem solving > > Subject: RE: [AccessD] Autonumber To Start From 900120 > > > > > > One way that works with all versions of Access (AFAIK) is to append in a > > dummy record specifying a value one less than the number you want. Then > > go > > in and delete that record. The next record will pick up with the next > > value > > (assuming an incrementing autonumber) > > > > John W. Colby > > www.ColbyConsulting.com > > > > -----Original Message----- > > From: accessd-bounces at databaseadvisors.com > > [mailto:accessd-bounces at databaseadvisors.com]On Behalf Of > > paul.hartland at fsmail.net > > Sent: Thursday, May 06, 2004 8:25 AM > > To: accessd > > Subject: [AccessD] Autonumber To Start From 900120 > > > > > > To all, > > Is there a way of telling the Autonumber where to start from in a > Database > > ? > > Thanks in advance. > > Paul > > > > -- > > > > Whatever you Wanadoo: > > http://www.wanadoo.co.uk/time/ > > > > This email has been checked for most known viruses - find out more at: > > http://www.wanadoo.co.uk/help/id/7098.htm > > -- > > _______________________________________________ > > AccessD mailing list > > AccessD at databaseadvisors.com > > http://databaseadvisors.com/mailman/listinfo/accessd > > Website: http://www.databaseadvisors.com > > > > > > > > -- > > _______________________________________________ > > AccessD mailing list > > AccessD at databaseadvisors.com > > http://databaseadvisors.com/mailman/listinfo/accessd > > Website: http://www.databaseadvisors.com > > > > > > > > > > > > > > > ************************************************************************** > > ** > > ******* > > "This electronic message is intended to be for the use only of the named > > recipient, and may contain information from Hudson Health Plan (HHP) > that > > is > > confidential or privileged. If you are not the intended recipient, you > > are > > hereby notified that any disclosure, copying, distribution or use of the > > contents of this message is strictly prohibited. If you have received > > this > > message in error or are not the named recipient, please notify us > > immediately, either by contacting the sender at the electronic mail > > address > > noted above or calling HHP at (914) 631-1611. If you are not the > intended > > recipient, please do not forward this email to anyone, and delete and > > destroy all copies of this message. Thank You". > > > ************************************************************************** > > ** > > ******* > > > > -- > > _______________________________________________ > > AccessD mailing list > > AccessD at databaseadvisors.com > > http://databaseadvisors.com/mailman/listinfo/accessd > > Website: http://www.databaseadvisors.com > > > > > > > > -- > > _______________________________________________ > > AccessD mailing list > > AccessD at databaseadvisors.com > > http://databaseadvisors.com/mailman/listinfo/accessd > > Website: http://www.databaseadvisors.com > > -- > > _______________________________________________ > > AccessD mailing list > > AccessD at databaseadvisors.com > > http://databaseadvisors.com/mailman/listinfo/accessd > > Website: http://www.databaseadvisors.com > -- > _______________________________________________ > AccessD mailing list > AccessD at databaseadvisors.com > http://databaseadvisors.com/mailman/listinfo/accessd > Website: http://www.databaseadvisors.com > > > > -- > _______________________________________________ > AccessD mailing list > AccessD at databaseadvisors.com > http://databaseadvisors.com/mailman/listinfo/accessd > Website: http://www.databaseadvisors.com