Updating Memo fields and ADO error

[rainbowrabbit]

Hi,

Access 2000 (9 SP3)
Windows 2000 client

I have a memo field in a table that contains descriptive text around requests for work being logged.

This memo field gets updated when a user types in more descriptive text on the form. The descriptive text is appended to the orginal text with a timestamp and the users name which gives a history of the request. This means that the original memo text gets larger the more updates that are made.

One record in the database has this memo field exceededing the 2000 character mark and now I get an error when attemtpting to update the record.

-2147217887 80040e21
Could not update; currently locked by another session on this machine

I am using an unbound form and ADO to save the records into the table. Below is the line that is fails on;

rst.Fields("request_desc").Value = rst.Fields("request_desc").Value & vbCrLf & vbCrLf & "********* Request Updated by " & strFullName & " - " & Now() & " ********** " & vbCrLf & Me.memAddDesc

I have also attempted to use Docmd but get the same problem with a 3188 error message.

Is there a way to keep updating a memo field?

Thanks

 

[Remou]

Microsoft specifies:
Number of characters in a Memo field 65,535
So the problem may be elsewhere. Have you tried a Compact and Repair?

As an aside, have you considered a separate table for memos where each comment could have a new record?

 

[rainbowrabbit]

Hi Remou,

I have compacted and repaired to no avail. I have cut and paste the contents into word and it has a character count with spaces of 2200.

I am beginning to think your suggestion of a separate table maybe the way I should go....

 

[Remou]

Hi
I wonder if this is relevant to you?
FIX: ADO: Unable To Update Memo Field > 64K In Access Database

http://support.microsoft.com/kb/q198532/

Though from a database point of view, a separate table is probably a good idea as you are likely to run out of space on the memo field, not to mention the inconvenience of locating a specific date.