Results 1 to 6 of 6
  1. #1
    2 Star Lounger
    Join Date
    Feb 2002
    Posts
    155
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Autonumber not working (A2K SR-1)

    I have a table with an an autonumber as the ID in a local database. Recently when I tried to enter new data by manually opening it, I discovered that the autonumber isn't working as expected - instead of a new number after the existing highest value, the new record had an ID which was already given to an existing record, resulting in my new record not accepted by Access. I tried to solve the problem by using Access's Compact and repair database but without success. After compacting and repairing, the new record that I tried to enter has a different autonumber ID but which was still used by another existing record.

    Does it mean that my table is corrupted? How should I do so that the autonumber is working correctly again?

    How can I detect such problem (collision of autonumber) using codes?

  2. Subscribe to our Windows Secrets Newsletter - It's Free!

    Get our unique weekly Newsletter with tips and techniques, how to's and critical updates on Windows 7, Windows 8, Windows XP, Firefox, Internet Explorer, Google, etc. Join our 480,000 subscribers!

    Excel 2013: The Missing Manual

    + Get this BONUS — free!

    Get the most of Excel! Learn about new features, basics of creating a new spreadsheet and using the infamous Ribbon in the first chapter of Excel 2013: The Missing Manual - Subscribe and download Chapter 1 for free!

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

    Re: Autonumber not working (A2K SR-1)

    Almost certainly you have a corrupt table, though it's surprising that the compact and repair didn't fix it. There was a problem in the intitial release of 2000 that caused this kind of problem, but it was fixed in SR1, and we haven't seen it for two or three years. In order to fix the problem, you will probably need to create a new table with empty records, and then run update queries to take the values from the existing table and populate the new table, and then delete those records where there isn't a matching record in the old table. This assumes you were using an autoincrement and not random numbers. If they were random, the Jet engine would take care of duplicates and simply assign a new number when it hit an existing record match. Hope this makes sense and is helpful.
    Wendell

  4. #3
    3 Star Lounger
    Join Date
    Dec 2000
    Location
    La Verne, California, USA
    Posts
    311
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Autonumber not working (A2K SR-1)


  5. #4
    2 Star Lounger
    Join Date
    Feb 2002
    Posts
    155
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Autonumber not working (A2K SR-1)

    After visiting Preston's helpful link, I guessed the table corruption might be due to the following cause:

    One of the desktops that was used in entering the data has MS Jet version 4.00.2927.17 (SP3) and mine is 4.00.6218.0 (SP6). The same back-end database had been processed by these two different builds of MS Jet. These different versions of the Jet engine might have caused the corruption.

    Following your advice, I created a new table and copied the data from the corrupted table to the new one. The new table plus the autonumber primary key field are working fine now. There is no more duplicates of the key autonumber field.

    Thanks a lot, Wendell.

  6. #5
    2 Star Lounger
    Join Date
    Feb 2002
    Posts
    155
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Autonumber not working (A2K SR-1)

    I followed Preston's pointer and visited the Microsoft's web site and its related links. I became a little disenchanted with the whole thing:
    (1) I need to know the OS version of the system that is going to use my MS Access's program. Windows 95/98/Me/NT/2000/XP each has a different update for the same version number of the Jet engine. One that is working perfectly in Windows 98 may not work well in Windows Me, for instance, because the updated Jet engine is different.

    (2) I need to ensure that the computer that is going to use my program have the same updated version of the Jet engine. To help update the targeted computer, I have to know its OS plus its updates, and the Jet engine plus its updates.

    These two considerations are really a pain in the ass if I want to make certain what works fine in mine work fine in the other computers too, though they all have MS Access 2000.

    Thanks Preston for giving me the valuable link.

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

    Re: Autonumber not working (A2K SR-1)

    The reason for the different versions is because Win2k and later use the Windows installer, while earlier versions of Windows didn't.
    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
  •