How to repair index corruption

[chris]

I have a A2K database with a table which seems to have a corrupt index
on a memo field. The corruption shows as garbage characters when the
memo field is used in some queries.
If I delete the index on the memo field, everything is ok. Since I
want that index, I did what I found in another thread, i.e. import the
table into another db and then reimport it (the thread stated that
indexes are then recreated). However the problem remains.
What I also did was this: I removed the index, then compacted/repaired
the database. But as soon as I recreate the index on the memo field,
I'm getting the same problem.

So in other words I have found the exact souorce of the corruption but
I can't seem to get rid of it without losing the index functionality.
Does anyone have a clue?

Tia,
Chris

 

[Jeff Boyce]

Chris

I seem to recall somewhere that memo fields are not amenable to indexing,
but that could be (my) faulty memory.

What is it that you hope to accomplish by using an index on your memo field?

 

[chris]

Thanks for replying, Jeff. I hope your memory is not faulty ;-) but
apparently the index property can be set for memo fields. In fact this
memo field contains some kind of appreciation of students. Its
contents can vary from a simple code (like A, B, C) to up to a full
page of text. Quite some reports select on the codes (e.g. all the
A's), so I thought I'd better index the field, hence the problem.

 

[Jeff Boyce]

Chris

It sounds like you have more than one "fact" to store. Could you add a
simple "Grade" field? You could index that quite easily.

Good luck

Jeff Boyce
<Access MVP>

 

[chris]

Alas, due to db design constraints I can not modify the structure of
this table.
Is there a way to know whether it is the index itself that is corrupt
or the data of 1 or more records?

 

[Jeff Boyce]

Chris

I suspect it's more likely to be the latter.

Have you saved a backup copy, run the Repair/Compact, created a new table,
and appended rows into the new table from the old? You could try appending
all but the memo field to see if you have problems, then do an update query
to copy over the memo field.

Good luck

Jeff Boyce
<Access MVP>