 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
MS Access Forum / Database Design / July 2004Resetting (Autonumber) - Solution
To All: Found this today and I know that it will be usefull for me so I thought I would pass it on. If you have ever set up a database and started testing had numerous "test" records taking up your autonumber? Maybe testing a program to assign an Invoice number and you haven't been able to reset the autonumber to start at your first invoice number?: What you do is purge all the records in your database table that you have used for testing. Then Compact the database. This will reset your autonumber... If you have real data in the database, you just purge the dummy data, then your autonumber will be 1+ the highest value in the table. Enjoy Brook Enjoy An observation... A scan through this newsgroup (tablesdbdesign) will reveal some consensus against using the Access Autonumber datatype for anything a user might see. This is not to say that it cannot (or even should not) be done, just that there are enough "wrinkles" involved in exposing what is intended to be a "behind the curtain" unique row identifier to the view of users. A word of caution -- if the database includes any "child" tables to the table with AutonumberIDs, re-starting the "parent's" Autonumbers will NOT update the child tables' use of those IDs as foreign keys. The net result, in this case, would be you'd "orphan" the child records.  SignatureGood luck Jeff Boyce <Access MVP I am mearly stating a point that if you have a table that you are using for testing purposes then this would work so that you can start your tables clean. Brook >-----Original Message----- >An observation... [quoted text clipped - 9 lines] >update the child tables' use of those IDs as foreign keys. The net result, >in this case, would be you'd "orphan" the child records. Brook If I've been testing in a new db, I start out "clean" by deleting all rows and moving forward. No need to "reset autonumber". Jeff What would you do if you had a primary key that was an autonumber? And this primary key/auto number field was use as an invoice number? Would you just start your invoice number with what ever number was next after all your testing? Brook >-----Original Message----- >Brook [quoted text clipped - 5 lines] > >. Brook, I think you are missing Jeff's point. If no one sees it, then it makes no difference whether it starts from 1 or 99999. It sounds like you are using the autonumber for something that the user will see (Invoice number). The consensus is against doing that. There are several reasons for that. If you take his suggestion and search the newsgroup you will see why. SignatureAlphonse Giambrone Email: a-giam at customdatasolutions dot us > What would you do if you had a primary key that was an > autonumber? And this primary key/auto number field was use [quoted text clipped - 14 lines] > > > >. Sorry to cause such a fuss... I was just trying to pass some information along that helped me out... >-----Original Message----- >Brook, [quoted text clipped - 27 lines] > >. Brook, pardon me for jumping in here. I'm pretty sure that my good friend Jeff would never use an AutoNumber field to create an invoice number for an application, but let me suggest that there is nothing inherently "evil" (if I may be permitted to borrow a term from our President) about doing so and it is certainly the easiest way to do that. However, there is an inherent problem in using it that way. There is no guarantee that there will not be gaps in the sequence of numbers. Most business like to have an unbroken sequence for numbers like invoice numbers and insist on having an unbroken sequence for numbers like check numbers. A value for an AutoNumber field is lost once it has been used. Thus, if a user starts creating a record and then presses the escape key or cancels the creation of the record in some other way, the AutoNumber generated is gone forever. Therefore, if you are going to use it for generating your invoice numbers you need to be aware of that and make your users aware of it. If you need an unbroken sequence of numbers, then you will have to come up with another way to generate it. SignatureLynn Trapp MS Access MVP www.ltcomputerdesigns.com Access Security: www.ltcomputerdesigns.com/Security.htm > What would you do if you had a primary key that was an > autonumber? And this primary key/auto number field was use [quoted text clipped - 14 lines] > > > >. Lynn (and Brook) You captured the gist of my first response -- Autonumbers do a great job, but I don't like using them for purposes other than they're designed for. Brook -- if you don't open the topic for discussion, how will you learn what other folks think? No problems! Jeff Boyce <Access MVP |
Reply to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2008 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.