Page 1 of 2 12 LastLast
Results 1 to 15 of 17

Thread: autonumber

  1. #1
    PaulLuke
    Guest

    autonumber

    I'm creating a knowledge base using Access 2000 and would like to start the document IDs with a six digit number.
    Is it possible to start the primary key with a seeded number?

    Tia

  2. #2
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: autonumber

    If you're storing the data in Access, you can change the seed by doing an append query on the table and appending the value you want as the starting point to the autonumber field. You can't enter values in an autonumber field but you can append values to one.

    However, I question the wisdom of doing this. If you want a number that looks impressive, build some code to generate that number and use it as a unique index, but keep your autonumber as the primary key, because it will be much faster. If you want to display document numbers a la the MS knowledgebase, just build a numberic string, which will even allow you to use leading zeros if you wish.
    Charlotte

  3. #3
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: autonumber

    Charlotte: Can you explain why it would be slower if you seed an autonumber field to start at some value by the method that you described? It seems to me that it would be just as fast for VBA to add one to a six digit number as it is to a one digit number (in binary). You only seed it once and after that it seems to me that it would be the same. However, it also seems to me that the extra overhead that you would have to code in to maintain a separate field, and generate unique numbers in that field would slow things down, particularly if more than one person could use the db at a time.
    Legare Coleman

  4. #4
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: autonumber

    Sorry, I wasn't clear in my comment about speed.[img]/w3timages/icons/blush.gif[/img] I meant the autonumber would be faster than a string value for indexing and searching. How many items do you intend to include in this knowledge base? The autonumber will be the most efficient key, but autonumbers are not necessarily consecutive, so don't rely on that. If you want continuous article indexes, use a made up key for that, but keep the autonumber (you don't have to display it) for behind the scenes indexing.
    Charlotte

  5. #5
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: autonumber

    Charlotte: I didn't ask the original question, I was just trying to figure out why you thought a separate field would be faster than an Autonumber field.
    Legare Coleman

  6. #6
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Brisbane, Queensland, Australia
    Posts
    352
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: autonumber

    Advice, rather than an answer to your query.

    NEVER use the primary key autonumber as anything that matters. If you want to use the field for anything add a separate autonumber field. I think the primary key numbers change when a database is compacted. Also if you make a mistake you cannot reuse that number.

    It is much better to let the primary key perform only its own function and use a separate number autonumber or otherwise for the external ID.
    David Grugeon
    Brisbane Australia

  7. #7
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: autonumber

    Did I answer your question? The answer is that I don't think that, I just didn't express myself well. I thought a separate field would be better for a user-significant key.
    Charlotte

  8. #8
    4 Star Lounger
    Join Date
    Jan 2001
    Location
    Altnau, Thurgau, Switzerland
    Posts
    447
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: autonumber

    Hi Grugeon,

    It is possible to reuse an autonumber if it has been deleted. You need to append a record with that number you wish to re-use. Obviously you can't duplicate it.
    I would also say that having 2 autonumber fields, one for the Primary key and one for the other use (referential key?) is overkill. Why should it give Access problems. Also for you (as developer/user) the second autonumber field is effectively read only in that table.

  9. #9
    peleg
    Guest

    Re: autonumber

    I agree that an autonum should not be used for a primary key. You should strive to find a key composed of one or more of the naturally occuring columns. The purpose of the primary key is to insure the uniqueness of the *contents* of the rows. While an autonum gives you something unique, it will not stop you from entering duplicates, say of "Acme Florists". If the primary key was the company name, you would have more assurance that this won't happen (Course, if they get the spelling wrong, it could happen, but that reduces to a form-design issue.)

    One correction. Autonums do NOT change values when the database is compacted. What can happen is this. If you created some rows (one or more), deleted them, and then compact, the autonum values of the deleted rows will be reused after the compact. What seems to happen is that Access always knows what the highest value it has EVER used is. So, if you delete row 1234, the next row will be 1235. Even if you compact at this point, 1234 will never get reused. Now, go and delete 1235, assuming it is the highest value and compact. Then 1234 and 1235 will again be used. The reuse only happens at the physical end of the table. If you delete row 23, that number will never be reused.

    If this weren't the case, autonums would be of limited utility.

    So what are they really only good for, if not primary keys?

    If your real primary key has several columns in it, it is just easier to add an autonum to use in joins. It is cheating, I know. But I never make the autonum the primary key in this case; it could be called a 'candidate key' however, that is still a violation of the rules. I just use it as a convenient cheat and only consider it to be a handy handle to a row and don't attribute any key-like properties to it.

    There is one place where I find, in spite of the above and my aversion to using autonums as primary keys, I can't seem to find anything that is generally better. If the data is a list of peoples names, then First and Last Name don't cut it becase it is likely that there can be more than one 'John Smith' in the table. So, you gotta add another column. Maybe an address line. But you don't always have the address line when you need it. SSN? Maybe, but this really isn't much different than using an autonum. Also, you may not always have it available when you are entering the row. Phone number? Ditto. It may be possible to not allow the entry of the row until you can get all of the necessary key data, but even that is not always possible. So, what is left? Only autonums. I would really like to hear how others get handle this kind of data. In 8 years of Access programming, I haven't thought of a really good, clean solution to the problem. Mediocre solutions, I have many. I wanna do better! but I've not been able to find the better solution.

    -- Peleg

  10. #10
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Brisbane, Queensland, Australia
    Posts
    352
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: autonumber

    Andy: Thanks for the information. I still think that in practical terms you would not normally be able to reuse autonumbers easily, and that is what our applications are about. In most cases I would agree the generation of a second autonumber wiould be overkill.


    Peleg:

    I am not too keen, myself, on using real data fields to create the primary key. My experience is that it tends to be more unwieldy than using an autonumber. That, however is my personal view based on the experience of the databases I have developed.

    My experience, however, is with small databases which do a lot with the links between data and are used by a few people. I have not developed any massive databases where the overhead of an extra field would be material.

    Thank you for your correction of my flawed understanding. It is useful to hear the opinions of other users/developers as they always broaden our knowledge.
    David Grugeon
    Brisbane Australia

  11. #11
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: autonumber

    Autonumbers are faster to search on the multiple column keys, which is a good reason to use them. An even better reason to use them is that they don't have any inherent meaning, so there is no reason to change them if a data field changes from 10 to 15 characters or from a number to text.

    That doesn't mean you shouldn't have a multiple column unique key to keep out duplicates. And the purpose of the primary key is NOT to insure anything except that you can uniquely identify that particular record. The purpose of a unique key is to prevent duplicates. They aren't necessarily the same thing.
    Charlotte

  12. #12
    2 Star Lounger
    Join Date
    Dec 2000
    Posts
    188
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: autonumber

    In your experience how has Access performed with an Autonumber field primary key and multiple users doing simultaneous data entry?

    TIA

  13. #13
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: autonumber

    Better than other methods, actually; but no multiuser data entry is smooth against an Access backend. I generally design my forms and subforms around that principle.

    In my last position, I created databases that held telephone surveys and event registration/survey/followup...whatever data. Because there might be anywhere from 2 or 3 to as many as a dozen people entering data into the same backend at the same time, I replicated only the front ends to the individual machines so that they were working with local copies of the interface. I designed my forms so that if one person were entering an address, someone else could be entering a phone number without bumping into each other, since the data was stored in different tables and accessed through individual subforms.

    I used No Locks (this was 97, so record level locking wasn't available) and wrapped everything I could in transactions to avoid placing a lock until the update method was invoked. I trapped the locking errors, and used a routine to wait for a random pause before trying once more to place the lock. I counted the tries and notified the user after 5 of them had failed, so that the user had the option of trying again or cancelling the save. If they cancelled, the data they entered was still in the unsaved record in the subform and they could try to save it again. The autonumbers took care of themselves when the record was created, so I didn't have to worry about testing to see if the new primary key had already been created for another record.

    The biggest problem arose when someone had a form open and their cursor positioned in one of the subforms, and then they went on break or wandered off to lunch. That effectively locked a page until I grabbed them by the scruff of the neck and made them log out!
    Charlotte

  14. #14
    peleg
    Guest

    Re: autonumber

    charlotte:

    You said:

    "And the purpose of the primary key is NOT to insure anything except that you can uniquely identify that particular record. The purpose of a unique key is to prevent duplicates. They aren't necessarily the same thing."

    The following gets a little mathy, but these are the foundations of Access. According to C.J. Date, the purpose of the primary key is to insure that there are no duplicates SO THAT YOU CAN UNIQUELY IDENTIFY A ROW. Remember, he was one of the inventors of the Relational Model, along with Codd. It goes like this: a table is a SET, in the strict mathematical sense. Therefore, duplicate elements are not allowed. In Access, we call the elements of this set "rows". Given that equivalency of definition of tables and sets, preventing duplicates and being able to uniquely identify a row are just different ways of stating the same thing. One implies the other. That is, if all rows are unique, then you don't have any duplicates and if you don't have any duplicates, then all rows are unique. That may sound trivial, but that is just the elegance of the math showing through. How Access, or any other RDBMS, manages to insure the uniqueness of primary keys (and therefore rows) is totally irrelevant. It need not be done with indexes; that is only an implementation detail. As long as the uniqueness of primary keys is enforced, and therefore duplicates are disallowed, your RDBMS conforms (at least in that respect) to the Model.

    There also may be more than one column or set of columns which could be chosen for a primary key, but there always has to be at least one column or set of columns that CAN be identified as such or you gotta go back to the drawing board. The alternate choices are referred to as "candidate keys".

    So, what your practice and mine amount to is having two candidate keys, one consisting of the autonum, and the other, the more desirable one, consisting of columns of naturally occuring items (as opposed to the 'faked' autonum column). You do about what I do, except in reverse. I make the 'real' data the primary key, and make the autonum a uniquely keyed field. We get the same effect, I guess, but I believe my way better conforms to the Model.

    In my experience, I've found when I violate the rules, I pay for it, so I try to stay as close as I can. That means I use 'real' data for my primary key even if I use an autonum for more efficient of searches and Joins, and because I am just too darned lazy to write Joins with three or more columns. If the primary key has only one or two columns in it, I never use an autonum. There isn't any significant efficiency issue in this case that I am aware of and Joins are easy enough. But I know I am cheating, that I am violating the principles of the Model. That doesn't justify it, but I've tried it both ways and using an autonum seems to me to be an overriding practical consideration when I do use them.

    -- Paul

  15. #15
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: autonumber

    Yes, I'm acquainted with Date and Codd and their body of work. However, my interpretation of the statements you quoted differs somewhat from yours. That's why programming is an art rather than a science, regardless of its mathematical basis. If it could be reduced to pure mathematics, computers could program themselves competently.

    The fact that the primary key is unique by definition makes every row unique ... by definition. The argument for and against actual data as a primary key can go on endlessly with equal weight and authority quoted on both sides and doesn't contribute much toward answering any particular question, so I prefer not to pursue it.
    Charlotte

Page 1 of 2 12 LastLast

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •