Limited Long text (1 Viewer)

[ilanray]

Hello
I have a long text field. I created a form with that field and update button which has an update query. When i type more than 2037 letters it gives me an error message like "update didn't succeed because the item is locked," if i click debug it show me the update sentence. If write less then 2037 letters everything works well
Any ideas?

 

[Pat Hartman]

Why are you using an update query instead of a bound form?

 

[ilanray]

I am using an update query

 

[The_Doc_Man]

If you are using an update query behind the scenes AND have a bound form open to the same record, you are running into a self-bind. The open form locks the record for (potentially deferred) update. If you then try a separate update query, you are trying to write over the same record you currently have open. The behind-the-scenes update and the form's update are two different formal channels - two recordsets - to the same place.

 

[Josef P.]

Since it works with up to 2037 characters, could this perhaps be related to page lock?
2 * 2038 = 4076 ... + some overhead = 4096

@ilanray:
Can you provide a sample file that shows the problem?

 

[MajP]

Why are you using an update query instead of a bound form?

 

[The_Doc_Man]

If the field is really Long Text then it is not stored with the rest of the record anyway, so an internal buffer limit isn't likely. Otherwise ALL Long Text fields would blow up, and we have seen that they do not. Long Text can only go up to 65535 bytes through the Access interface (GUI interface) but the field size is less than that, so buffer length isn't the problem. Number of characters in a text box is also 65535 so that isn't the source of the problem. (Though that IS why the limit is 65535 through the GUI.) The limit on size of a query is 65535, so maybe I could see having a limit of slightly less than 65535 for the text of the update query such as "UPDATE table SET longtext='....lots of long text...' WHERE ..." But 2037 is again less than the limit for a query.

Are you using any functions during the manipulation of the Long Text field? Because some issues have been reported with selected functions where you passed very long strings through some kind of VBA function.

 

[ilanray]

No the update query i am using looks like docmd.runsql "update tasks set description='" & me.description & "' where id =" & me.id

 

[Pat Hartman]

It is hard to help when you don't answer specific questions which have been asked multiple times.

 

[561414]

2037 is a random number for what you are describing, OP
If you can not provide a sample database, post some screenshots of the involved procedures and your interface to be able to figure out the rest of the information.

 

[The_Doc_Man]

Counting out the bytes in your query,

docmd.runsql "update tasks set description='" & me.description & "' where id =" & me.id

Your entire command including the docmd and the constant text portions of the query (after substitution) is the length of the ID field + 2092. If the ID is no more than 6 characters, that is 2098. Taking out the DoCmd.RunSQL and the space, it drops to 2085. I don't see where 2037 makes any sense as the limit. Your equivalent line, after substitution and counting the constant text parts, is already longer than the string you were looking to add. Either you have a strange type of corruption or there is something you haven't told us yet. And we don't understand enough about what you are trying to do to be able to make a more educated guess. I think I would investigate this by attempting a Compact&Repair on a copy of the file that is acting up to see if something gets repaired.

 

[Josef P.]

Attached is an example file to show the problem.

Note: please do not discuss the usefulness of changing a data field from the current record of a form via an update statement. I can't answer this question.

 

[ebs17]

Summary:
Either use less than 2037 characters or only use exactly one access to the memo field.

Access to a memo field here also means showing the field in a form (without actual editing).

So if you edit the field via update query or VBA, you are not allowed to display it in a form at the same time.