data corruption - the search key was not found in any record

[DC Gringo]

While on a text type field, when try to input data directly to a table (in a
record that is already created), I get the error:

"the search key was not found in any record"

Upon moving off the record I get a little square charater preceding an "@"
character. I've read up a bit and found two suggestions. The first is to
compact and repair. Although this seems to work and allows me to input data
into my other record, I get a new record at the top of my table, preceding
my first record, that seems to be written in Chinese, with a primary key of
"-16742401". I seem to be able to just delete this record and move on, but
I'm curious as to whether it has any known meaning.

It seems I have three options:

1) Doing the compact/repair and deleting this weird record
2) Deleting my bad record and recreating it and reassigning primary keys to
related records
3) Creating a new database, importing all objects, and leaving this record
out (with a SELECT FROM WHERE) of the import

Thoughts on the weird record and/or any of my solutions would be greatly
appreciated...

 

[Allen Browne]

To rebuild your database:
1. Make a copy of the file while it is not in use.

2. Open the relationship window (Tools | Relationships), and delete any
relationships that the problem table is involved in.

3. Open the table in design view. Open the Indexes box (View menu), and
delete everything in this box. This includes deleting the primary key index
(though you do not delete the actual field itself). Save. Close the table.

4. Compact the database: Tools | Database Utilities | Compact. Close the
database.

5. Create a new (blank) database: File | New.

6. If this is Access 2000 or later, uncheck the Name AutoCorrect boxes under
Tools | Options | General.

7. Import everything into this new database: File | Get External | Import.

8. Test that your table is working okay in the new database.

9. Open the table in design view, and create the indexes that you deleted in
step 3. Save. Close the table.

10. Open the Relationships window, and create the relationships this table
was involved in.

The whole process should take less than half an hour, and get you back on
the road again.

 

[DC Gringo]

Allen, thank you, this is good advice...

I have a couple of questions: what is this record in that has
Chinese-looking characters in it I get in the first row after the compact?
And secondly, it seems that my database (and the bad record) works just fine
after the compact. Do I really need to do the import and recreation of
indexes? FYI, this database sadly does not have relationships.

_____
DC G

 

[Allen Browne]

The bad characters do indicate a corruption.

If the compact'n'repair successfully removed the corruption, there is no
need to go through the process of rebuilding the database.

 

[DC Gringo]

OK, thanks...

And then what do I do with the Chinese-looking record it generates as the
table's first record?

-______-
DC G

 

[Tony Toews]

If the compact'n'repair successfully removed the corruption, there is no
need to go through the process of rebuilding the database.

FWIW Peter Miller has stated that you should always import all
objects in a repaired MDB into a new MDB as there may be minor
corruption still left which can get worse over time.

Tony
--
Tony Toews, Microsoft Access MVP
Please respond only in the newsgroups so that others can
read the entire thread of messages.
Microsoft Access Links, Hints, Tips & Accounting Systems at
http://www.granite.ab.ca/accsmstr.htm

 

[Tony Toews]

And then what do I do with the Chinese-looking record it generates as the
table's first record?

Delete it.

But does it come back whenever you do a compact even when you've
deleted it?

You could also try importing everything into a new MDB.

Tony
--
Tony Toews, Microsoft Access MVP
Please respond only in the newsgroups so that others can
read the entire thread of messages.
Microsoft Access Links, Hints, Tips & Accounting Systems at
http://www.granite.ab.ca/accsmstr.htm