Bookmark Error during Table sort

[Mike S.]

I'm working with a database (Access 2000 Ver 9.0.2720) that has over 40
fields, 4050+ records and 96 queries referencing either the table or master
queries. When try to sort by First and Last Names, I get a popup window that
says "Not a valid bookmark.", a "Help" button", and "OK". (Help hasn't
helped as the VB info was left off of my copy.) In addition, the top record
has "#Error" in each text/number field, and a grayed out box in each checkbox.

Each attempt to interact with the errored record results in 1 or more
iterations of the above error window. (I've managed to work around this by
not sorting by both columns, but I've got many "Smiths" in both columns.
Also, the window repops only when the mouse tracks across the record or when
I select a column without changing the sort order.) And all attempts to
delete the offending record lock up Access.

I've checked the Knowledge Base(KB), but, as KB suggests Visual Basic(VB)
issues and solutions, I'm left puzzled. I don't know any VB, nor did I use
it when creating the database. Oh, I've been using this database since 1998,
and importing it as I got newer versions of Access.

More: The error only appears when sorting Last Name & First Name, be it
ascending (the error row appears at the top) or descending (the error appears
at the bottom). Other multi-column sorts do not produce the error, unless
they are subsequent to the Last+First sort. And, the one time I saved the
table format after an errored sort, I had to create a new key (index #,
Autonumber) to reopen it.

Also, I don't want to have to delete those columns as editting 4000 records
is painful.

Any input will be appreciated. Thanks.

 

[Allen Browne]

Hi Mike

The symptoms you describe are symptomatic of a corruption, either in the
data or in an index of the table.

Firstly make a backup copy of the database while no-one has it open, and
without overwriting any other backups. Then if your attempts to recover it
make it worse, you can revert to this state again.

Next, download and apply Service pack 3 for Office 2000, and (most
importantly) service pack 8 for JET 4. Both are available from:
http://support.microsoft.com/gp/sp
Do not skip this step: you have a very early version of Access 2000 that was
literally riddled with bugs, and you need to patches. (Depending on your
version of Windows, you may find that you already have the JET update
installed, but is is important.)

That done, open the database, and choose:
Tools | Database Utilities | Compact and Repair
If you are really lucky, the database is now repaired. (This works for a
corrupted index, but not for corrupted data.)

If the problem is still there, follow the steps for symptom 3 in this
article:
Recovering from Corruption
at:
http://allenbrowne.com/ser-47.html
This will give you a rebuilt table without the problem records. The crucial
aspect is to NOT refer to the corrupted data row during this process.