Solved

Cannot save memo field when more than 1 user and 356 chars.

Posted on 2011-03-08
39
511 Views
Last Modified: 2012-06-27
Please see attached a front end and back end database. There is only one table and one form.

On the form there is only one field - which updates a memo field in the table in the back end.

When two users have the form open (displaying the one and only record, 356 chars counted in Word) if you add one more characters to the record it cannot be saved

If you delete a character, then save, then you can then replace it.

For example open the front end on two pc's, open the form and try to add the letter E to the very end of the form.

You get a message saying the record is locked on the other machine.

If you have the form open exclusively then you can save OK

The original form was more complicated but I have cut this down to show that the other stuff I had on the form was not causing the problem.

Do I have the Access Options for refresh and locking set correctly?

I am at a loss to explain this.

My customer discovered the problem on his network - a different setup completely to mine.

Any ideas?

Lou  NotesProblem.zip
0
Comment
Question by:Louverril
  • 20
  • 16
  • 2
  • +1
39 Comments
 
LVL 1

Expert Comment

by:reginaldmerritt
Comment Utility
0
 

Author Comment

by:Louverril
Comment Utility
The link refers to a completely different problem.

There is no problem saving the record etc. as long as it is below a certain number of characters. As can be seen from the attached files no workgroups are involved.

Thanks anyway,

Lou

0
 

Author Comment

by:Louverril
Comment Utility
This applies to ANY memo field in any record.

The length seems to be limited to around 90 - 150 lines  / 356 chars.

Any one who has time try and see if you can get one to work.

fornt end on two pc's with a simple form and containing a memo field. Once you gt passed a certain length one then bothe users are locked out!

Lou
0
 
LVL 57

Assisted Solution

by:Jim Dettman (Microsoft MVP/ EE MVE)
Jim Dettman (Microsoft MVP/ EE MVE) earned 500 total points
Comment Utility
Lou,

  If I'm understanding this correctly, then unfortunately this is a case of "that's the way it works".  Memo and OLE fields > 32 bytes are stored on LVP (Long Value Pages) apart from the rest of the record.

  The fact that you can delete a character and then save without issue is because you are working within the space already reserved for the memo field.

  But adding a character at that point is most likely triggering the process to gain new LVP space and you get a record lock because another user is in there reading the page, so it can't reorganize the page.  Why it's trying to reorganize the page though rather then simply adding a storage segment is a guess though as the interals of JET/ACE have never been fully documented.

   It may also be that for ACE, they've modified the threshold of 32 bytes for deciding when to store a Memo or OLE field on the main record or in a LVP page.

  I would suggest changing the locking mode to pessimisitic (Edited Record) and see if that helps.   That way you are placing a lock on the record as soon as you type a character up until the point the record is saved.  The second user will get a record locked message up front then as soon as they go to type a character and there should be no issue with the first user saving additional characters.

JimD.

0
 
LVL 57

Expert Comment

by:Jim Dettman (Microsoft MVP/ EE MVE)
Comment Utility

  Also make sure your set for Record level locking (File, Options, Client Level Settings and check "Open databases by using record level locking" under Advanced).

JimD.
0
 
LVL 14

Expert Comment

by:Don Thomson
Comment Utility
You should be able to enter 65K of data in a standard memo field - and 1 gig if you add it through programming

Which version of access are you using and how large is the file

2003 still has a 2 gig limit overall
0
 

Author Comment

by:Louverril
Comment Utility
Thanks for your response Jim,

I suspected something like that related to the size of the record  but I had no knowledge of LVP etc.

I had already tried out your suggestion. I had set the form property "Record Locks" to "Edited Record".

This did lock the other user out while the updates were being made (i.e. while the record was Dirty) - even on short records (as it's supposed to). But once you got "over the limit" regardless of whether you were the one who got there first you couldn't save the record.

Your reply prompted to check if this was also the case with the Defalt Record Locking set to "Edited Record" in the database Access Options. The same thing occurs though.

I have also tried opening and closing the form on each PC - but once the size is passed a certain point neither PC can then edit it. The only way to edit the record is for only one PC to have it open.

I am trying to think laterally for a way around this but I am stuck. I am thinking temporary table and a button on the ribbon that opens and closes the notes form to update it on eachPC. But that's going to be very messy....

Lou
0
 

Author Comment

by:Louverril
Comment Utility
DTHConsulting:

Yes I know - but you just try doing that in the situation I described above - two front ends and a back end, bothusers have the front end open at the same record.

As mentioned if only one use has the record open then no problrm you have your 65K

Lou
0
 
LVL 57

Expert Comment

by:Jim Dettman (Microsoft MVP/ EE MVE)
Comment Utility
<<This did lock the other user out while the updates were being made (i.e. while the record was Dirty) - even on short records (as it's supposed to). But once you got "over the limit" regardless of whether you were the one who got there first you couldn't save the record.>>

  That's really odd.

  I can't think of anything else and I've never heard of anyone with this problem before.  Did you create this new test DB from scratch or from the clients?

  I'm wondering if there isn't some corruption at work here...

JimD.
0
 

Author Comment

by:Louverril
Comment Utility
Jim,

I created one from the clients, one  COMPLETELY from scratch and another from another clients!

All have exactly the same problem.

Also I tested it with the form based on a query not a table.

Although I didn't test the record locks out with the form based on a query. I'll give it a go.

Lou

0
 
LVL 57

Expert Comment

by:Jim Dettman (Microsoft MVP/ EE MVE)
Comment Utility

  I'll look at it again, and if I still can't figure anything out, I'll ask around and see if I can't get some answers.

JimD.
0
 

Author Comment

by:Louverril
Comment Utility
TEST-Memo-Field-Dbs.zip message when size is exceeded
Thank you Jim.

I recreated the table (auto primary key and one memo field - rich text)  and then used the form creator to create a simple form. I created a query to use as the reocrdsource. I still had the same problem.

I have attached some sample databases (all created form scratch - no code - very  very basic) ) and instructions so you can more quickly recreate the problem.

The instructions below are also attached in a more readable formatted pdf version : Instructions to recreate the problem.pdf

I feel like no one beliewves me that this is happening so I took the time to make it easy for people to recreate,

The front ends are all mapped to Y as a  backend location - but of course you can change this.

Thanks Lou


Instructions/Description of Problem:

Issue - Memo field cannot save is multiuser environment

This describes how to recreate the problem where more than one user cannot update a memo field after the contents of the filed exceed a certain size (about 100 very simple lines).
There are four databases in all – see attached.
Two front ends TEST-FE-EditlockonDb.accdb and TEST-FE-NoLocksonDb.accdb and two back ends TEST-EditLockOn_BE and TEST-NoLocks_BE.
Each of these databases either has EditLockOn set in the Access Options Advanced or has No Locks set.
All databases have “Open databases by using Record Level Locking” checked.
Inside the two front ends there are four forms and one table. They are all copies of the same form but two are based on a query of the table and two use the table as the direct record source. For each type of record source there is a version of the form which has Lock On Edit set in the forms properties and one with No Locks set in the forms properties.
Put TEST-EditLockOn_BE and TEST-NoLocks_BE on a file server.
Put Front Ends, TEST-FE-EditlockonDb.accdb and TEST-FE-NoLocksonDb.accdb, on both of two PC’s, A and B

Test One (repeat with other versions of dbases and forms - if required) but this (TEST-FE-EditlockonDb  & TEST-EditLockOn_BE ) is the combination which out of them all should NOT have a problem as the locking is so controlled.
Use TEST-FE-EditlockonDb.accdb
Link front end table to TEST-EditLockOn_BE
Open form frmTestMemoLockOnEdit-Qry on  PC A and paste the type around 30 lines of text – could just be numbers, see below. Click Save on Ribbon.
Open form frmTestMemoLockOnEdit-Qry on PC B and add around a ten line chunk to the memo field – e.g. numbers 1-10 each on separate rows. Click Save on Ribbon.
Keep adding chunks of a few lines and saving on alternate PC’s – eventually (at about 100 lines) one PC will get this message “Could Not Save, currently locked by another user” (see attached for actual message.)
 
The text below was entered in blocks – when the length got to the point indicated the user could not save (the numbers 2 – 9). The last successful save was after the last “Typing something else” was saved.

Text that was entered - in chunks on alternate PC's.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
The lazy brown fox jumped over the quick dog
Today is Thursday 10th March
2
3
4
5
6
7
8
9
Typing anything
 
2
3
4
5
6
7
8
9
Typing something else
 
2
3
4
5
6
7
8
9
Typing something else
 
2
3
4
5
6
7
8
9
 Typing something else
 
2
3
4
5
6
7
8
9
 Typing something else
 
2
3
4
5
6
7
8
9
 Typing something else
 
2
3
4
5
6
7
8
9
 Typing something else
 
2
3
4
5
6
7
8
9
 Typing something else
 
2
3
4
5
6
7
8
9
 Typing something else
 
2
3
4
5
6
7
8
9 <- got to here and could not save.
 Recreate-the-Mermo-field-problem.pdf Recreate-the-Mermo-field-problem.pdf
0
 

Author Comment

by:Louverril
Comment Utility
Sorry attached intructions twice....
0
 
LVL 57

Expert Comment

by:Jim Dettman (Microsoft MVP/ EE MVE)
Comment Utility

 No problem.  I still don't see why this doesn't work and it does look like a bug, so I've passed your first DB onto the Access Team at Microsoft.   Might be a day or two before we get an answer back.

JimD.
0
 

Author Comment

by:Louverril
Comment Utility
Thanks Jim!

Will wait to hear.

Thanks for taking this seriously.

Lou
0
 
LVL 57

Expert Comment

by:Jim Dettman (Microsoft MVP/ EE MVE)
Comment Utility
Lou,

 Well early word is that it is a bug related to it being a Rich Text field, which is stored as HTML.  If you change it to plain text, the problem goes away.

 Looking at your original database, for those 350 or so characters, the resulting HTML is a little over 2000.  Since JET stores things in unicode, which is two bytes per character, and page size in JET is 4096K, it seems like this occurs when it needs another page of storage.

  Some how fetching a new page along with handling the HTML is causing the problem.

  I doubt we'll see any offical response from Mcirosoft on this anytime soon, so in the meantime, can you work with it as plain text or no?

JimD.
0
 

Author Comment

by:Louverril
Comment Utility
Plain text would not really be anywhere near as good for this particular client with the notepad - his small team have been using it for 7 months now and they highlight stuff - change the colour etc to break up the notes. They find it really useful to pass on messages and reminders and to make sure everyone sees them.

I can't even really see any scope for a sort of fiddly work around. Do you think it would be worth trying to capture the updates and trying to add them to the record via vba - somehow!!! Probably not.

As an option I could say well you'll have to accept there is a length restriction - but how can I handle that to stop it getting to the point where the record locks eveyone out? Some way of counting the record size....?

If not then he'll just have to cope with plain text.

When you say "doubt we'll see official response form Microsoft on this anytime soon"  Will I ever hear anything?

Thanks again,

Lou
0
 
LVL 57

Accepted Solution

by:
Jim Dettman (Microsoft MVP/ EE MVE) earned 500 total points
Comment Utility
<<As an option I could say well you'll have to accept there is a length restriction - but how can I handle that to stop it getting to the point where the record locks eveyone out? Some way of counting the record size....?>>

  Only thing that comes to mind would be a diary type system; individual records for each note date/time stamped rather then saving all the notes into a single field.    You could limit them too a single page worth of notes and thus avoid the problem.

  That's probably fairly workable for them and closest to what they have now, but it means some work for you.  However adding another table and a sub-form for entry shouldn't be that much of a problem (I say that though without knowing how extensive these notes are used of course)

<<As an option I could say well you'll have to accept there is a length restriction - but how can I handle that to stop it getting to the point where the record locks eveyone out? Some way of counting the record size....?>>

  What you'd need to do is check the size with LEN() in the OnChange event of the field (or BeforeUpdate) and limit them to 2000 characters.  That would keep it within the page limit and avoid the locking issue.

<<When you say "doubt we'll see official response form Microsoft on this anytime soon"  Will I ever hear anything? >>

   I was talking on the order of days and it's very hard to say.  I'm sure we'll get some type of answer back, but the fix might take considerably longer.  It may be in the form of a hot patch, or it might end up waiting for the next SP.  Then again, I've seen bugs never get fixed.

  However this appears to be somewhat serious in nature, so I would think they'll address it, but I can't speak for them.  And at this point, the actual problem has not even been identified exactly or what it will take to fix it.

  I'll post back here if I hear anything.

JimD.






0
 

Author Comment

by:Louverril
Comment Utility
Thanks Jim,

I'll look at the length idea as an option for them . At least it stops them getting locked out. I'll give them that option or plain text. Cross fingers they'll stick with me.

I am not sure about the individual records way of going though - it's would be very good in some  circumstances but they like the simplicity of the single sheet notepad.

I really hope there is a fix for it - another new customer really likes the idea of that notepad on their front screen - it's simple and it means everybody in the office see's the same thing. For them I have also added a couple of dashboard type "subforms" on there, where they can see clients with actions due/clients with actions overdue.  So the  form acts as a sort of round up and central communication point for the whole system.

Shall I leave the question open?

Thanks Lou

0
Backup Your Microsoft Windows Server®

Backup all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

 
LVL 14

Expert Comment

by:Don Thomson
Comment Utility
Until a fix is found for this - have you considered just linking the field to a word document - Then you can use all the formatting - and have an unlimited size limit
0
 
LVL 57

Expert Comment

by:Jim Dettman (Microsoft MVP/ EE MVE)
Comment Utility
<<I really hope there is a fix for it - another new customer really likes the idea of that notepad on their front screen - it's simple and it means everybody in the office see's the same thing. For them I have also added a couple of dashboard type "subforms" on there, where they can see clients with actions due/clients with actions overdue.  So the  form acts as a sort of round up and central communication point for the whole system.>>

  Another option, which might get around the locking issue is breaking out the memo field into another table and doing a 1:1 relationship.

<<Shall I leave the question open?>>

  Yes, leave it open for the moment.  However if it becomes abandoned, just post a single comment to get it flagged active again.

  I'd like to hear something back from Microsoft before we close this out.

JimD.
0
 

Author Comment

by:Louverril
Comment Utility
<<  Another option, which might get around the locking issue is breaking out the memo field into another table and doing a 1:1 relationship.>>

That's interesting I'll give it a go.

<<  Yes, leave it open for the moment.  However if it becomes abandoned, just post a single comment to get it flagged active again.>>

OK

<<I'd like to hear something back from Microsoft before we close this out.>>

Thanks again!!!!!!!!!

Lou
0
 
LVL 57

Expert Comment

by:Jim Dettman (Microsoft MVP/ EE MVE)
Comment Utility
<<<<  Another option, which might get around the locking issue is breaking out the memo field into another table and doing a 1:1 relationship.>>

That's interesting I'll give it a go.
>>

  Personally I don't like the idea because as soon as they move to the memo field, the main record will be committed.  That leaves lots of confusion for someone that tries to undo changes and can't.

JimD.
0
 

Author Comment

by:Louverril
Comment Utility
That may be OK for this particular customer as there is no only one other field on the menu form and that is a drop down list they use to quickly check prices - they can't update it.

I will have to check it out - thanks for the warning.

Lou
0
 

Author Comment

by:Louverril
Comment Utility
I tried the 1:1 idea but hit the same problem once the record length went over a certain size.

Below is what I did - did I understand your idea?

Two tables tblA, tblB

tblA:
one key field fldKey

tblB:
two fields
fldKey
fldNotes

I set up a one to one  relationship between the two key fields and giving then a value of 1.

frmMenu record source was a query fldKey from tblA and fldNotes from tblB


Lou
0
 
LVL 57

Expert Comment

by:Jim Dettman (Microsoft MVP/ EE MVE)
Comment Utility
Lou,

<<frmMenu record source was a query fldKey from tblA and fldNotes from tblB>>

  needs to be a main /subform, main bound to tblA and subform to tableB.

JimD.
0
 

Author Comment

by:Louverril
Comment Utility
Jim,

I have tried the subform method and I still get the same problem.

See the attached databases (FE asnd BE) and frmNotes in the FE.

Thanks

Lou MemoProblemExampleUsingSubform.zip
0
 
LVL 57

Expert Comment

by:Jim Dettman (Microsoft MVP/ EE MVE)
Comment Utility
Lou,

  Bummer, that's a suprise.  I'll try it here when I get a chance just to double check, but I don't doubt your right.

JimD.
0
 

Author Comment

by:Louverril
Comment Utility
Please can you keep this question open until  J Dettman has some more feedback.

Thanks
0
 
LVL 57

Expert Comment

by:Jim Dettman (Microsoft MVP/ EE MVE)
Comment Utility

 I have not heard anything yet.  I'll poke them again.

JimD.
0
 

Author Comment

by:Louverril
Comment Utility
Jim,

Just to let you know I have put in place a length limit - as you described. This is a least stopping the error from occurring.

Thanks
0
 
LVL 57

Expert Comment

by:Jim Dettman (Microsoft MVP/ EE MVE)
Comment Utility

 So far, no response from the Access team.  I'm going to ping them one more time.

JimD.
0
 

Author Comment

by:Louverril
Comment Utility
Thanks Jim.

I think it is an important feature. Dashboards etc are getting much more popular. My customers find it really useful that the whole team sees the same notes/messages when they log on.

Thanks again,

Lou.
0
 
LVL 57

Expert Comment

by:Jim Dettman (Microsoft MVP/ EE MVE)
Comment Utility
Lou,

  I'm still pushing for an answer; even just to get them to acknowledge the bug.

  Stay tuned...

JimD.
0
 

Author Comment

by:Louverril
Comment Utility
Thanks!
0
 
LVL 57

Expert Comment

by:Jim Dettman (Microsoft MVP/ EE MVE)
Comment Utility
Lou,

  I've pushed on this and got nothing back on it.  I know the Access team has been busy, but gut tells me that it is probably a fairly serious bug and there is no quick solution.

  Might as well close this.

JimD.
0
 

Author Comment

by:Louverril
Comment Utility
OK - shame.


Thanks for you help.
0
 

Author Closing Comment

by:Louverril
Comment Utility
There wasn't a solution to the issue. However Jim Detman gave ways of getting around the issue which might be useful to other people.

Lou
0
 
LVL 57

Expert Comment

by:Jim Dettman (Microsoft MVP/ EE MVE)
Comment Utility
<<OK - shame.>>

  Yes. I'm more than a little bummed.  I expected an un-official response of some type at the very least and not to be simply ignored.

JimD.
0

Featured Post

IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

Introduction When developing Access applications, often we need to know whether an object exists.  This article presents a quick and reliable routine to determine if an object exists without that object being opened. If you wanted to inspect/ite…
Describes a method of obtaining an object variable to an already running instance of Microsoft Access so that it can be controlled via automation.
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
Learn how to number pages in an Access report over each group. Activate two pass printing by referencing the pages property: Add code to the Page Footers OnFormat event to capture the pages as there occur for each group. Use the pages property to …

771 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now