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>

 

[chris]

Hello Jeff,

I tried the procedure you suggested but still no luck. More testing
forces me to this conclusion: as soon as I index a memo field in this
table (or in a copy + append version of it), corruption shows up. This
happens even if I first take out all memo fields and create a new one:
when I put an index on it, things go wrong.
On one hand I guess I can live without this index but on the other
hand I'm afraid to end up with a completely corrupt database. Do you
have more suggestions?

tia,
Chris

 

[Jeff Boyce]

Chris

Create a new empty database. Import objects from the questionable one
(except for the "memo" table).

Go back to the old database and create a new table, without the memo field,
and append all memo-less rows to it.

Go back to the new database and import that memo-less table.

Recompile.

Repair and compact.

If you first save a backup, you can even try adding a memo field (with
nothing in it), and adding an index.

I still seem to recall that indexes should not be used on memo fields --
perhaps a search on Google.com would give you more info on this...

 

[chris]

Jeff

I would like to try this method but I was wondering if there is a way
to transfer relationships (there must be about 100 in this db) from
one mdb to another.

 

[Jeff Boyce]

Chris

During the 'import objects', the bottom of that window has Options or
something or other. One of the choices is Relationships, if I recall
correctly. There are other choices you might need to make, too.