MS Access Error updating memo field with long text

Question

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.

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.

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.

(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.)

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:

-- 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.

-- 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.

-- Other operations on the rows that, unnoticed by you, happen to contain the #Error values fail.

Regardless, the root problem is whatever causes the #Error values to get placed into the Memo fields in the first place.

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.

So why does the error occur when Updating using >2000 characters?

gwideman gwideman · Accepted Answer · 2017-03-22T07:15:55

The key factor that provokes this error is the Memo field having an index. Apparently, although the Memo type field can hold a bazillion characters, the index can't deal with more than about 2000.

Knowing that this is the precipitating factor, probably a number of workarounds come to mind. First, you can obviously just disable the index. This solution is easy to verify in a dummy database: Create two tables containing Memo fields, one with an index and the other without. Run update queries that put >2000 characters into each Memo and note the results.

But perhaps you think you need the index? Your use case might be satisfied if you create a second field that will contain an initial substring of the main Memo (shorter than 2000 characters), and index that instead. This could be used for sorting purposes for example. In most cases, where a memo contains narrative information, it's unlikely that the memo data values differ only after 2000 characters. Or perhaps you can devise a hash function and make a separate column of that.

What if you have a database that already contains these #Error values? Some advice floating around on the web, especially in relation to downstream problems like failure of Compact and Repair, suggests that your database is corrupt and should be abandoned. I'm not so sure. If you can delete the #Error-afflicted rows, then delete the index, and then recreate the deleted rows, you may be back in business. Compact and Repair should run properly at that point, giving some confidence that you fixed the offending part. (Make backups along the way, obviously.)

MS Access Error updating memo field with long text

2 Answers