none
Experience with memo fields (Access 2007) RRS feed

  • Question

  • I'm reluctant to use memo fields because I've already read that they are the origin of corruption problems of the accdb files.
    Can you share your experience concerning this issue.
    What are the problems that i can face ?
    What are the techniques i should apply ?
    What must i  avoid ?

    Best regards

    João


    Joao Simplicio Rodrigues

    Thursday, April 28, 2016 7:18 PM

Answers

  • >>>I'm reluctant to use memo fields because I've already read that they are the origin of corruption problems of the accdb files.

    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.

    In addition you could refer to below helpful links:

    Truncation of Memo fields

    Recovering from corruption

    Problems With Memo Fields When Running A Query In Access 2010

    Disclaimer: This response contains a reference to a third party World Wide Web site. Microsoft is providing this information as a convenience to you. Microsoft does not control these sites and has not tested any software or information found on these sites; therefore, Microsoft cannot make any representations regarding the quality, safety, or suitability of any software or information found there. There are inherent dangers in the use of any software found on the Internet, and Microsoft cautions you to make sure that you completely understand the risk before retrieving any software from the Internet.
    • Proposed as answer by David_JunFeng Wednesday, May 4, 2016 8:38 AM
    • Marked as answer by David_JunFeng Thursday, May 5, 2016 9:56 AM
    Friday, April 29, 2016 2:03 AM

All replies

  • Hi. My experience with Memo fields does not include any problems I can recall. Where have you heard they cause corruptions? Just curious...
    Thursday, April 28, 2016 7:36 PM
  • The only thing I have run across is when you use a memo field in a query that has Totals enabled, you can get what looks like chinese or cryptic characters in the field. That field is unreadable. I use version 2007. It causes a temporary field "corruption" but the data itself is fine. I have never seen it cause actual permanent data corruption. Of course, you can never use the field in any reference in an expression because the field has the capability of holding 16K characters.
    Thursday, April 28, 2016 11:26 PM
  • >>>I'm reluctant to use memo fields because I've already read that they are the origin of corruption problems of the accdb files.

    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.

    In addition you could refer to below helpful links:

    Truncation of Memo fields

    Recovering from corruption

    Problems With Memo Fields When Running A Query In Access 2010

    Disclaimer: This response contains a reference to a third party World Wide Web site. Microsoft is providing this information as a convenience to you. Microsoft does not control these sites and has not tested any software or information found on these sites; therefore, Microsoft cannot make any representations regarding the quality, safety, or suitability of any software or information found there. There are inherent dangers in the use of any software found on the Internet, and Microsoft cautions you to make sure that you completely understand the risk before retrieving any software from the Internet.
    • Proposed as answer by David_JunFeng Wednesday, May 4, 2016 8:38 AM
    • Marked as answer by David_JunFeng Thursday, May 5, 2016 9:56 AM
    Friday, April 29, 2016 2:03 AM
  • just truncating per the DJF post.... and that is simply a matter of understanding that one can't deal with a 4k size in every situation;  with an awareness of the truncating then otherwise memo field types are fine....never had a problem....
    Friday, April 29, 2016 2:31 PM