title: MS Access Error updating memo field with long text
body: <p>Searching this problem returns quite a few search hits, but many off-track answers, so I'm posting a concise description here, and answer below.</p>
  
  <p>The problem afflicts Microsoft Access 2010, and some versions before. Access 2013 renames Memo type to Long Text. I don't know if it has the same problem.</p>
  
  <p>The root problem is associated with running an UPDATE query on a table with a memo field, in certain particular circumstances. This might be an UPDATE query composed in the visual query window, or some VBA running SQL via DAO or ADO or similar.  Or it could arise while updating via a form.</p>
  
  <p>(The current post is concerned with this occurrence just within an Access database, though elsewhere you will find discussion of similar-sounding issues when Access is connected to an external database server.)</p>
  
  <p>Instead of generating an immediate and obvious error alert, Access (or perhaps Jet) places the value #Error (which is not just the string "#Error"!) into the Memo field. This might easily go unnoticed until some later time, resulting in visible errors such as:</p>
  
  <p>-- You use Compact and Repair. That seems to complete, but Access quietly adds a MSysCompactError table with a couple of rows. One error -1611 complains that Access was stopped and couldn't complete the operation. A second, more-specific-seeming error complains that it can't find field "Description". That appears to be an internal error that has no relevance.</p>
  
  <p>-- You try to copy the table to another database. Access gives an error complaining that another user is using the table or has updated the table, and won't complete the operation.</p>
  
  <p>-- Other operations on the rows that, unnoticed by you, happen to contain the #Error values fail.</p>
  
  <p>Regardless, the root problem is whatever causes the #Error values to get placed into the Memo fields in the first place. </p>
  
  <p>Many posters have noted that it occurs if the UPDATE attempts to put strings longer than about 2000 characters into the Memo field. That's a surprise, as Memo fields should be able to hold 1 gig characters or more depending on version, even if it only allows 65k through the UI.</p>
  
  <p>So why does the error occur when Updating using >2000 characters?</p>