Memo fields in MS Access are similar to the ‘text’ fields in that they allow you to store characters. However, the size of a text field is limited to just 255 characters while a memo field allows as many as 64,000 characters. The actual contents of the memo are stored in separate data pages and the field itself stores pointer to these pages. Access allows you to limit the size of a text field via the Field Size property, but the same cannot be done to a memo field. Hence, memo fields are used when your records are larger than 255 characters and can be stored without formatting.
A prominent issue that is faced typically with memo fields is that they are sometimes cut off when processed by Access. This means when you perform aggregation, de-duplication, or formatting, Access may end up truncating the memo during this processing. The reason for this behavior is self-explanatory. When processing involves string operations, performance can be much slower than expected. A single operation may take several days as Access would require comparing tens of hundreds of characters in the memo with thousands of characters of other memo fields. In addition, comparisons may involve other things. The JET 4 MDB and ACCDB files are UNICODE-formatted. Processing these files takes more time for there are more disk reads. For these reasons, Access only processes the first 255 characters when handling memo fields.
Memo fields are more vulnerable to corruption than any other object in your database. If the pointer to the actual memo data is written incorrectly in the field, you may see garbage when you access this field. If corruption occurs to the memo field, it can affect the entire database. Even after fixing the damage, you may lose the actual data of the memo field. When multiple users in a network try to append data to the end of a memo field, the field may show up a ‘#deleted’ symbol indicating data corruption. If this field is accessed by a user, it would display the following error on the screen:
‘The Microsoft Jet database engine stopped the process because you and another user are attempting to change the same data at the same time.’
If you attempt to <link removed> containing the corrupt memo field, you may receive a false report that the repair was successful. If you attempt to compact the database, you may receive another error:
‘Record(s) can't be read; no read permission on 'filename'.’
You can remove corruption by deleting the row explicitly, and then compacting the database. You can recreate the memo field and restore the contents of the field by linking to an older backup.
A good approach to prevent memo field corruption is to either avoid using memo fields or store them in separate tables, and then create a one-to-one relationship to the first table. You can also choose to use OLE objects to store large amounts of text instead of using memo fields.
| 
