Write Conflict Error Editing Memo Field

[normturgeon]

I’m experiencing the following Write Conflict Error when editing a “Memo” field on an MS Access subform:

“This record has been changed by another user since you started editing it. If you save the record you will overwrite the changes the other user has made. … “
Options: Copy to Clipboard or Drop Changes

Environment: Access 2003, SQL Server 2005, Windows 7 Pro

I have a main form with one subform.

The main form is not bound to any table or query. All of its controls are populated by its On Open Event handler. One of these controls (a textbox) contains the subform’s Linked Master Field.

The subform is bound to an SQL Select statement whose underlying table is an SQL Server table linked via ODBC. The table contains some fields with a character datatype.that exceeds 255 characters (e.g., ntext, nvarchar(256), etc). When the table design is viewed in Access these fields appear as Memo fields.

Whenever I edit any one of these fields on the subform, and then move to a different record, I get the Write Conflict error; and there are no other users.

Changing the datatype to nvarchar(255) solves the problem, but that is too limiting for my client.

Is there a solution to this problem?

[normturgeon]

I followed other threads and found a solution offered by Danny J. Lesandrini in November 2005, viz:

“Add a timestamp field to the table in SQL Server, and everything will be fine. The problem is that, because of the memo field, the client can't determine with certainty that the record hasn't been changed by someone else. Just add the timestamp and see what happens.”

That worked for me.

Thanks Danny

[NeoPa]

Good for you Danny. Thanks for posting. I'll take the unusual step of flagging your response as Best Answer, as it does provide a good solution to the problem and it's expressed clearly :-)