> Memo Field Will Not Update When More Than 1 User And 356 Chars

[Louverril]

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

 

[boblarson]

First up, EACH USER should have a copy of the frontend on their machine. Is that the case or are you trying to use a single frontend which everyone opens from a network location? If the latter then you need to ensure that each user gets a separate copy of the frontend.

Second, if more than one user is going to be having the same record open, and each editing it, then that is also a recipe for trouble.

 

[Louverril]

No each user has the front end on their local PC.

They have been using it daily for 7 months with no problem - until the length of the notes became an issue.

Although I am not convinced that its not something to do with the refresh rates etc on the Access Options Advanced?

Did you look at these on the samples?

Thanks!

 

[Louverril]

I have tested this out.

It is absolutely nothing to do with having the notes open on each pc. I changed to Record Locks edited record on the notes form and this works fine (if another user has made the record Dirty the record is locked - you can't change it - and thats fine) . BUT you still cannot add more than the 356 characters!!!!!!

That testing on two PC's. Forget everything else surely two people should be able to update a memo field every now an again and use the max approx 60,0000 chars?

Just to re-iterate there is NO problem apart from the number of characters.

Baffled!

Lou

 

[Louverril]

For info this response from JDetman on another forum and my response. Still got a problem any ideas VERY welcome!!

Jim said:

"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 intervals 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 pessimistic (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."


My reply:

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....

 

[missinglinq]

They have been using it daily for 7 months with no problem...

...surely two people should be able to update a memo field every now an again and use the max approx 60,0000 chars...

...there is NO problem apart from the number of characters.

Running a multi-user database without each user having a copy of the front end on their machine is a tried and true method of creating all kinds of problems, including corruption!

The fact that you had no problem for 7 months is totally meaningless! I've seen reports of such apps running smoothly for as long as 14 years and then bombing out! And once trouble starts it never stops! All it apparently takes is some combination of events to occur and BOOM!

As Bob suggested, you simply have to bite the bullet and split it, before you can even look at the problem with this field. Splitting it may, in fact, solve this problem.

Are you sure the character count was 356 rather than 256 and that the field is defined as a Memo field rather than Text? 256 is the maximum length for a Text field.

Linq ;0)>

 

[Louverril]

I thought I had made it clear that each user DOES have a front end

See earlier posts

"No each user has the front end on their local PC."

and

"Please see attached a front end and back end database.

and

"For example open the front end on two pc's,"