Results 1 to 5 of 5
  1. #1
    3 Star Lounger
    Join Date
    Mar 2003
    Location
    Elkins Park, Pennsylvania, USA
    Posts
    325
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Autonumber Duplicating? (Access 2002)

    Hi, again.

    I "own" a database used by my department that is used to track jobs (we're a word-processing center). It's a relational database that creates a record of the job that was submitted to be worked on and which is linked to another table that lists all of the documents processed for each job (e.g. one job can comprise 3 Word documents.) Each job is assigned a unique number (using Access' Autonumber field-type) and each document processed is assigned it's own unique number, too. (I don't go by filenames because they sometimes get changed.) About two months ago, I split the database into a front-end/back-end system (because we have two sites and because I need to modify reports, queries, etc. from time to time.)

    The other day the administrative assistant at my site told me that she noticed a problem with the number assigned to one of the jobs. She showed me a report from May 25th. On the report for that day, there was a listing for job 35708. (We're a very busy center!) Then she showed me a report from June 1, and on that report, job number 35708 was an entirely different job! This has never happened in the 7 years that we've been using this database.

    Does anyone know if it is possible that a front-end/back-end system might lead to such a problem? As I said, the job number is an Autonumber field, indexed with no duplicates allowed. Aside from the obvious possibility that the admin overwrote the entire contents of the previous job (and hasn't fessed up), might there be some chance that having two admins at each site opening a new record at the same time might have caused a duplication of the Autonumber field? Or is there a possibility that there was a problem with the index?
    <font face="Comic Sans MS">That's what you do in a herd; you look out for each other!</font face=comic> - Mike

  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 Duplicating? (Access 2002)

    Are these "sites" both sharing the same back end? If they share a backend and you're dealing with backend tables, you shouldn't be getting duplicate autonumbers. In fact, if the autonumber is the PK or a unique key, you shouldn't be *able* to get duplicates without getting a duplicate key or index error. If the autonumber field is NOT set up as unique, then there is no guarantee you won't get the same number assigned somehow. In fact, there isn't much point in using an autonumber unless you do make it a unique key.
    Charlotte

  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: Autonumber Duplicating? (Access 2002)

    Are both jobs still in the table? If they are, then you indeed have a problem. My guess is that they aren't.
    Mark Liquorman
    See my website for Tips & Downloads and for my Liquorman Utilities.

  4. #4
    3 Star Lounger
    Join Date
    Mar 2003
    Location
    Elkins Park, Pennsylvania, USA
    Posts
    325
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Autonumber Duplicating? (Access 2002)

    Thanks for both of your replies.

    No, both jobs are not in the database at this time (and I'm not sure if they ever actually were.) Yes, the Autonumber field is set so that duplicates are not allowed.

    I didn't think such a thing was possible either, but I wanted the opinions of others, too, before I wasted a lot of time trying to figure out how my database allowed such a thing. Bearing in mind that this admin is the same person who fudges the Date Expected field "...so we don't look bad", I'm going to chalk up this incident to OE.
    <font face="Comic Sans MS">That's what you do in a herd; you look out for each other!</font face=comic> - Mike

  5. #5
    4 Star Lounger
    Join Date
    Aug 2002
    Location
    Dallas, Texas, USA
    Posts
    594
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Autonumber Duplicating? (Access 2002)

    There is a bug in Access 2000 (which I think the latest service pack fixes), which goofs the Autonumber, during a compact. It will let the system try to 'reuse' an existing number. Just an FYI.

Posting Permissions

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