Results 1 to 8 of 8
  1. #1
    Join Date
    Aug 2002
    Posts
    23

    Unanswered: AutoNumber Jumping by 2 or More

    My multiuser app in Access2k sucddenly has deveoped a strange issue. The autonumber field is suddenly incrementing by 2 or 3 instead of 1.

    The app is split into an app file on multiple user's machines, and data files on the server.

    I have tried compacting the data file, and creating a new file and importing the data into it. Nothing seems to work.

    Has anyone seen this before?

    PS: there are only about 4000 records in this table.

    Rick
    A good cook doesn't use a smoke alarm for a timer.

  2. #2
    Join Date
    Dec 2002
    Location
    Préverenges, Switzerland
    Posts
    3,740
    wasn't thinking - reply deleted

    izy

  3. #3
    Join Date
    May 2003
    Location
    Dallas
    Posts
    817
    Provided Answers: 5
    multiple users opening a form in add mode and creating a record will lock an autonumber even if they discard the record, thereby allowing the number to "jump" by two for the next legitimate added record. If the field is truly autonumber I doubt if you could duplicate the error using a single user - have you tried that?

    There are workarounds like auto save once the number is created - remember- even though you see the number created it does not "exist" until that record is saved.

    HTH

  4. #4
    Join Date
    Aug 2002
    Posts
    23
    This is what I tried. Everything was done on the data file with no other users logged in. I open the file exclusively.

    ***
    Open the data file compact and repair.

    "Start" to create a new record. The field increments correctly. Press the <Escape> key to cancel.

    "Start" another record... the record has incremented by 2.

    Note here that I haven't actually created ANY NEW RECORDS!

    Compact and repair

    "Start" another record... the record has incremented by 3.

    ***

    I have never seen this happen before. Compacting always seems to reset the "next number" to 1 more than the "highest number."

    Thanks again
    A good cook doesn't use a smoke alarm for a timer.

  5. #5
    Join Date
    May 2003
    Location
    Dallas
    Posts
    817
    Provided Answers: 5
    OK here is what I tried
    table1 field1 is text
    field2 is autonumber - primary key set
    added 4 records so number is 1-4
    created form and added 5 records but escaped befor saving - and each new potential add incremented by one
    but I did not save any record
    I then CLOSED the form and compacted when I restarted the next new rec strted with 5 as anticipated.

    So now lets look at your structure
    1. Are you using linked tables?
    If so you are only compacting the frontend not the linked tables
    2. Version of Access - tested on 97 but I believe 2000 behavior will be the same.
    3. Do you begin the compaction with ALL objects closed? Including the form for the data entry?

  6. #6
    Join Date
    Aug 2002
    Posts
    23
    All work was done in the data file on the server, which is linked to the app files on the client's machines.

    There are no forms, queries, reports, or code in the data file, so there is nothing else open.

    I have to agree with you though, I have tried the same thing in other apps, and this is the first time I have ever seen this behavior.


    Thanks again

    Rick
    A good cook doesn't use a smoke alarm for a timer.

  7. #7
    Join Date
    May 2003
    Location
    Dallas
    Posts
    817
    Provided Answers: 5
    When you say on the data server - do you mean linked tables?
    If you are using a front end backend structure remember - compacting the frontend does not compact the linked tables. Also if multiple users are accessing the datafiles you are "reserving" an autonumber for the users as they open and add a new (whether saved or not) record.

    Also in a form structure that uses autonumber for adding records, try to keep the user from keeping that form opend in Add mode unless doing true data entry.

  8. #8
    Join Date
    Aug 2002
    Posts
    23
    I am in the "backend" when I compact. I think the problem may be associated with the "reserved" numbers though. I will check in that direction and see if I can come up with a solution.

    Once again... Thanks

    RR
    A good cook doesn't use a smoke alarm for a timer.

Posting Permissions

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