Results 1 to 7 of 7
  1. #1
    4 Star Lounger
    Join Date
    Jun 2001
    Location
    Edinburgh, Midlothian, Scotland
    Posts
    492
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Compact & Repair (Access 2003)

    I run a database keeping a financial record for a school club. I know its not necessary, but I like all my entries to be consecutively numbered (using an autonumber field), as I feel that occasional missing numbers may look suspicious to an auditor.

    Any errors I make therefore, I have traditionally corrected using Compact & Repair to re-set the autonumber. Today however, I've run the database on a different PC which uses Access 2003 (The file is in Access 2000 format). I've tried a dozen times, and the autonumber will not re-set. I can't find anything in the help file. Does Access 2003 work differently if a file is an 2000 format?

    Thanks

  2. #2
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Compact & Repair (Access 2003)

    I am not yet running 2003, but trying to keep autonumbers sequential has always been an exercise in frustration. Nor is it the intended behavior or use of the autonumber. They are only incidentally sequential if you choose an incremental type. The numbers themselves are meaningless, and you get into trouble when you try to make them meaningful. There were problems in Access 2000 with it not resetting the autonumber seed on compact, although those were at least partially cured in the service packs. However, I believe the problem you're encountering may be a result of the Jet used by 2003, since the resetting of the autonumber seed got broken again in one of the later Jet service packs.
    Charlotte

  3. #3
    4 Star Lounger
    Join Date
    Jun 2001
    Location
    Edinburgh, Midlothian, Scotland
    Posts
    492
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Compact & Repair (Access 2003)

    Yes I understand I am using the autonumber for something it is not designed for, but it has been very handy.

    The help file in Access 2003 states

    "However, in an Access database, if you have deleted records from the end of a table that has an AutoNumber field, compacting the database resets the AutoNumber value; the AutoNumber value of the next record you add will be one greater than the AutoNumber value of the last undeleted record in the table."

    So it should still work, but it may be that I have to use another field, and set up a new numbering system which I increment myself.

    Thanks

  4. #4
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Compact & Repair (Access 2003)

    The help files have always said that. Up until Access 2000, it was true. Since then, it seems to depend on the Jet service packs and the Office patches. <img src=/S/shrug.gif border=0 alt=shrug width=39 height=15>
    Charlotte

  5. #5
    4 Star Lounger
    Join Date
    Jun 2001
    Location
    Edinburgh, Midlothian, Scotland
    Posts
    492
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Compact & Repair (Access 2003)

    Yeah, sorry, that was naive of me to believe the Help File.

    Thanks anyway

  6. #6
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,624
    Thanks
    3
    Thanked 60 Times in 60 Posts

    Re: Compact & Repair (Access 2003)

    <!mskb=287756>Microsoft Knowledge Base Article 287756<!/mskb> suggests that any version of the Jet 4.0 Service Pack from 4 up (8 is the latest) fails to reset the autonumber.
    Wendell

  7. #7
    4 Star Lounger
    Join Date
    Jun 2001
    Location
    Edinburgh, Midlothian, Scotland
    Posts
    492
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Compact & Repair (Access 2003)

    Yes that seems to confirm the problem.

    I'll leave thinsg as they stand.

    Thanks anyway.


    Colin

Posting Permissions

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