Error on Save a changed row if the change was in a field of type 'Memo'

Question

We have a Access-db with linked (odbc) tables on an sql-server. Some times there is a rare problem with one column of a particular row: no more changes in the field of type memo are possible. Changes in other columns of this particular rows are saved as normal. The error-msg goes something like: annother application has changed the row and therefor the update has been canceled.

what could be the reason, what can be done to prevent this behaviour?

Peace Ice

Update:

The mdb is definitively not corrupt. There are only the odbc-connections inside, we use it in read-only mode. The issue must be between the jet-engine and the odbc-driver respective the sql-server, that is what i think.

Peace

Ice

Answer 1

If your data were stored in a Jet/ACE back end, I'd say you likely have a corrupted memo pointer. Since your data is in SQL Server and accessed via ODBC, that can't be the answer. But since others might come to this discussion who are encountering the problem with a Jet/ACE back end, the below might be helpful:

In Jet/ACE tables, memo data is not stored inline with the the other fields. Instead, the data is stored in separate data pages elsewhere and all that is stored inline is a pointer to the first of the external data pages. That pointer is susceptible to corruption and is a frequent cause of lost data.

Some links from Tony Toews (the best source for this):

General Reference Source on Jet/ACE Corruption

Corruption Symptoms

In that second one, search for 3197, which is likely the error number of the problem you're encountering. There's a link there that explains how to troubleshoot it.

After you've fixed it, you should consider restructuring your data tables to minimize the risk of memo field corruption.

1. I know you are not using Access, but for Access forms, one solution is to avoid bound memo fields, and instead edit them in unbound textboxes. In the Access form's OnCurrent event, you'd copy the memo data from the form's fields collection into the unbound textbox for it, and in the textbox's AfterUpdate event, save it back to the form's underlying recordset.

2. for all applications, Access or not, putting the memos in a separate table segregates the memo field pointer from the rest of the data. If you have one memo, it can be a 1:1 table, and if you have multiple memos, you would have a 1:N and the memo table would have to have a field to indicate the memo type. With that structure, the main record does not have to be deleted and recreated to fix a corrupted pointer -- all you have to do is delete the corrupted record in the memo table.