Results 1 to 11 of 11
  1. #1
    3 Star Lounger
    Join Date
    Sep 2002
    Location
    London, England
    Posts
    294
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Autonumber seems to have rest itself (2000)

    I have a table in an mdb database where the primary key, an Autonumber, appears to have reset itself. The result of this is that the users cannot add any new records to this table because the numbers being generated already exist. The Autonumber is of the incremental type. During development the table had test data in it but before it was delivered all the transaction tables were emptied.

    I think it may be possible to do odd things to autonumbers by appending records to the table that already have values in the autonumber field. However, I don't think that the users in this case are capable of doing this.

    Does anybody know of any glitches or goltchas with autonumbers that might have lead to this? And how do I get out of it? Should I append a dummy record with a large value in the autonumber field? Could I therefore reset the autonumbers to carry on from 1,000,000 say?

    Ian

  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 seems to have rest itself (2000)

    The answer depends on the service release of Office 2000 you're running and the Jet service packs you have installed.
    Charlotte

  3. #3
    Silver Lounger
    Join Date
    Jun 2001
    Location
    Niagara Falls, New York, USA
    Posts
    1,878
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Autonumber seems to have rest itself (2000)

    Hi Ian

    Check out post <post#=1917>post 1917</post#> Create Duplicate Values In The Index Message? (a2k (9.0.3821) SR-1 Jet 4.0)

    Mark Liquorman has posted some fix it code on this forum, matbe he will chime in

    Please post back your solution

    HTH

    John

  4. #4
    3 Star Lounger
    Join Date
    Sep 2002
    Location
    London, England
    Posts
    294
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Autonumber seems to have rest itself (2000)

    Hi John. The link you included takes me to a post called 'Optimal Browser Settings for Using these Boards'.
    I'll try searching for the title but last time I tried to do a search I was told that the facility had been removed.

    Ian

  5. #5
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Autonumber seems to have rest itself (2000)

    Support4John probably meant the thread starting at <post#=301645>post 301645</post#>.

    Search has been back since the end of December.

  6. #6
    3 Star Lounger
    Join Date
    Sep 2002
    Location
    London, England
    Posts
    294
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Autonumber seems to have rest itself (2000)

    (Edited by HansV to make URL clickable - see <!help=19>Help 19<!/help>)

    Yes, I found what looks like a solution at http://members.iinet.net.au/~allenbrowne/ser-40.html. I'll let you know....

  7. #7
    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 seems to have rest itself (2000)

    As John pointed out, I have a fix for that problem on my website (see the link below). The cause appears to be Append queries. Supposedly there is an SR that fixed this, but I'm not 100% sure the problem ever got completely solved.
    Mark Liquorman
    See my website for Tips & Downloads and for my Liquorman Utilities.

  8. #8
    Silver Lounger
    Join Date
    Jun 2001
    Location
    Niagara Falls, New York, USA
    Posts
    1,878
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Autonumber seems to have rest itself (2000)

    Hi Mark

    What is the difference between your code and the following?

    Provided by Allen Browne, allen@allenbrowne.com, August 2003
    Resetting AutoNumbers

    John

  9. #9
    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 seems to have rest itself (2000)

    I provide 2 ways to fix the problem, ADO or DAO. Allen's approach relies on ADO. The ADO solution does require specific ADO libraries be referenced which may or may not be a problem at a particular site, and it does require the database not have any other users in it. I wrote mine such that it would operate as a separate utility, in which you specify which database (and tables) you want to fix. Allen's appears to operate within a database, so you'd have to import it into each database you need to fix. The ADO solution (either his or mine) is technically probably a better solution than the DAO method, which employs sort of a brute force method to accomplish the task (it can't reseed the autonumber directly as can be done using ADO). The DAO method, however, doesn't require exclusive access to the database, and it too is a separate database that prompts you for the database to correct.

    I doubt that either is "better"; one may offer a feature the other doesn't, but they both should do the job for you.
    Mark Liquorman
    See my website for Tips & Downloads and for my Liquorman Utilities.

  10. #10
    3 Star Lounger
    Join Date
    Sep 2002
    Location
    London, England
    Posts
    294
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Autonumber seems to have rest itself (2000)

    John, your solution worked! Thanks.

    What are the chances of it happening again? Is there a 2000 version fixes the problem?

    Ian

  11. #11
    Silver Lounger
    Join Date
    Jun 2001
    Location
    Niagara Falls, New York, USA
    Posts
    1,878
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Autonumber seems to have rest itself (2000)

    IanR

    Your guess is as good as mine and probably as good as the high-ranking WMVP

Posting Permissions

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