Results 1 to 3 of 3
  1. #1
    Join Date
    Aug 2010
    Posts
    10

    Unanswered: Autonumber is out of sequence

    I have a table with the Autonumber function to create an id number for each record in the table. It is the key for the table. The only purpose of this number is to ensure a unique ID for each record in this table.

    However, the Autonumber function is automatically generating values that are about 7 values less than the previously saved value in the table, therefore, this value is not unique, and I get a key violation.

    I thought I tricked the table by generating and deleting 6 new records, until the autonumber generated a value of the last saved record, and then I closed the table and my append query started functioning again, to add new records to this table.

    But after closing the database, and reopening, the autonumber problem comes back.

    I have the database set to "compact on close", and there are about 7000 records in the table. Some records have been deleted, so the autonumber field does have some gaps in the numbers from 1 to 7000.

    How do I keep the autonumber field in line with the last number it generated? I figured that the database would keep track of that on it's own, and it has for a long time (I've been using this database for about 2 year).

    I am using Access 2010.

  2. #2
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    gaps don't matter, its in internal reference number an autonumber column shoudl have no meaning or value beyond that or outside the system

    my guess is the compact on close is possibly the source of the problem. if you've lasted for 2 years with compact on close then you have done well.

    as to resolutions
    take a backup
    ..but beyond that I don't know


    i'd try changing the datatype from autonumber to long
    save the table, compact and repair the app.
    then set the column back to autonumber.. see if that clears it.

    in any event drop the idea of compact on close...
    I'd rather be riding on the Tiger 800 or the Norton

  3. #3
    Join Date
    Jun 2005
    Location
    Richmond, Virginia USA
    Posts
    2,763
    Provided Answers: 19
    Quote Originally Posted by healdem View Post


    ...my guess is the compact on close is possibly the source of the problem. if you've lasted for 2 years with compact on close then you have done well...
    Healdem is absolutely right about this! C & R is one of the recognized causes of corruption, and so you should always make a backup copy of your file before performing one, which means that Compact on Close is a terrible option! In addition to this, there is no reason, in a correctly designed database, for doing one each time it is closed!

    Here's a link to Allen Browne's article on the problem:

    Microsoft Access tips: Fixing AutoNumbers when Access assigns negatives or duplicates

    Linq ;0)>
    Hope this helps!

    The problem with making anything foolproof...is that fools are so darn ingenious!

    All posts/responses based on Access 2003/2007

Tags for this Thread

Posting Permissions

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