Ms-Access MDB: Split Memo field into Several Text fields. (To prevent data corruption)

Question

I use an Access database used just as back end.
I use some Memo Fields.

I have learnt that Memo Fields are subject to cause database corruption because they are stored in a separate data page; the record only holds a pointer to the data page where the actual data is stored.

Most of the time I just need from 100 to 1000 chars or so, so I had an idea...
My "brilliant" (or not) idea was to split the memo into 4 or 5 Text Field's (they can hold 255 chars each).

Anyone has done this before?
Any known issues?
Would this approach be less prone to data corruption?

Thanks,
Jag

P.S.
1. I have had corruption issues before.
2. I know the best thing would be to migrate to another back end, but it isn't possible.

Answer 1

The better workaround is to unbundle the memo data page from its attached record. The way to do that is to put the memo field(s) in a separate table. You can either do it with a 1:1 table (with multiple memos in the side table), or, better, as a 1:N table with a memo type field. The latter way is the only way to completely avoid the memo pointer problem, though, as in the former solution, the corruption of any memo pointer in the memo table causes you to lose all of them.

Also, you should consider why your database is corrupting memo pointers. Aside from the suggestion above, you should probably consider editing your memos unbound, though since it doesn't seem you are using an Access front end to your Jet MDB that is probably not an issue. I see memo corruption every now and again, but not very often. If you see it often, it suggests to me that your application is badly designed or your operating environment is markedly substandard.

Answer 2

It sounds like you want to take an existing memo field and chop-up before putting it into the database and then gluing it back together when you need to display it.

I think you are going to be in for a world of pain if you try that. I'd try harder to change the backend if that was your only option :)

I'll build on DWF's answer by suggesting doing something like you were suggesting. Make a 1:N table (I'll call it UserNotes) with a text field (vs the memos that you seem to be having troubles with).

Then create a subform with UserNotes as its record source, and replace the memo field with the UserNotes subform. This lets the user decide where line/paragraph breaks are within the context of the 255 character text field. (for conversion you'll still have to chop the memos up, but that's just a one time operation)