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

    Autonumber reset again (2000)

    I have a database that has reset the autonumber seed in one of its tables so that the newly created numbers already exist. As the autonumber field is the PK, new records cannot be saved. I was pointed to a fix last time I posted this problem here which worked well but I am worried that this problem is going to keep on happening and make the database unuseable. Once I've run the fix to reset the seed Is there anything that I can do to the database or the table to ensure that it doesn't hap;pen again? I told the user that it was a very rare event (I had never seen it before) but now it's gone and happened to his database twice.

    Ian

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

    Re: Autonumber reset again (2000)

    As Support4John wrote in the previous thread on this subject, it's hard to pinpoint what triggers this error. Which version of Jet 4.0 is running on the problem PC? Recent SP releases of Jet 4.0 are supposed to have solved this problem (see <!mskb=257408>Microsoft Knowledge Base Article 257408<!/mskb>)

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

    Re: Autonumber reset again (2000)

    Hi Hans,

    The knowledge Base article that you have pointed me to leads to a download KB page (239114 ) that mentions SP8 as the latest version of Jet 4.0. It gives a table for working out which version you are on based on the file version of the copy of msjet40.dll in WindowsSystem32. Version number 4.0.8015.0 equates to SP8 according to this table.

    Unfortunately both my machine and the user's problem machine have versions of msjet40.dll that are greater than this (4.00.8618.0).

    Do you know if there is a version after SP8?
    If I download a service pack from this page, and it is lower than the one installed, will it detect this and fail to run?
    Also, the database is split into a front end and a back end. Am I right in thinking that the version of Jet installed on the back end machine is irrelevant?

    Thanks again

    Ian

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

    Re: Autonumber reset again (2000)

    I just checked, and I also have version 4.00.8618.0 on my work PC, dated 16 March 2004, so it is very recent. I wouldn't recommend downloading another version in this case (although as far as I know, the installer would warn you if you try to install an older version, and recommend to keep the newer one.) Since the backend doesn't run anything, the jet version installed there should not matter.

    It's a baffling problem; I have tens of databases that are used day in day out by varying numbers of users, and have never had duplicate autonumbers...

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

    Re: Autonumber reset again (2000)

    If you have that version, you are current as far as I know - I have the same version as well dated March 2004. To answer your question about the version in the back-end, you are correct - the version on the front-end PC is what gets used. In your situation, I would suspect something other than the Jet engine. I would look at the possibility of a corrupted index, any problems with network connections being flakey, and any rouge code either altering existing numbers or adding duplicates with append operations, etc. We have a number of clients using 2000 based systems, and I've never seen a duplicate autonumber. As an alternative, if it continues to happen you might consider creating a table with the next available number and rolling your own - we have used that in systems quite successfully.
    Wendell

Posting Permissions

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