Richard what is your thought about using Autonumbers? Would it not be better to generate your own? I have done some data recovery before and that autonumber can really give trouble sometimes, Just your thought?
Auto numbering your items can lead to spaces or 'gaps' being left inside your table. These spaces can never be recovered once they have been created. They are created because auto numbering only guarantees that the number hasn't been used before they dont guarantee that they are in sequence. Anytime you create a record, but it gets cancelled or deleted, you will end up with a space in your auto numbering field that cannot be deleted or changed.
Hi Ray, usually your Id column wouldn't be seen by the customer/user, it's something only the DB needs to know about, therefore it wouldn't matter if it skipped a number.
But as Rich mentions in the video if there are times you need it to always exist, like the PaymentTypes it can be better to maintain it yourself.
For the most part I do prefer using autonumbers. Generally they are meant for relationships only and the end user will never have to see them or work with them. If you need a number that the customer may see like a "customer code" then you can create your own, and we're going to be doing that in the POS that we're building. However that's a secondary number and the table will still have an autonumber. The exception is going to be that payment type table that's only going to have a couple of records in it and I want to hard-code things like credit card, cash, etc. in the VBA so I want them to have specific numbers.
As far as data recovery goes, it can be a pain if you lose some records and have to re-enter them and then have to reattach them to related tables. That can be a nightmare. In this case I recommend good backups. But it's nothing that you can't fix with a series of update queries. My general rule of thumb is to never delete information. Unless your database is getting crazy huge you should always just mark things as inactive or not needed. Don't delete orders for example.
As far as worrying about numbers being in sequence that's not something that you should have to worry about. As long as you have a unique number for your relationships that's all that matters. If you need sequential numbers then yes definitely use your own number. You can use DMAX to determine the largest current number, then add one. Save it to the table quickly though so another user doesn't get assigned that number in a multi-user environment.
You can get rid of autonumber gaps at the end of the table if you delete some records at the end. Just compact and repair the database and it will reset the autonumber to the next highest one.
Sorry, only students may add comments.
Click here for more
information on how you can set up an account.
If you are a Visitor, go ahead and post your reply as a
new comment, and we'll move it here for you
once it's approved. Be sure to use the same name and email address.
This thread is now CLOSED. If you wish to comment, start a NEW discussion in
Access Developer 14.
