?
Solved

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

Posted on 2011-03-08
39
Medium Priority
?
571 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 20
  • 16
  • 2
  • +1
39 Comments
 

Author Comment

by:Louverril
ID: 35081512
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
ID: 35082558
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
Veeam Disaster Recovery in Microsoft Azure

Veeam PN for Microsoft Azure is a FREE solution designed to simplify and automate the setup of a DR site in Microsoft Azure using lightweight software-defined networking. It reduces the complexity of VPN deployments and is designed for businesses of ALL sizes.

 
LVL 58

Assisted Solution

by:Jim Dettman (Microsoft MVP/ EE MVE)
Jim Dettman (Microsoft MVP/ EE MVE) earned 2000 total points
ID: 35083331
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 58
ID: 35083475

  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
ID: 35083745
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
ID: 35084031
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
ID: 35084075
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 58
ID: 35084402
<<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
ID: 35086265
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 58
ID: 35086431

  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
ID: 35094882
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
ID: 35094942
Sorry attached intructions twice....
0
 
LVL 58
ID: 35095637

 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
ID: 35098076
Thanks Jim!

Will wait to hear.

Thanks for taking this seriously.

Lou
0
 
LVL 58
ID: 35098555
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
ID: 35098714
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 58

Accepted Solution

by:
Jim Dettman (Microsoft MVP/ EE MVE) earned 2000 total points
ID: 35099349
<<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
ID: 35107312
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
 
LVL 14

Expert Comment

by:Don Thomson
ID: 35107619
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 58
ID: 35108770
<<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
ID: 35110013
<<  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 58
ID: 35110625
<<<<  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
ID: 35115814
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
ID: 35115956
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 58
ID: 35121808
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
ID: 35130322
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 58
ID: 35130486
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
ID: 35180136
Please can you keep this question open until  J Dettman has some more feedback.

Thanks
0
 
LVL 58
ID: 35180301

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

JimD.
0
 

Author Comment

by:Louverril
ID: 35182047
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 58
ID: 35305171

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

JimD.
0
 

Author Comment

by:Louverril
ID: 35321580
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 58
ID: 35323781
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
ID: 35331792
Thanks!
0
 
LVL 58
ID: 35418496
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
ID: 35423173
OK - shame.


Thanks for you help.
0
 

Author Closing Comment

by:Louverril
ID: 35423207
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 58
ID: 35424247
<<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

Prepare for your VMware VCP6-DCV exam.

Josh Coen and Jason Langer have prepared the latest edition of VCP study guide. Both authors have been working in the IT field for more than a decade, and both hold VMware certifications. This 163-page guide covers all 10 of the exam blueprint sections.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

It’s been over a month into 2017, and there is already a sophisticated Gmail phishing email making it rounds. New techniques and tactics, have given hackers a way to authentically impersonate your contacts.How it Works The attack works by targeti…
The Windows Phone Theme Colours is a tight, powerful, and well balanced palette. This tiny Access application makes it a snap to select and pick a value. And it doubles as an intro to implementing WithEvents, one of Access' hidden gems.
With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…
Add bar graphs to Access queries using Unicode block characters. Graphs appear on every record in the color you want. Give life to numbers. Hopes this gives you ideas on visualizing your data in new ways ~ Create a calculated field in a query: …

752 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