Results 1 to 8 of 8
  1. #1
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Melbourne, Victoria, Australia
    Posts
    314
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Autonumber goes back to an earlier value! (Office 2000 Sr1a)

    This has happened TWICE now.
    Suddenly I can't add any more records to my Contacts table because the value of the Primary index which is an Autonumber field has somehow reset itself to an earlier value (2917 instead of 8769). So there is a message objecting that I can't add any record because this will create duplicate values in the Primary index. True enough.
    But why does this happen.
    Sure, I have been doing some coding lately in ADO to add records to this Contacts table as well as a Calls table via an Inner join query on the two but I don't really know why any activity here could reset the autonumber.
    The database is split by the way into a front and back end.
    Any ideas what might be happening?

  2. #2
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Edmonton, Alberta, Canada
    Posts
    326
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Autonumber goes back to an earlier value! (Office 2000 Sr1a)

    I've had this happen once to one of my backends. Given the lack of any other possibilities (I hadn't made any design changes anywhere close to the time it happened) I assumed it was corruption.

    As I recall I used Jetcomp on the backend to fix it.

  3. #3
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Melbourne, Victoria, Australia
    Posts
    314
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Autonumber goes back to an earlier value! (Office 2000 Sr1a)

    Thanks Douglas but what is JetComp?

  4. #4
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Melbourne, Victoria, Australia
    Posts
    314
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Autonumber goes back to an earlier value! (Office 2000 Sr1a)

    Don't worry, I found JetComp at Microsoft's website.

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

    Re: Autonumber goes back to an earlier value! (Office 2000 Sr1a)

    There was a bug in vanilla A2k that caused it to misbehave like that when values were appended to an autonumber field. The append did not reset the seed even after a compact on the database. SR1 was supposed to have cured that, and it did so on my machine. Are you sure you still have SR1 installed?
    Charlotte

  6. #6
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Melbourne, Victoria, Australia
    Posts
    314
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Autonumber goes back to an earlier value! (Office 2000 Sr1a)

    Yes I do - I just double-checked.
    And I didn't try to append to an autonumber field either.
    The only similar operation I tried to perform was
    (in code) adding a record to the Calls table without putting any value for the field ContactID which is required in my database by referential integrity being set for the relationship between the Contacts and Calls table. But I realised I didn't have "Values required" set to "yes' for the remote key.
    Seems a weird requirement - you can't put a value for the remote key that isn't in the primary table but you CAN put no value at all.
    This is referential integrity?????
    I've corrected this but maybe having no remote key can wreak havoc under certain circumstances?

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

    Re: Autonumber goes back to an earlier value! (Office 2000 Sr1a)

    The alternative would be that you could not create a child record until the parent record had been created and saved, which is not always possible if you're using an append query to both tables at once. And yes, it is referential integrity. You can't just stuff anything in that foreign key, it has to be a valid one.
    Charlotte

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

    Re: Autonumber goes back to an earlier value! (Office 2000 Sr1a)

    I encountered this problem myself while working on an article and tracked down a knowledgebase article that explains but does NOT satisfy. Take a look at MSKB article 287756. Both 2000 and 2002 use Jet 4.0, so this explains why a problem fixed in Access 2000 SR-1 could be broken again. Unfortunately, it doesn't say anything at all about a fix for the problem.
    Charlotte

Posting Permissions

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