Risks Involved When Using Memo Fields in MS Access Databases

[markwillium]

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.

[Taaner]

Thanks for this information Mark. I was always sceptic towards using memo-fields in a database, this article confirms to avoid the use especially in an Access database.

Should the article not been moved to "insights" since it is not a question?

[zmbd]

The problem here that I see is that the post appears to be an aggregate of information from sources like

• Allene Browne: Truncation of Memo fields
• Allene Browne: Recovering from corruption
• Ben Beitler: Problems With Memo Fields When Running A Query In Access 2010

that has been barely re-worded and as such, without citing the sources borderlines in plagiarism.

Also, it appears that some of the issues with Memo field corruption have been fixed: memo-fields-multiuser-database (also of note is that markwillium's post even echos information found within this thread).

I'm sure that the site admins have started a review of this post.

[TheSmileyCoder]

I have used memo fields in my databases since I started developing in Access, and I have to say I don't really agree with the contents of your post.

Yes, Access will in some cases only work on the first 255 characters of a memo field, but to be honest I don't see this as a bug, I see it as a feature, that admittedly could do with some more information/warning about how to use it. It makes perfect sense actually to restrict the query engine to not work on the entire field, especially since memo fields can in theory contain over a hundred thousand characters (although max limit on GUI is 64.000, you can add more through VBA). Know your tool.

Perhaps Memo fields are more susceptible then other types of fields to corruption. I haven't seen any empirical evidence IN MY OWN work as of yet. I am also more likely to be in a accident if I use my car rather then walk, all the same I still use my car, and I still use my memo fields. That said, you should of course not use a memo field to store text that you know will never be longer then 255 characters. Don't use a memo field for a first name. This is no different then I shouldn't use a text field to store an integer (even though possible), and I shouldn't store dates in a text field either. Know your fields, and use them APPROPRIATELY.

@Taneer
I believe the main purpose of this post was to sneak in a link (commercial spam) to a site. The link was removed by Rabbit, and I will leave the post as is. People are entitled to their opinions on Memo fields, but I don't really believe this counts as an insight.

In general if you keep your frontend seperated from your backend, and avoid doing design changes on a live database the risks of corruption are small. That said, always remember that access (unless a SQL server is used as backend) is a file based database, and that several read/writes are performed to the backend file all the time in a multi-user environment.
Make sure you have a backup strategy. Make sure that your local IT department takes regular backups, of the backend file. In 5 years of developing I have twice had the need to retrieve a backup file. One was caused by yours truly running a bad update query (wooops) and the other was caused by a complete power failure to the network drive.