Results 1 to 10 of 10
  1. #1
    New Lounger
    Join Date
    Oct 2001
    Location
    Indiana, USA
    Posts
    21
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Auto Number start point (Access 2000)

    I have a table that uses AutoNumber in primary key field. There are no records in the table, I have deleted any records created while testing. I now need to start AutoNumbering from a specific number. I have copied the table structure, appended a record with the number previous to my start point into my temporary table and then appended this to my empty table.
    I thought this would allow me to use AutoNumber and assign the next in sequence, apparently I was wrong! I still get numbers in sequence from the test records. Any thoughts?

  2. #2
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,623
    Thanks
    3
    Thanked 60 Times in 60 Posts

    Re: Auto Number start point (Access 2000)

    Compact and repair your database with no records in the table. If you have a record with the ID number one less than you want to start with, that process will reset the counter to give you the next number in seqence. Unfortunately you can't specify the number you want to start with like you can in SQL Server.
    Wendell

  3. #3
    Star Lounger
    Join Date
    Dec 2001
    Location
    Fredensborg, Denmark
    Posts
    86
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Auto Number start point (Access 2000)

    I'll share my thoughts. Don't use autonumbering if you want to control numbering. Using autonumbering means that you don't care what the numbers are as long as they are unique, since that is what is needed for a primary key. Don't show the user the autonumber on a form - it's just an internal thing.

  4. #4
    3 Star Lounger
    Join Date
    Mar 2001
    Location
    Minneapolis, Minnesota, USA
    Posts
    262
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Auto Number start point (Access 2000)

    If you'd like it to restart the autonumbering at 1, with the table cleared of any records, choose to compact and repair the database.

    However, if you'd like to begin at a number other than 1, or increment the autonumber value by something other than increments of 1, I believe you have to make use of the ADOX library.
    <font face="Comic Sans MS"><font color=blue>~Shane</font color=blue></font face=comic>

  5. #5
    Silver Lounger
    Join Date
    Jan 2001
    Location
    Indianapolis, Indiana, USA
    Posts
    1,862
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Auto Number start point (Access 2000)

    Hi Mark,

    I agree with Claus about not using AutoNumber for creating specific numbers in most cases - I use =DMax("<KeyFieldName>","<Table>") + 1 for creating an ID number that's one higher than the last.

    However, just for information, you can have AutoNumber start at a given value (such as 1000) by entering the start value in the Format field property (in the Table Design).

    HTH <img src=/S/thumbup.gif border=0 alt=thumbup width=15 height=15>

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

    Re: Auto Number start point (Access 2000)

    I have to go on record as disagreeing with Claus and Mark on autonumbers. I use them extensively because they are not data, they are merely values that serve as keys. My users don't see them and they are only used internally.

    That said, there is NO reason to start an autonumber at a particular point. Compacting the database will reset the autonumber seed value in an empty table, just as Wendell and Shane have suggested. However, unless you have applied at least SR1 to your Office 2000 installation, appending a record to the autonumber field will *not* reset the seed for the next number. That was a bug in the initial version of Access 2000 that was cured by the service release.
    Charlotte

  7. #7
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,623
    Thanks
    3
    Thanked 60 Times in 60 Posts

    Re: Auto Number start point (Access 2000)

    I'll side with Charlotte on this as well. In particular, the technique Mark uses can create race conditions where you end up with duplicate key errors because somebody started an insert, got interrupted (or just typed slower than the other person), and another person adds a record before the other person finishes. IF you insist on controlling the numbering, you should add a table which contains the next record number available, do an update on it with edit locks to increment it by one, and then use the number to key your record with. But it's lots more work than an autonumber.
    Wendell

  8. #8
    Silver Lounger
    Join Date
    Jan 2001
    Location
    Indianapolis, Indiana, USA
    Posts
    1,862
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Auto Number start point (Access 2000)

    It's almost lunchtime and I'm so hungry I could eat my words! <img src=/S/yum.gif border=0 alt=yum width=15 height=15>

    On second thought, I agree with Wendell and Charlotte. <img src=/S/surrender.gif border=0 alt=surrender width=31 height=23>

    The technique I mentioned will only work with Single-user situations. A better solution would be to have an Autonumber field for the Primary Key, then use the DMax formula as the default value for a different "ID" field (such as Invoice Number, etc.).

  9. #9
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts

    Re: Auto Number start point (Access 2000)

    I got the impression that's pretty much what Claus meant - don't use autonumbers for meaningful numbers, only to provide unique keys for internal use.
    Regards,
    Rory

    Microsoft MVP - Excel

  10. #10
    New Lounger
    Join Date
    Oct 2001
    Location
    Indiana, USA
    Posts
    21
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Auto Number start point (Access 2000)

    Thanks for the advice, as you can tell by my questions I am a new user and prone to making some bad decisions in database design. After viewing the posts I realize I have created a bit of a mess and need to change my design.
    I have many tables (some populated with data) and relationships set up, how will changing these key fields affect the database? Am I in a "start-over" situation or can I create new key fields that are AutoNumbers and change my current key fields to Numbers? Any suggestions?
    PS: where did you get the Indiana state flag, that's pretty cool.
    Thanks,
    Mark

Posting Permissions

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