Solved: Major problem with Access dB

Status
This thread has been Locked and is not open to further replies. Please start a New Thread if you're having a similar issue. View our Welcome Guide to learn how to use this site.

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!
 

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.
 

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.
 
Status
This thread has been Locked and is not open to further replies. Please start a New Thread if you're having a similar issue. View our Welcome Guide to learn how to use this site.

Users Who Are Viewing This Thread (Users: 0, Guests: 1)

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 807,865 other people just like you!

Latest posts

Staff online

Top