[AccessD] Memo field corruption

James Button jamesbutton at blueyonder.co.uk
Sat Jan 26 11:43:03 CST 2019

Add to the avoiding corruption of masses of data

Allowing for easier changes of associated apps when the memo contains RTF or
other things such as images
Also reduces the time taken for the DBMS to load up the data in the table for a
SELECT statement, or just a scan through the file for backups etc.
If you have 10,000 rows, and each has 1000 bytes of data that's a 10MB table
And maybe 20MB of Temp files 
With a Memo of - say 4000 bytes in each that means any process of the table will
have to read 100MB and possibly use 200MB of RAM for that process
Complete with the flushing of memory to pagefile and the flushing of other data
from the storage cache, and   the RAM allocated by the OS for I/O buffering.

So - separate out the memo and blobs and you avoid massive processing overheads
as well as backup requirements if the entries are held in separate files
And that is without even considering the increasing size of images and
commentary that may be in memo fields
Cut and paste of text from reports and filled-in forms - as in as well as
describing the actual incident, detail your understanding of the cause, and the
effect of the incident on yourself.


-----Original Message-----
From: AccessD <accessd-bounces at databaseadvisors.com> On Behalf Of Susan Harkins
Sent: Saturday, January 26, 2019 5:18 PM
To: 'Access Developers discussion and problem solving'
<accessd at databaseadvisors.com>
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
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,

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?  


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
Website: http://www.databaseadvisors.com

More information about the AccessD mailing list