Results 1 to 7 of 7
  1. #1
    Join Date
    Jul 2011
    Posts
    14

    Unanswered: Autonumber c*ck up

    Hi all

    I have an issue with an autonumber field in a table.

    Somehow, recently, the same number has been issued twice.

    OK, I've realised this is an issue so want to change the field properties to indexed with no duplicates to ensure it doesn't happen again.

    However, it won't let me change this while there are duplicate numbers in the table and I am unable to modify the numbers manually.

    My next thought was to delete the relevant records, however as so much is linked throughout, it won't allow me to do that either.

    Any suggestions?

  2. #2
    Join Date
    May 2004
    Location
    New York State
    Posts
    1,178
    I had this problem once. I think the solution was to delete the autonumber field altogether, and save and close the table. Open it up again in design mode and put the autonumber field back in. Copy your table before you try it, though, I'm not 100% sure it works.

    Sam

  3. #3
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    I had this problem once too and saving and closing the table was not enough. I had to Compact/Repair the database, otherwise the problem with the AutoNumber field reappeared when I recreated the column. In every case, copy the table, or even better, make a backup of the database before anything else.
    Have a nice day!

  4. #4
    Join Date
    Jul 2011
    Posts
    14
    Thanks for the advice guys.

    The problem I have is that field is for 'quote' numbers, and with 1200 already on the system, deleting the the field and adding it back, while it works a treat for solving the duplication issue, it 'reallocates' all the numbers, so quote numbers on file, won't match whats on the system.

    It's so frustrating as I can't prevent future errors until this one is fixed - yet it won't allow me to fix it.

  5. #5
    Join Date
    Jul 2011
    Posts
    14
    I've decided to bite the bullet on this one and delete the field, then add it again, with 'no duplicate' properties.

    Not the ideal solution, but better now than later on when there's even more to go wrong....

  6. #6
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by matt1174 View Post
    I've decided to bite the bullet on this one and delete the field, then add it again, with 'no duplicate' properties.
    what happened to "My next thought was to delete the relevant records, however as so much is linked throughout, it won't allow me to do that either" ?

    even if it does let you delete the column and reassign the autonumbers, won't this create an even bigger c*ck up because the linked tables will then potentially point to the wrong rows???
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  7. #7
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    Id check your relationships by the way. make certain you are using referential integrity when defining relationships.

    I suspect that changing the actual data isn't going to be be to tricky, providing you have the paperwork to hand so you know which child rows belong to which parent.

    so changes
    make certain no one is using the db
    take a backup copy and work on that copy
    dont trust your users so rename the live copy to something else so the users cannot open the db whilst you are applying changes
    remove realtionships on the relevant tables, and remove any relationships which are not flagged to 'enforce referential integrity'
    change the datatype of the relevant columns to long (the datatype autonumber uses)
    edit the offending duplicated number and set the child rows to that new number
    take a backup
    compact and repair the db
    set the datatype to autonumber and make it the primary key
    re definw the realtionships you deleted earlier
    I'd rather be riding on the Tiger 800 or the Norton

Posting Permissions

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