1. Computer problem? Tech Support Guy is completely free -- paid for by advertisers and donations. Click here to join today! If you're new to Tech Support Guy, we highly recommend that you visit our Guide for New Members.

Solved: Major problem with Access dB

Discussion in 'Business Applications' started by DKTaber, Jan 15, 2011.

Thread Status:
Not open for further replies.
Advertisement
  1. DKTaber

    DKTaber Thread Starter

    Joined:
    Oct 26, 2001
    Messages:
    2,871
    Created a database containing a table DMMemVol which houses all the personal info on members of an organization. All records have an ID, which is the primary key. I updated and appended additional records from a spreadsheet. I then noticed that 3 records from the spreadsheet hadn't appended, so tried to append them via an append query. Kept getting a "key violation" error, so tried to enter them manually. Get same error.

    Each time I go to enter any new record, the autonumber is the number of a prior record. Last autonumber is 790, but when I try to enter an new record, the autonumber shows as 89, 90, 91. . . one higher each time I try to enter a record. Each of those IDs are already taken by other records. The problem is obviously a corrupted ID index. How can I reindex a database so it will have all the existing IDs and will stop blocking me from entering new records?

    FYI, I have tried "compact and repair" and removing the relationship links between tables and removing/saving/then again establishing the primary key. Nothing works.

    HELP!
     
  2. OBP

    OBP

    Joined:
    Mar 8, 2005
    Messages:
    19,895
    Don, I have seen something similar before.
    I would Copy the database.
    Copy the problem table and pasting it Structure Only,
    Add a field called oldID.
    Compact & Repair.
    Create an append query for all fields and with the ID appending to the OldID field.
    Remove the relationship to a related table.
    Create a Select query from the related table with the linking field.
    Add the old table, linked ID to linking field in the related table. Add the new table linked OldID to problem table ID.
    Change to an Update query.
    In the "Update to" row put the [newtable].[ID] and run it. (where newtable is the actual name of the new table)
    This should replace the original ID in the Linking field with the new ID from the new table.
    Create the relationship between the new table and the related table.

    I know it is a lot of work, but it should work.
     
  3. DKTaber

    DKTaber Thread Starter

    Joined:
    Oct 26, 2001
    Messages:
    2,871
    As usual, Tony, you came thru. It definitely would have been a lot of work and the process is fraught with peril. Luckily, I didn't have to do it because of an observation I had yesterday and again this morning. The highest ID in the misbehaving table was 790. The first time I tried to enter a new record, the autonumber came up with 91, which already belonged to another record, and that's what caused me to put the post on TSG. But I also noted that when I hit Esc, then tried to again enter a new record, the autonumber was higher. I don't know how or why, but when I tried that again this morning, the number had advanced to 533. I then wondered what would happen if I just kept typing one letter in the first field of the new record row (to get the table to assign an ID), then hit Esc twice and kept repeating that, what would the autonumber do? Just like yesterday, it advanced by one each time. So for about 5 minutes, I just kept hitting a letter key followed by two Esc hits until the number reached one more than the highest ID. At that point, I entered a complete new record. . . and the table accepted it! So the table now works and associated queries, reports, forms also appear to work perfectly.

    Although I've used Access for 15+ years, I have never encountered this bizarre behavior. And like so many other Access glitches, I haven't the foggiest what caused it. It was fortunate that I wasn't dealing with a table containing 100,000 records with an autonumber ID stuck at 10. Then I would definitely have had to do the copy-over process.

    Thanks for the advice, which I printed out in case I run into the problem again.
     
  4. PrivacySoftware

    PrivacySoftware

    Joined:
    Jan 14, 2011
    Messages:
    4
    Thanks, I was having similar issue with my db and this corrected it.
     
As Seen On
As Seen On...

Welcome to Tech Support Guy!

Are you looking for the solution to your computer problem? Join our site today to ask your question. This site is completely free -- paid for by advertisers and donations.

If you're not already familiar with forums, watch our Welcome Guide to get started.

Join over 733,556 other people just like you!

Thread Status:
Not open for further replies.

Short URL to this thread: https://techguy.org/974878

  1. This site uses cookies to help personalise content, tailor your experience and to keep you logged in if you register.
    By continuing to use this site, you are consenting to our use of cookies.
    Dismiss Notice