[AccessD] Memo field corruption

[Steve Schapel]

I had a problem just this week with a Long Text (Memo) field.  I tried 
to apply a function to this field in a query.  This resulted in the data 
being truncated to the first 255 characters.  The solution was to apply 
the function in an Update or Append query and write the resultant data 
to a table, and then use it from there.

Regards
Steve


------ Original Message ------
From: "Daniel Waters" <df.waters at outlook.com>
To: "Access Developers discussion and problem solving" 
<accessd at databaseadvisors.com>
Sent: 31/01/2019 9:19:47 AM
Subject: Re: [AccessD] Memo field corruption

>I did have a heavily used memo field as a place for people to put comments.  This was in a business process module for handling nonconforming material.
>
>To add a comment you opened a separate form and typed in your comment, then pushed save.  From there the comment, name, and date would be inserted into the memo field in the table at the end of current contents of the field.  No editing of existing information was allowed.  I didn't have any concurrency issues.  That worked to handle a memo field that is used repeatedly by different people.
>
>Dan
>
>-----Original Message-----
>From: AccessD [mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Jim Dettman
>Sent: January 30, 2019 6:15
>To: 'Access Developers discussion and problem solving'
>Subject: Re: [AccessD] Memo field corruption
>
>
>  The other issue there is locking.
>
>  Memo data > 30 bytes is stored on Long Value Pages, which are always locked
>at page level.
>
>  So even if you are using record level locking, including a memo field can
>cause a lot of concurrency issues.  That's another main reason for moving it
>out to a separate table and updating the memo fields as a separate process.
>
>Jim.
>
>-----Original Message-----
>From: AccessD [mailto:accessd-bounces at databaseadvisors.com] On Behalf Of
>Susan Harkins
>Sent: Saturday, January 26, 2019 12:18 PM
>To: 'Access Developers discussion and problem solving'
>Subject: Re: [AccessD] Memo field corruption
>
>The main reason was to protect the rest of your data -- if the memo field is
>corrupted, you're dealing with just the memo fields in that table. It won't
>prevent corruption, just make it easier to fix if you do.
>
>Susan H
>
>
>In this page
>https://bytes.com/topic/access/answers/190112-query-memo-field-cuts-off-255-
>characters at the bottom it says this:
>
>The reason for these limitations is that memo content is not actually stored
>in the table. Only a pointer to its location on disk is. This makes features
>like sorting and grouping _very_ inefficient because the query has to use
>the pointer to go "get" the text, evaluate it, and then apply the sorting
>and grouping.
>
>So - if the data is already NOT stored in the table, why create another
>related table for memo fields?
>
>I'd suggest trying to track down the source of this 'rule' before you go
>further with it.  Maybe this was helpful for much older versions of Access
>for some reason?
>
>Good Luck,
>Dan
>
>
>I think a lot of "expert" database developers suggest keeping the memo field
>in a separate table using a 1:1 or 1:n relationship -- just in case.
>
>Susan H.
>
>
>I always include memo fields in the same table.  I haven't heard of what you
>described - have you seen that done somewhere?
>
>Dan
>
>
>When you include a memo field, do you include it in the table or do you
>relate to a second table that stores just the memo field?
>
>Susan H.
>
>
>