Long text/MEMO or Text Data Type

Question

Why do we have to avoid the LONG TEXT data type in Ms Access 2010 and use the Text datatype instead, wherever practical?

Answer 1

Daniel's quite right about the corruption issue; I've had Memo fields corrupt databases several times.

But another issue is that Long Text fields have some limitations. They cannot have a Format property (without being truncated to 255 characters); and they cannot be indexed, making sorting and searching much less efficient. On a real-life note, a message over 255 characters invites skimming rather than reading carefully.

If you NEED long text, by all means use it. Back up frequently; use a linked separate backend just for the long text if necessary. You just have to know the limitations and live with them!

Answer 2

Thank you Mr. Vinson for further clarification. I think i am just going to go with short text data type as i see it has less complications as compared to that of Long Text Type.

Answer 3

Thank you very much Mr. Pineault. It does clarify my confusion upto some extent. 👍

Answer 4

One word, corruption.  The belief is that Long Text are more likely to corrupt then other data types.  That said, not everyone holds this belief.  Then again, why take a chance. 

If you are designing your database, you are always best to assign the most appropriate data type anyways to minimize space (and thinking about corruption of course).

Some go to the extent of housing Long Text fields in a separate table for the main data so if corruption occurs it doesn't impact the rest of the main table data.  Some will even house the Long Text in another database altogether for the same reason.

But it all comes down to corruption.