corrupted record in table, won't delete or edit

[Hph]

I am dealing with an error that has ocurred in the past but has me
baffled as to why it occurs. With no warning one random record in a
table will appear to convert from "english" to "japanese?" The
record will not delete, the record will not allow edits. The table
will not then let me replace the bad record with a new one because it
causes a "duplicate" in the SSN field. I have gotten around this in
the past by copying the structure of the table and then copying and
pasting the data into the new table leaving the bad record behind.
However, in the past the error has ocurred in tables which were linked
to my main table which has an auto-number field. The auto-number
field is a "number" field in the linked tables. I can change the auto-
number to a "number" and copy it but cannot go back to an "auto-
number" field for future records. If I copy the information into a
new auto-number table all the records get assigned a new number. I
would then have to figure out who is who and renumber all the
information in all the other tables. I really need someone to talk me
through this or who can help me resolve it. The corrupted table is
the main table in my database which keeps my Operation Department
running. We test and repair equipment in nuclear power plants. I
need help. Please.

 

[Ken Snell \(MVP\)]

You can "set" the value of an autonumber field to the required values in
these situations, but you must do it via an Append query, not by a
copy/paste approach.

Here's what you do. Create a new, temporary table to hold the uncorrupted
records during the process. Use a Number field (set to Long Integer) for the
field that will hold the autonumber field's data. Copy the uncorrupted
records to this table.

Now create a new table that is a duplicate of the corrupted table. It should
contain an autonumber field just like the original table. Do not add any
data to this table yet.

Now create an append query:

INSERT INTO NewTable
( NameOfField1, NameOfField2, NameOfField3, ... )
SELECT T.NameOfField1, T.NameOfField2, T.NameOfField3, ....
FROM TempTable AS T;

(I've put ellipses into the SQL statement above to show that you need to put
all the fields in the two tables into the SQL statement.)


Now, run the append query to copy the uncorrupted values from the temporary
table into the new table. The autonumber field will then accept the values
from the uncorrupted records.

Then, very important -- go to Tools | Options | General tab and deselect
Name AutoCorrect option, else ACCESS will try to help you by remembering the
previous table's names in all your queries and messing you up with the next
steps.

You now can delete the corrupted table and change the name of the new table
to the original table's name (you will need to delete Relationships for this
to be done, and then reestablish them after you're done).