Error 3188 - Couldn't update; currently locked by another session

[JWS315]

I have an Access 2003 MDE FE/BE solution that is getting an error 3188 on a
specific record. The error only occurs when the user selects the entry and
clicks the "Update" button (the entry is added without any problem, it is
only when they want to update/add aditional text to the memo field). The
code below is what is used to do the update:

dbIASSecure.Execute "UPDATE [Strength]" & _
"SET StrengthFB= '" & Replace(Forms!responses!txtStrength, "'", "''") &
"', KeyStrength= " & Forms!responses!chkKeyStrength & ", StrengthCC= " &
Forms!responses!chkStrengthCC & ", " & _
"STEvalFactors= """ & tmpSTEvalFactors & """, collabid= """ &
tmpCollabid & """, STKBF= """ & Forms!responses!txtstkbf & """ " & _
"WHERE Id= " & Forms!responses!lstStrength.Column(2) & " AND
Organization= """ & Forms!responses!lstStrength.Column(1) & """ AND Qnum= " &
Forms!responses!lstStrength.Column(0) & " WITH OWNERACCESS OPTION"

The field that is being changed is a memo field and they have no problems
with updating other records for this field - it only happens on a specific
record.

I have the Open Databases with record-level locking checked and Default
record locking set to No Locks on the options tab.

Thanks in advance for any help or suggestions.

Jerry

 

[Wally]

I posted essentially the same question yesterday, title 'memo field
corruption' as below:
*************************
HI
I developed an app (Ac2000/FE/BE) some time ago and all was fine.

Suddenly the BE is getting corrupted when updating a memo field. I've tried
updating via ADO CMD object, Docmd.runsql and now via an ADO recordset. I've
seen some postings that suggest problems when memo size gets over certain
number of chars. That sound correct because some records now have large
(<2K)ammounts a data in the memo field and they are the one giving the
problem, smaller size fields work fine.

Anyone got any input, workarounds etc.

regards


walter
****************************

I've seen lots of references to this on other sites but a deafening silence
from MS.

 

[Dabaum]

So I just barely got this same error. Been all over the web to different
forums and everything trying to find a solution to this. It worked fine 2
weeks ago. Now it seems since that error popped up, several things have
stopped working correctly. The code hasn't changed. I was working on a
replicated database with FE and BE. When I finished the design changes I
synchronized it with the one. None of the changes transfered over. And then
this error popped up. So now that I am focussing on the portion of code that
blows up other listboxes are now not functioning. The code still haven't
changed from the backup I made before syncrhonizing.

I posted essentially the same question yesterday, title 'memo field
corruption' as below:
*************************
HI
I developed an app (Ac2000/FE/BE) some time ago and all was fine.

Suddenly the BE is getting corrupted when updating a memo field. I've tried
updating via ADO CMD object, Docmd.runsql and now via an ADO recordset. I've
seen some postings that suggest problems when memo size gets over certain
number of chars. That sound correct because some records now have large
(<2K)ammounts a data in the memo field and they are the one giving the
problem, smaller size fields work fine.

Anyone got any input, workarounds etc.

regards


walter
****************************

I've seen lots of references to this on other sites but a deafening silence
from MS.

I have an Access 2003 MDE FE/BE solution that is getting an error 3188 on a
specific record. The error only occurs when the user selects the entry and
clicks the "Update" button (the entry is added without any problem, it is
only when they want to update/add aditional text to the memo field). The
code below is what is used to do the update:

dbIASSecure.Execute "UPDATE [Strength]" & _
"SET StrengthFB= '" & Replace(Forms!responses!txtStrength, "'", "''") &
"', KeyStrength= " & Forms!responses!chkKeyStrength & ", StrengthCC= " &
Forms!responses!chkStrengthCC & ", " & _
"STEvalFactors= """ & tmpSTEvalFactors & """, collabid= """ &
tmpCollabid & """, STKBF= """ & Forms!responses!txtstkbf & """ " & _
"WHERE Id= " & Forms!responses!lstStrength.Column(2) & " AND
Organization= """ & Forms!responses!lstStrength.Column(1) & """ AND Qnum= " &
Forms!responses!lstStrength.Column(0) & " WITH OWNERACCESS OPTION"

The field that is being changed is a memo field and they have no problems
with updating other records for this field - it only happens on a specific
record.

I have the Open Databases with record-level locking checked and Default
record locking set to No Locks on the options tab.

Thanks in advance for any help or suggestions.

Jerry

 

[Dabaum]

I have a main form that holds a field that holds memo data. Complaints were
made that the memos were disappearing and the request was brought to me to
store each piece of the memo as an individual note. Since all the memo field
did was store whatever the users notes were for that day. Now I built a form
that stores each note and for small entries it copies all the notes into the
table where the Memo is then displayed from the Control. I don't think it is
a matter of memo size because none of them exceed the 65K char limit.
However it does seem like if there are too many notes then the update in the
Memo field won't occur. I've posted the code that I used here. Hope it
helps you understand what I am trying to do.



Private Sub cmdClose_Click()
On Error GoTo Err_cmdClose_Click
'test to see if user is in process of editing or adding a new note.
If bEdit Or bAdd Then
If Me.NewRecord Then
DoCmd.DoMenuItem acFormBar, acEditMenu, acUndo, , acMenuVer70
Me.Memo.BackColor = rgb(190, 190, 190)
Call UpdateList
End If
bAdd = False
bEdit = False
Me.cmdAddRec.Enabled = True
Me.cmdAddRec.Caption = "Add Note"
Me.cmdUpdate.Enabled = True
Me.cmdUpdate.Caption = "Edit Note"
Me.NoteDate.Locked = True
Me.cboRER.Locked = True
Me.cboEntryType.Locked = True
Me.Memo.Locked = True
Me.Memo.BackColor = rgb(190, 190, 190)
End If
Call BuildMemoField(Me.PropertyID)
DoCmd.Close acForm, "sfrmPropertiesUpdates"

Exit_cmdClose_Click:
Exit Sub

Err_cmdClose_Click:
MsgBox Err.Description & ": " & Err.Number
Resume Exit_cmdClose_Click

End Sub

Private Function BuildMemoField(ByVal PropertyID As String)
On Error GoTo Err_buildMemoField
Dim dbs As Database, qdf As QueryDef, strSQL As String
Dim DeletedQ2 As Boolean
Dim DeletedQ1 As Boolean
DeletedQ1 = True
DeletedQ2 = True

Set dbs = CurrentDb
strSQL = "SELECT PropertyID, PropertyNumber, NoteDate, entryType, RER,
Contacts, Memo " & _
"From tblPropertiesUpdates " & _
"WHERE (((tblPropertiesUpdates.PropertyID) = " & PropertyID &
")) " & _
"ORDER BY NoteDate DESC;"

Set qdf = dbs.CreateQueryDef("qryUpdateMemo", strSQL)
DeletedQ1 = False
Dim strMemo As String
Dim records As DAO.Recordset
Set records = qdf.OpenRecordset
Do Until records.EOF
'FieldType (records.Fields("Memo").Type)
strMemo = strMemo & CStr(records.Fields("NoteDate").Value) & ": "
strMemo = strMemo & CStr(records.Fields("entryType").Value) & "; "
strMemo = strMemo & CStr(records.Fields("RER").Value)
If IsNull(records.Fields("Contacts")) Then
strMemo = strMemo & vbNewLine & " " &
CStr(records.Fields("Memo").Value) & vbNewLine & vbNewLine
Else
strMemo = strMemo & "- " &
CStr(records.Fields("Contacts").Value) & vbNewLine
strMemo = strMemo & " " & CStr(records.Fields("Memo").Value)
& vbNewLine & vbNewLine
End If
records.MoveNext
Loop
'MsgBox Len(strMemo), vbInformation, "Length of Memo"
dbs.QueryDefs.Delete "qryUpdateMemo"
DeletedQ1 = True
strSQL = "SELECT Memo From tblProperties WHERE tblProperties.ID = " &
PropertyID & ";"
Set qdf = dbs.CreateQueryDef("qryUpdatePropMemo", strSQL)
DeletedQ2 = False
Set records = qdf.OpenRecordset
Form_frmPropertiesNew.Refresh
records.Edit
records.Fields("Memo") = strMemo
records.Update
dbs.QueryDefs.Delete "qryUpdatePropMemo"
DeletedQ2 = True
Set qdf = Nothing
Set dbs = Nothing
Set records = Nothing
Exit Function

Err_buildMemoField:
MsgBox Err.Description & ": " & Err.Number
If Not DeletedQ1 Then dbs.QueryDefs.Delete "qryUpdateMemo"

If Not DeletedQ2 Then dbs.QueryDefs.Delete "qryUpdatePropMemo"
End Function

 

[Dabaum]

I don't know if this solution will do it for everyone, but I found that my
session was being locked because I edited the memo from a form separate from
the one it was displayed on (memo is stored as many individual notes that are
then compiled together and updated in the respective table.

I inserted this little bit of code and everything is working great now.

Set records = tDef.OpenRecordset(dbOpenDynaset)
records.FindFirst "ID = " & PropID
If Not records.NoMatch Then
DoCmd.Close acForm, "frmPropertiesNew", acSaveYes
records.Edit
records.Fields("Memo") = strMemo
records.Update
DoCmd.OpenForm "frmPropertiesNew", acNormal, , "ID = " &
Me.PropertyID
End If

where ID is the key to tblProperties, and PropertyID is the key in
tblPropertiesUpdates that links all notes with that property.