Results 1 to 8 of 8
  1. #1
    5 Star Lounger
    Join Date
    Mar 2001
    Posts
    989
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Use of AutoNumbers (2003)

    I understand that it is considered appropriate to use AutoNumbers extensively as primary keys. Other advice suggests that primary keys should, where possible, have meaning in the context of the database.
    When should AutoNumbers be used? Is the issue of non-consecutive numbering a serious impediment to their use? Thanks, Andy.

  2. #2
    4 Star Lounger SteveH's Avatar
    Join Date
    Jan 2001
    Location
    Chelsea, Gtr London, United Kingdom
    Posts
    587
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Use of AutoNumbers (2003)

    My take on this is as outlined in my <!post=Tips 'n' Tricks handout,515889>Tips 'n' Tricks handout<!/post>.

    Using meaningful data as a Primary Key will probably come back to haunt you later when the client changes the nature of that data. Non-consecutive autonumbers makes no difference at all - in fact you can set autonumber to Random rather than Increment if you wish.
    Steve H
    IT Lecturer/Access Developer
    O2K SR3/O2010; Win7Pro

  3. #3
    Gold Lounger
    Join Date
    Jun 2001
    Location
    Crystal Beach, FL, Florida, USA
    Posts
    3,436
    Thanks
    1
    Thanked 34 Times in 34 Posts

    Re: Use of AutoNumbers (2003)

    From a sheer convenience standpoint (that is, to anyone writing code that has to join tables, etc.), it is easier to uniquely identify a record with a single field, rather than the sometimes 2 or more fields that might otherwise be necessary. Performance will be better also. And you can still create unique indexes on these other fields to ensure uniqueness, so you haven't given up anything. I really can't think of a situation where using an autonumber as a PK would be the wrong solution; most cases it is the best solution.
    Mark Liquorman
    See my website for Tips & Downloads and for my Liquorman Utilities.

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Use of AutoNumbers (2003)

    Just to confirm what the others have already written: AutoNumbers are the ideal Primary Key field. They only take up 4 bytes per record, they are set by Access as soon as you create a record, and they never change. There is no need for the AutoNumber field to be meaningful.

    PS Other than Mark, I generally don't use an AutoNumber field in the join table of a many-to-many relationship. For example, if you have tblPersons with AutoNumber primary key PersonID, and a table tblHobbies with AutoNumber primary key HobbyID. The join table tblPersonHobbies that lists which hobbies each person has, has a primary key consisting of the combination of PersonID and HobbyID. It would be possible to use an AutoNumber field in this table, but I prefer not to. This is mostly a matter of taste. I don't want to imply that Mark's approach is wrong.

  5. #5
    Gold Lounger
    Join Date
    Jun 2001
    Location
    Crystal Beach, FL, Florida, USA
    Posts
    3,436
    Thanks
    1
    Thanked 34 Times in 34 Posts

    Re: Use of AutoNumbers (2003)

    Hans,

    Like you said, using an autonumber as the PK in a "resolver" table of a many-to-many is a matter of personal taste. I've done it both ways at various times. I personally just found that having the autonumber as the PK sometimes made it a little easier when I was working with this table within a subform or a listbox.

    Once upon a time there was a discussion thread on this subject on the old Access forum on Compuserve. Some of the heavy hitters for Access and SQL (like Joe Celko, etc.) weighed in on the subject. I thought I had a copy of it, but it was from 2 or 3 computers ago.
    Mark Liquorman
    See my website for Tips & Downloads and for my Liquorman Utilities.

  6. #6
    5 Star Lounger
    Join Date
    Mar 2001
    Posts
    989
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Use of AutoNumbers (2003)

    Thanks again for all the input folks. Like most Access questions the answer tends to be "it depends". I suppose if sequential numbering (without gaps) were really a concern/ distraction, it's always possible to use DMAX() to obtain the next available number. Thanks again, Andy.

  7. #7
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Use of AutoNumbers (2003)

    See <post:=287,907>post 287,907</post:> for some examples of assigning a sequential number using DMax.

  8. #8
    Gold Lounger
    Join Date
    Jun 2001
    Location
    Crystal Beach, FL, Florida, USA
    Posts
    3,436
    Thanks
    1
    Thanked 34 Times in 34 Posts

    Re: Use of AutoNumbers (2003)

    Yes, just put the code in your form's Before Update event, something like this:

    If Me.NewRecord = True
    Me.ID = nz(DMax("ID","myTable),0) + 1
    End If
    Mark Liquorman
    See my website for Tips & Downloads and for my Liquorman Utilities.

Posting Permissions

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