"JamesJ" wrote in
:

This is only to segregate the memo field from the rest of the
record? Even now I find that when the memo field gets corrupted I
can go to the table and delete the text from the memo field and
the the record is ok. I just need then to put the data back into
the memo field.

But it's very often the case that you *can't* get the data out of
the rest of the record, and that's why segregating memo fields in
separate tables is helpful.

If that's the case, even replacing the record wouldn't be much
more work than replacing the data in the memo field.

In my experience, that has *not* been the case -- the whole record
was lost. And even when the other data was available, you still have
to create a new record to hold the recovered data, since the corrupt
pointer cannot be removed from the record. If the primary key of the
deleted record matters, you have to append the data, retrieve the
new PK value and then update all child records of the old record to
point to the new record, and only then can you delete that record.

So it's a pain if you have memo fields in tables that participate as
parent records in parent/child relationships between tables. If the
table with memos in it is only a child record, and its PK is
immaterial, then you're right, it's no big deal. But if not, it's
quite a bit of work, seems to me.

It seems that the corruption of the memo field occurs when there
is a large amount of data in it.

I don't think that's true. It can happen with any amount of data.
What matters is the editing state when the event that interrupts the
edit occurs.

Most of this data comes from my dvd library and the dvds that are
tv shows with many show titles and descriptions and I'm cramming
all the information about the dvd into the memo field.
So a text field wont get it. 255 characters doesn't go very far.

I would never argue for avoiding memo fields entirely for data that
exceeds 255 characters.

Also I know I believed I asked this in this thread but before I
make any rash
changes to my db I want to find out if anything can be done to
actually minimize the chance of corruption in memo fields.

1) It appears that a large amount of data in memo fields seems to
promote corruption.
Are my observations true?

Not in my experience.

2) If I use only plain text as apposed to rich text, will that
minimize the chance for corruption?

I don't believe so. I have never seen any evidence that the content
of the memo field has anything to do with the corruption of the memo
pointer.

One less drastic step that can be taken to minimize the possibility
of memo field corruption is to edit the memos what I call
"semi-bound". That is, on your forms, remove the control source of
the text box that is bound to your memo field, and then in the
form's OnCurrent event, load the data into the unbound text field
from the form's underlying recordsource:

Me!txtMemo = Me!Memo

Then in the AfterUpdate of this unbound control, write it back:

Me!Memo = Me!txtMemo
Me.Dirty = False

It's important to save the record immediately after writing to the
memo field or you've gained nothing by going with an unbound
control.

But the truly reliable method is to move the records to a separate
table.

On the other hand, the above requires no restructuring of the data
and will minimize the time at which your memo fields are vulnerable.

All that said, if you're having frequent corruptions, you've got
something wrong with your computer or network and rather than
spending all this time on workarounds, you should fix the underlying
problem that's leading to the memo-pointer corruption, particularly
given that other kinds of data can also get corrupted and those
aren't necessarily as easily worked around.

--
David W. Fenton http://www.dfenton.com/
usenet at dfenton dot com http://www.dfenton.com/DFA/