If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Resetting (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 |
#2
|
|||
|
|||
Resetting (Autonumber) - Solution
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. -- Good luck Jeff Boyce Access MVP |
#3
|
|||
|
|||
Resetting (Autonumber) - Solution
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... 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. -- Good luck Jeff Boyce Access MVP . |
#4
|
|||
|
|||
Resetting (Autonumber) - Solution
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 |
#5
|
|||
|
|||
Resetting (Autonumber) - Solution
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 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 . |
#6
|
|||
|
|||
Resetting (Autonumber) - Solution
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. -- Alphonse Giambrone Email: a-giam at customdatasolutions dot us "Brook" wrote in message ... 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 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 . |
#7
|
|||
|
|||
Resetting (Autonumber) - Solution
Sorry to cause such a fuss... I was just trying to pass
some information along that helped me out... -----Original Message----- 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. -- Alphonse Giambrone Email: a-giam at customdatasolutions dot us "Brook" wrote in message ... 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 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 . . |
#8
|
|||
|
|||
Resetting (Autonumber) - Solution
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. -- Lynn Trapp MS Access MVP www.ltcomputerdesigns.com Access Security: www.ltcomputerdesigns.com/Security.htm "Brook" wrote in message ... 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 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 . |
#9
|
|||
|
|||
Resetting (Autonumber) - Solution
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 |
#10
|
|||
|
|||
Resetting (Autonumber) - Solution
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 |
Thread Tools | |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
Data Access Page Autonumber | General Discussion | 0 | June 30th, 2004 12:36 AM | |
autonumber | Deepak | General Discussion | 3 | June 18th, 2004 09:07 PM |
Autonumber -Reset to a lower number | Mike | General Discussion | 2 | June 5th, 2004 01:54 AM |
Autonumber foreign keys and subforms | Ed Havelaar | Database Design | 3 | May 12th, 2004 07:34 AM |
reset autonumber value in table | Database Design | 0 | April 29th, 2004 02:11 PM |