[AccessD] Memo field corruption

[Stuart McLachlan]

Only returnng the first 255 characters haas been a problem when working with memo fields ( 
now apparently known as "Long Text", not ""Memo"!) in a query forever :(.


Here's a few possible workrounds.
http://allenbrowne.com/ser-63.html


On 31 Jan 2019 at 8:44, Steve Schapel wrote:

> 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-o
> >ff-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.
> >
> >
> >
> 
> 
> -- 
> AccessD mailing list
> AccessD at databaseadvisors.com
> http://databaseadvisors.com/mailman/listinfo/accessd
> Website: http://www.databaseadvisors.com
>