[AccessD] Memo field corruption

[Daniel Waters]

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. 




-- 
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com

-- 
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com