Problem using Replace function with Memo field

[Christine in New Jersey]

I am using the Replace function with a recordset field variable to replace
all double-quotes with single-quotes in a Memo field.

If the Memo field contains more than 2036 characters, the following error
message is displayed:

***************
Runtime error: '3188'

Could not update; currently locked by another session on this machine.
***************

As soon as I reduce the number of characters to 2036 or less, the error no
longer occurs. There is NO other copy of the database running. I often find
that Microsoft error messages do not provide the real reason for the error.

If anyone can help, I would appreciate it.

Thanks!

 

[John Nurick]

Hi Christine,

I haven't been able to reproduce this. Here's the code I used; it works
fine replacing " with ' in a memo field about 6000 characters long
containing more than 1200 " characters.

Public Sub test()
Dim rst As DAO.Recordset
Dim S As String

Set rst = CurrentDb.OpenRecordset("tblTwo")
Do Until rst.EOF
S = Replace(rst.Fields("Sequence").Value, """", "'")
Debug.Print Len(S), Mid(S, 500, 80)
rst.Edit
rst.Fields("Sequence").Value = S
rst.Update
rst.MoveNext
Loop
End Sub

Can you share some more information about what you're doing, and with
what versions and service packs of Office and Windows?

Note that the code above should in real life be replaced with a query:

UPDATE tblTwo SET Sequence = Replace([Sequence],"""","'");

 

[Christine in New Jersey]

John:

Thanks for replying. Sorry I have taken so long to get back to you, but I
needed to work on another project before I could get back to this one.

I've determined that the problem is not with the replace function. Rather,
whenever I attempt in code to change the field value of a memo field that
contains more than 2036 characters, I get the following error:

"Run-time '3188': Could not update; currently locked by another session on
this machine"

This happens not only on my computer using Access 2003, but also on my
client's computer using Access 2000.

Fields that are 2036 characters or less work fine.

Here is a reprensentation of the actual code:

With vTable (recordset variable; vFieldName string variable also defined)
(other code here)
.Edit
.Fields(vFieldName) = "This is a test" ***ERROR OCCURS HERE***
.Update
End With

Any ideas?

Thanks, Christine

 

[John Nurick]

Hi Christine,

One possibility is that the message means what it says, that something
else (a bound form?) has locked the record(s) in question.

But a web search suggest that it's also possible that the database (or
to be precise the part of the MDB file where the contents of this memo
field in these particular records are stored) has become corrupt.

See Tony Toews's corruption FAQ at
http://www.granite.ab.ca/access/corruptmdbs.htm, especially "To retrieve
your data from a Corrupt Microsoft Access MDB" at
http://www.granite.ab.ca/acces­s/corruption/corruptrecords.ht­m

 

[Christine in New Jersey]

John:

Thanks. I will try creating a new database file and copy the data to the new
file and see if that solves the problem. I will let you know how I make out.

Christine

 

[Christine in New Jersey]

John:

Yes, there is a form open that is displaying the record while the procedure
is running. Closing the form, then re-opening it after the procedure is
finished solves the problem. Funny, though, that it's only an issue when the
contents of the Memo field get to a certain size.

Thanks so much for your guidance.

Christine

 

[John Nurick]

I'm glad that was the problem. The reason it only happens with longer
field contents is no doubt something complicated to do with pages and
record locking, which I have no intention of learning about<g>.

Corruption of memo fields almost always seems to involve losing data.