Page 1 of 2 12 LastLast
Results 1 to 15 of 19
  1. #1
    New Lounger
    Join Date
    Jan 2011
    Posts
    10
    Thanks
    1
    Thanked 0 Times in 0 Posts

    MS Access 2010 Compact & Repair Breaks AutoNumber

    Steps to duplicate:
    Create new empty mdb or accdb
    Create new Table1
    Fields: ID, autonumber, PK ascending
    F2, text (255)
    Add 1003 records
    Compact & Repair
    Add record 1004 manually, ok
    Change PK to Descending
    C&R
    Add record 1005 manually, FAIL due to duplicate ID
    Conclusion: Following a C&R, if more than 1001 records, insert fails when PK on autonumber field is descending.

    Change PK to Ascending
    C&R
    Add record 1006 manually, ok
    Conclusion: Following a C&R, if more than 1001 records, insert fails when PK on autonumber field is descending.

    Can anyone else duplicate this?
    We do not see this problem with Access 2007 or 2003.

    MS Access 2010 sp1 32bit
    Win 7 64bit

  2. #2
    Administrator
    Join Date
    Jun 2010
    Location
    Portugal
    Posts
    12,519
    Thanks
    152
    Thanked 1,398 Times in 1,221 Posts
    Hi,

    Sorry, I failt to understand why you would mess with the autonumber sequence, after a compact and repair (or at any time, after having database records, for that matter).

  3. #3
    New Lounger
    Join Date
    Jan 2011
    Posts
    10
    Thanks
    1
    Thanked 0 Times in 0 Posts
    We do not mess with autonumber. Access 2010 does during a compact & repair if we have more than 1001 records and PK sort descending.

  4. #4
    Administrator
    Join Date
    Jun 2010
    Location
    Portugal
    Posts
    12,519
    Thanks
    152
    Thanked 1,398 Times in 1,221 Posts
    This seems to be a known issue: http://msmvps.com/blogs/access/archi...escending.aspx

    Maybe this info can help: http://allenbrowne.com/ser-40.html

    Besides that, sorry if I insist on the autonumber thing, why do you use descending autonumbers? I know it may seem even irritating, but you shouldn't really have a need for a specific autonumber sequence. If you do, probably you should handle the primary key generation yourself, IMHO.
    Last edited by ruirib; 2011-08-18 at 17:23.

  5. #5
    New Lounger
    Join Date
    Jan 2011
    Posts
    10
    Thanks
    1
    Thanked 0 Times in 0 Posts
    Thanks ruirib,
    That msmvp post was by one of my contacts at Experts Exchange. Note it was just posted 8/17/2011.
    I cannot find anything on allenbrowne.com
    My question was, "Can anyone else duplicate this? We do not see this problem with Access 2007 or 2003."
    Steve

  6. #6
    Administrator
    Join Date
    Jun 2010
    Location
    Portugal
    Posts
    12,519
    Thanks
    152
    Thanked 1,398 Times in 1,221 Posts
    I am sorry, I should have checked Access 2010 properly. How do you get decrementing autonumber fields? You set a negative increment?

    On allenbrowne.com you have code to check every table's seed value. With some changes you could use it to fix your problem, if I understood it correctly.
    I also doubt that many people use autonumbers set to decrement...

  7. #7
    New Lounger
    Join Date
    Jan 2011
    Posts
    10
    Thanks
    1
    Thanked 0 Times in 0 Posts
    Open table in design view. Select Indexes.
    Please understand that we have had over 300 sites run this application in various versions leading up to Access 2010. Our choice to use descending is not the issue.
    Can you duplicate this problem as we have outlined?

  8. #8
    Administrator
    Join Date
    Jun 2010
    Location
    Portugal
    Posts
    12,519
    Thanks
    152
    Thanked 1,398 Times in 1,221 Posts
    The autonumber field is not decrementing. You chose to have the index associated with it sorted descending. I hadn't really understood that.

    Anyway, I can duplicate the problem. The code at allenbrowne.com can indeed be a solution for your issue as it will reset the seeds of the tables that experience the problem. I ran an ALTER TABLE statement with my test table, to reset the autonumber seed value, and everything worked normally after that.

  9. #9
    Super Moderator
    Join Date
    Jun 2002
    Location
    Mt Macedon, Victoria, Australia
    Posts
    3,993
    Thanks
    1
    Thanked 45 Times in 44 Posts
    I have just tested this and was able to duplicate the problem.
    Initially I just sorted the table into descending order. This did not cause problems.
    Then I changed the Index on the autonumber field to Descending, Compacted again and was unable to add a new record because the ID assigned matched an existing one.

    But why would you ever want the index to be in descending order?
    Regards
    John



  10. #10
    New Lounger
    Join Date
    Jan 2011
    Posts
    10
    Thanks
    1
    Thanked 0 Times in 0 Posts
    Thanks ruirib,
    Now try compacting after applying the reseed. It will brak again.

  11. #11
    New Lounger
    Join Date
    Jan 2011
    Posts
    10
    Thanks
    1
    Thanked 0 Times in 0 Posts
    Thanks John,
    Please understand that over the last 10 years we have had over 300 sites running this application in various versions leading up to Access 2010. Our choice to use descending is not the issue.
    Steve

  12. #12
    Administrator
    Join Date
    Jun 2010
    Location
    Portugal
    Posts
    12,519
    Thanks
    152
    Thanked 1,398 Times in 1,221 Posts
    I will say again, but for the last time, though. I see no reason to keep the index on the autonumber in descending order. If you need to rely on a specific order for record presentation, you should do that on the query that retrieves the records to display. One of the principles of relational databases is that you should not presume any order for the table records....

    Considering that Access 2010 seems to have an issue with that situation (and it should not have, it's an obvious bug), probably the easiest way to deal with it is to change the ordering of the primary key index. If you choose not to do that, then you will need to run code to reset any autonumber fields, after every single database compact operation.

    Best Regards

    Rui

  13. #13
    Super Moderator
    Join Date
    Jun 2002
    Location
    Mt Macedon, Victoria, Australia
    Posts
    3,993
    Thanks
    1
    Thanked 45 Times in 44 Posts
    I understand that you have chosen to use Descending, and so this is a big issue for you.

    But I am puzzled about what is achieved by using a Descending index.
    Regards
    John



  14. #14
    Administrator
    Join Date
    Jun 2010
    Location
    Portugal
    Posts
    12,519
    Thanks
    152
    Thanked 1,398 Times in 1,221 Posts
    Quote Originally Posted by johnhutchison View Post
    I understand that you have chosen to use Descending, and so this is a big issue for you.

    But I am puzzled about what is achieved by using a Descending index.
    I am too, John, and what I noticed was the Access 2010 displays the records according to the primary key index order, so I guessed it must be for that reason, cause I can't really see any other effect of choosing to do so.

  15. #15
    New Lounger
    Join Date
    Jan 2011
    Posts
    10
    Thanks
    1
    Thanked 0 Times in 0 Posts
    Thank you all.
    Anyone have ddl code to to change the primary key to ascending?
    Steve

Page 1 of 2 12 LastLast

Posting Permissions

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