Records unexpectedly locked

[always404]

Windows XP (also tried this on Win7)
Access 2007

I'm having a strange problem updating a memo field. I have stripped the form down to a bare minimum and spent several hours trying to reproduce this error reliably so bear with me.

IF:

1) if a form "FORM1" has a memo field bound to a field in a query/table
2) FORM1 is *showing* (not necessarily editing) a record that has a memo field with more than 2048 characters
3) a second user attempts to edit the memo with FORM1
4) access attempts to save the record either when the user leaves the record or by running a vba command, while condition 2) is still fulfilled

THEN:

Access throws the error "Could not save; currently locked by another user"



_______________________

This is strange because:

1) if the record were being edited by another user, record-level locking would have caught this and prevented edits before user2 ever was able to start typing. FORM1 is set to lock edited records both as the database default, and in the form properties

2) this problem disappears when the char count in the memo field is less than 2048 characters, this seems to be some upper limit in a memory buffer somewhere? this is beyond my comprehension. the form behaves like normal until this threshold is passed

3) in the midst of editing this post i tried to reproduce this in a query and on the table, the same thing happens; if you view (by clicking on, not necessarily editing) the record with a memo of more than 2048 char, a second user cannot make save changes to the memo

_____________

possible solution:

maybe i could make an unbound text field that holds the memo text, and when a user edits it, the edit can be written to the record.

The only problem is that i must then handle record locking and unlocking another way

i'll take any ideas i can get! this is a very troublesome problem

[Missinglinq]

This obviously is being used in a multi-user environment, so the question has to be asked, has this app been split into a Front End (with Forms, Queries, Reports) and Back End (with Tables) configuration, with each user having a copy of the Front End on his/her PC? If not, this is probably the problem. Multi-users working on a single, shared file, can cause all sorts of problems, with the one you've reported being the most commonly seen!

The really insidious thing about this type of set up is that it can work, even for extended periods of time, without problems, before the errant behavior appears! But once these apps start misbehaving, the problem(s) always become chronic, appearing over and over and over again until the app is split properly.

Linq ;0)>

[always404]

I'm glad you asked. At one time the system was unified under one file.
However I split the database into a backend (tables only) and a frontend (forms, queries, etc) months ago.

But the real answer to your question is yes and no.
The users are accessing the same file, but it is now an accde (read only) that contains the front end.
I assumed that the users would be opening their own instances of this file and so this wouldn't be a problem.

However when i was troubleshooting and trying to make a fix I was using a single machine opening multiple instances of an accdb which is the "improperly split" scenario you were referring to. Although I must conclude that this cannot be the root of the problem, because users experience identical symptoms on a properly split database.

But I could be wrong, I am relatively new to this stuff.

[alcapps]

here is a good source for record locking.
http://www.databasedev.co.uk/multi-user-application-record-locking.html
http://allenbrowne.com/ser-63.html

you might need to change the locking to Edit Records instead of no locks.

I got this from another site lisaChow.
Thanks. I will try the SQL Update if I can get it to work in the background. That sounds easier to do and like less of a change for the users. If it doesn't work, I'll figure out the OLE Object way.

The problem is that when you have a memo field, you can only use a text box on a form. There is no "memo box". Even though a text field holds only 255 characters, a text box will hold 2,048. A text box will display more than 2,048 characters, but won't let the user make any changes on the form. It's an Access bug.

Hope this helps

[NTC]

give each user their own separate FE file

everything should work fine if you do that.

[Missinglinq]

...a text box will hold 2,048. A text box will display more than 2,048 characters, but won't let the user make any changes on the form. It's an Access bug...

Sorry, but the number of characters a Textbox can hold is 65,535 not 2084.

I just created two separate databases in 2003 and 2007 (on separate machines) and placed 25000+ characters into Memo Fields, via Textboxes, on Bound Forms, and had no problems either editing or saving edited changes. There are plenty of bugs, in Access, but this is not one.

It's very likely that there are reasons that will cause this behavior, but it has nothing to do with the length of a Memo Field or the fact that it is displayed using a Textbox.

As NTC said, the first step here is to place a copy of the Front End on each user's PC.

Linq ;0)>

[always404]

Okay, sorry about the delay but it is difficult for me to do a lot of testing in the limited amount of time i have allotted to database work.

I went ahead and made a new DB from scratch with just three fields: ID, TheName, TheMemo
The backend is on the server. I made a front end with a query that has all 3 fields. I then made a form that sources the query.
I confirmed that record level locking (edited-record) was set everywhere.

I then made an accde that i copied to two separate computers' desktops directories to ensure they were locally saved.

@NTC and Missinglinq: thanks, but this problem still happens even when there are separate copies per user on the most simple of databases

@alcapps: thank you, but I have already set record level locking (1st link) and i'm not performing any operations on the memos outside of displaying and saving (2nd link)

Does anyone think i should try to migrate to OLE objects as a workaround? that difficult but promising.
I'm still thinking manually handling displaying/saving an unbound textbox might be the solution.

If anyone wants a copy of my test, you're welcome to try it.

[Missinglinq]

If anyone wants a copy of my test, you're welcome to try it.

Happy to look at if you post the accdb file so we can look behind the scenes. Remove any confidential data, Zip it up and attach it.

Don't remember asking/reading, is this an Access Back End or something else?

Linq ;0)>

[always404]

Happy to look at if you post the accdb file so we can look behind the scenes. Remove any confidential data, Zip it up and attach it.

Don't remember asking/reading, is this an Access Back End or something else?

Linq ;0)>

I hope you don't mind, but I had to zip the file. It would not let me upload the accde.

DB FILES.zip

Edit: the contents are the accdb TESTDB which is the backend, an accde TESTFRONTEND, and another accdb TESTFRONTEND which the accde is based on
to replicate the error put the backend somewhere, doesn't really matter so long as you can access it with the front end
make sure the frontend is properly linked and open it, doesn't matter if you use the accde or not
open another instance of access, either on the same computer or a different one as long as you can access the same backend

to view proper behavior use the first instance and begin editing the name
use the second instance and attempt to edit the name (it will be locked and wont let you because of record level locking)

to view improper behavior and do the same with the memo field instead of name and the errors begin to happen

to view proper behavior again, try the same editing tricks on the empty memo fields or play with it with anything less than 2048 and the proper behavior continues

i kept this test case very very very simple so you can see that my code is not screwing things up; any insight you can provide would be wonderful

[always404]

Good news and bad news.
Although I've found a work around, I can't seem to figure out what was the matter with access in the original post.
My workaround seems to do the job, but there is some lag when running the sql string if a record is being edited elsewhere in the table, I'll see if I can iron that out though, then this will be (at least for my purposes) solved.

Private Sub Form_Current()


TheMemo.Value = DLookup("[TheMemo]", "Query1", ("[ID]=" & Forms!Form1!ID))


End Sub


Private Sub TheMemo_GotFocus()


On Error GoTo Handler
[TheName] = TheName.Value
TheMemo.Locked = False


Exit Sub


Handler:
TheMemo.Locked = True
MsgBox ("Memo is already being edited")


End Sub


Private Sub TheMemo_LostFocus()


DoCmd.SetWarnings False
DoCmd.RunCommand acCmdSaveRecord

Dim SQL As String
SQL = "UPDATE Table1 " & _
"SET TheMemo = '" & Me.TheMemo.Value & "'" & _
"WHERE Table1.ID = " & ID.Value
DoCmd.RunSQL SQL

DoCmd.RunCommand acCmdSaveRecord
DoCmd.SetWarnings True


TheMemo.Locked = True


End Sub

Since the memo field seems to be buggy, i'm handling it with an unbound text box. I force the record/form to be dirty any time the faux memo field has focus. The try/catch throws an error message if it can't be edited. The user then makes edits and when the fake memo field loses focus, a save command is issued to erase the dirty status, a sql statement performs the update i need, and the user is none the wiser. Good plan?