Memo fields lock

[loRdK]

I'm using VB 5, JET 3.5 and a 2.0 Access database in a multiuser environment.

I have a memo field which keeps a large ammount of text on each record.

Setting the memo field value delays a bit (no problem), and I noticed that, while processing it, the JET engine keeps locking even if lockedits is set to false.

So when another user tries to set the value of a memo field, I got the runtime error 3197: The Microsoft Jet database engine stopped the process because you and another user are attempting to change the same data at the same time.

I'm sure the database is not corrupt.

Example:

User1:
-------
tb.lockedits=false
tb.edit
(...)
tb("MyMemo").value = Value$

User2:
-------
tb.lockedits=false
tb.edit
(...)
tb2("OtherMemo").value = Value$

While User1 processes the memo field setting request, if User2 tries to set another memo field value, I get the lock error.

Wouldn't lock happen only when processing the update method?

Can anyone help me, please?

Thanks.

 

[loRdK]

Correcting:

User1:
-------
tb.lockedits=false
tb.edit
(...)
tb("MyMemo").value = Value$

User2:
-------
tb2.lockedits=false ''CORRECTED HERE
tb2.edit ''CORRECTED HERE
(...)
tb2("OtherMemo").value = Value$

 

[loRdK]

Also tb and tb2 are different tables...
Thank you.

 

[vb5prgrmr]

loRdK, have you read FAQ222-2244 yet?

Yeah that is one of those funny things about access but what I do not see is the update statement in your psudo code and if you are not using it and relying upon the user to move next to update it that could be your problem.

Just a shot in the dark

Good Luck

 

[loRdK]

Thanks for your reply, vb5prgrmr.

I haven't read the FAQ222-2244. But the link you inserted points to Tek-Tips home page... can you tell me the right link?

I do use update in the full code, but the lock error happens before it is reached (in the line in which the value of the field is set).

If you think of something else, please let me know.

Thanks a lot.

 

[LostInCode]

A lock will happen automatically if the long binary (memo) field contains too much data. I don't remember the limitations.
In older versions a lock was placed on every LV pages (containing binary data). This was needed to insure that the user sees unchanged data with these large LV pages (because all pages did not get pulled in at once)
In Dao 3.51 and Jet sp3 this was changed to only place locks on the field when the data spanned over more than one page.
Consider using a seperate recordset and the GetChunk method to retrieve all of the data at once, and then close the rs after the data is retrieved.

 

[LostInCode]

> I don't remember the limitations

Duh. Of course it is one page of data, or 2048 bytes.