?
Solved

Creating Table For Memo Field

Posted on 2014-01-20
28
Medium Priority
?
1,156 Views
Last Modified: 2014-03-14
I've got a Memo field in the main table of my database.
About 10% of records have data stored in this field.
I'm having frequent recording locking problem with the memo field even though it's a single user database. This is despite much restarting, repairing, decompiling, recreation of entire records and of memo records with all html stripped out (using notepad) etc.
I also recreated the front/backends by importing into blank databases.

I'm going to run a make table query with only the RecordID and the memo field in the new table (Let's call it the memo table). I will have a one-to-one relationship between the two tables.

Do I need a corresponding record in the memo table for each record in the main table, or just those with data in the memo field? (The former seems the most likely to me).

When I create a new record in the main table, will a corresponding record be automatically created in the memo table?

Finally, a little help with the code that opens a popup form with the memo info.
This works a present where the memo field is in the main table.

   DoCmd.OpenForm "MEMOpopup", , , "RECORDid = " & Me.RECORDid

Will it work when the MEMOpopup Form is based on the new memo table?

Thank you for your time in reading through this longish question
and any help that you may provide.
0
Comment
Question by:Eirman
[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
  • 13
  • 5
  • 3
  • +1
28 Comments
 
LVL 58
ID: 39794493
<<and any help that you may provide. >>

 Well first the bad news; splitting off the memo fields into a one to one is not going to help with your locking problem.

 Memo and OLE fields are stored seperately on LVP (Long Value Pages) when they are >30 bytes.  LVP's are always locked at page level no matter what the settings.  

  So even if you split them off to a seperate table, then use a query to join the two and base your form on that, your right back where you started.

 The only way to avoid the locking problem is to use an unbound control, and get/put the data yourself as needed.  

  That's true if it's in a seperate table or not.

Jim.
0
 
LVL 58
ID: 39794497
You other option would be to convert the backend to SQL Server, which does locking differently.

Jim.
0
 
LVL 24

Author Comment

by:Eirman
ID: 39794515
The only way to avoid the locking problem is to use an unbound control, and get/put the data yourself as needed.
Can you explain in more detail please.


What happening is ....
I go to a record in a form ... all other form are closed.
Then open the POPUP form for the record (with existing data in the memo field).
I paste in an additional block of text and try to save ....
A warning informs me that record is locked by another user.

This is a very simple vocabulary database with <1000 records.
I'm amazed that Access can't cope with this.

I was following this advice, in hiving off the memos to a new table
http://bytes.com/topic/access/answers/947191-risks-involved-when-using-memo-fields-ms-access-databases
0
U.S. Department of Agriculture and Acronis Access

With the new era of mobile computing, smartphones and tablets, wireless communications and cloud services, the USDA sought to take advantage of a mobilized workforce and the blurring lines between personal and corporate computing resources.

 
LVL 58

Assisted Solution

by:Jim Dettman (Microsoft MVP/ EE MVE)
Jim Dettman (Microsoft MVP/ EE MVE) earned 500 total points
ID: 39794639
<<I go to a record in a form ... all other form are closed.
Then open the POPUP form for the record (with existing data in the memo field).
I paste in an additional block of text and try to save ....
A warning informs me that record is locked by another user.>>

 There are a couple of things at play here possibly and I should have been a bit clearer in the way I stated things.

 First, there is a bug in Access where if the memo field is in RTF format and it's more then approx 350 characters, you'll get a locking notification every time.   I don't think that bug ever been fixed.

 Second, and what I was talking about in my first comment, is that it's important to understand that memo and OLE fields are stored on LVP's.

 All the other fields are stored on a single page and a records can't span pages.

 So when you have a table that has a memo field, your are locking the "main" record, plus as many pages as needed for the memo fields (memo fields don't span pages, but linked multiple pages are used to give you text in chunks of 2048 bytes and combined giving you the appearence of a much larger field)

 There are two forms of locking; pessimistic and optimistic.  This controls when the lock is placed.   With pessimistic, the lock is placed as soon as a record starts to be edited and not released until a commit or rollback.

 With optimistic, a lock is placed only when the record is actually being updated.

 On top of that, JET can place that lock at page level (which may cause more then one record to be locked) or at record level.

  Record level locking though was an add-on and for it to work properly, you need to open the database using ADO in a specific way.  Otherwise, you get page level locking for everything.

  Now to add to the confusion; because record level locking was an add on, it wasn't set up all that well.   In the case of LVP's, locking is always done at the page level regardless of settings.

 Back to what I was talking about in the first comment; it doesn't matter if you split a memo field off to a seperate table or not if you bind a form (or forms) to a recordsource that contains a field from the main part of the record and the memo field.   You still end up with the same locking situation.

i.e. you split the memo off to a seperate table.   You build a query that joins the two and base a form on that.   Your now right back where you started.

What if you don't have a control on the main form for the memo field, but do a popup which does?  Your still right back where you started.

In order to avoid a lock on the memo page(s), you can do one of two things (and this is where I was not clear):

#1
1. Split the memo fields off from the main table.
2. Have a main form based on a recordsource which only looks at the main table.
3. Have a popup form that is based on the memo field table.

or

#2
1. Split the memo fields off from the main table.
2. Have a main form based on a recordsource which only looks at the main table.
3. Get/put the memo field data in code.

With that said, there is still one last thing you need to understand; two users could bump into one another on the memo field table even if you've specified record level locking as there may be more then one record to a page, and locking is always done on page level

 That's why if I really need memo fields, I generally go with #2 above.   I use pessimistic record level locking on the main part of the record, and update the memo field in a seperate table with code.   Locks on the memo field table are optimistic at page level.

  That allows for more control over the process and I can allow for editing of all the fields on a single form.   You could also leave everything as is, stick with optimistic locking, and allow for getting lock conflicts.

Jim.
0
 
LVL 38

Assisted Solution

by:PatHartman
PatHartman earned 320 total points
ID: 39794694
Before you open the popup form, you need to save the current record otherwise, if the current record is dirty, you will be conflicting with yourself.  Access saves the current record under a lot of circumstances but this is not one of them.  Whenever you open a report or another form as a popup, etc, you MUST save the current record first.  I use:

DoCmd.RunCommand acCmdSaveRecord

some folks recommend:

If Me.Dirty = True Then
    Me.Dirty = False
End If

I think this is confusing and I have never run into the issue that makes the standard command a problem so if you do use this technique, add a commend to remind yourself (and others) that this is actually saving the current record rather than discarding it.

I don't generally split off memo fields but when I do, I use a subform to display the value.  If I wanted a large space and didn't have the screen real estate, I would put the subform on a tab so the user could view it or not and keep it from interfering with the rest of the data.  

Regarding the 1-1 relationship.  It is actually 1-0 because the related side is always optional.  That means that entering a record in the "parent" side of the relationship will not force a record to be created in the "child" side of the relationship.  This is the behavior you want especially if the child-side is sparse as yours is.  The impact this has is that when you create a query that joins the two tables, you must use a left join or you will only get parent records that have a child record.
0
 
LVL 24

Author Comment

by:Eirman
ID: 39794769
Thanks for the extensive answer Jim, It's really appreciated.
I'll really need to increase my access knowledge before I can make full sense of your reply.
Get/put the memo field data in code. really has me puzzled.

I've attached the entire database as it has no personal data.
It's just a list of words I'm familiar with / am aware of, but don't use.
Best viewed @ 1680x1050 then click on the ribbon button to hide it & nav bar.
Click on the X button for the popup (ADEPT is the first word with extra data)

The word EXISTENTIALISM has 6,400+ characters in the memo field and it causes no problem, so the problem treshold is somewhere beyond that.
GENERAL.accdb
GENERAL-TABLES.accdb
0
 
LVL 38

Expert Comment

by:PatHartman
ID: 39795062
I feel so invisible.

Did you make sure you were saving the record before opening the popup form?
Did you notice the third solution (subform) which is essentially a no-code option?
0
 
LVL 38

Expert Comment

by:PatHartman
ID: 39795225
Thanks for the explanation.  I felt like I was talking to my daughter:)
0
 
LVL 24

Author Comment

by:Eirman
ID: 39795895
Hi PatHartman,

Indeed, I saw your response and it was much appreciated.
I only saw it after my previous post (with the attachments).
I was working on the post for quite some time - it involved counting characters in fields and more which is why it took so long and that I seemed to ignore you.
It was at 17:42:17 local time which was the end of my official workday and I didn't check for missed posts during my typing period.

I'm going to try the extra save and dirty methods, before opening the popup.

I'll try the subform method as an experiment, but the popup suits much better .... and thanks for the 1-0 relationship info .... it was one of my original queries.

It's 02:15 here in Ireland and I'm off to bed.
My next post will be in about 14 hours from now.

Thanks
0
 
LVL 24

Author Comment

by:Eirman
ID: 39807492
I'll sort this question out over the weekend.
0
 
LVL 24

Author Comment

by:Eirman
ID: 39811758
Good morning, JDettman & PatHartman.

I entered this code in the button that opens the popup ....
DoCmd.RunCommand acCmdSaveRecord

If Me.Dirty = True Then
    Me.Dirty = False
End If


and I still get this error on a record with <4000 characters,
yet as I mentioned above, I have records with 6000+ characters.

Runtime Error 3188
Could Not Update; Currently locked by another session on this machine.


I don't want to use the subform method and as I haven't got a clue how to even start to ..
"Get/put the memo field data in code", I'm really stuck.

Is get/put easy to implement?
Is SQL Express a viable solution? .... What's the learning curve?
0
 
LVL 51

Assisted Solution

by:Gustav Brock
Gustav Brock earned 1160 total points
ID: 39831705
> "Get/put the memo field data in code", I'm really stuck.

Very simple. Have an unbound textbox to display and edit the memo.

To read the field, at the OnCurrent event of the form have:

    Me!txtMemo = Me![YourMemoTableField]

To write the field (after editing), at the AfterUpdate event of the textbox have:

    Me![YourMemoTableField] = Me!txtMemo

/gustav
0
 
LVL 24

Author Comment

by:Eirman
ID: 39837759
I will attend to this question on Friday.
0
 
LVL 24

Author Comment

by:Eirman
ID: 39845580
Hi Gustav,

While I think I understand the method you have described, I'm not quite sure how to implement it in practice. Would it be something like this ?  .....
I would have a hidden/unbound memo field on my popup form.
When I click on an EDIT button, the hidden field would become visible and the text in the bound memo field would be copied into the now visible field.
Having edited text in the the newly visible field, SAVE would copy from this into the bound memo field, save it and then hide the unbound memo field.

If the above is correct, do I need to clear the bound memo before copying text from the unbound field?
0
 
LVL 51

Expert Comment

by:Gustav Brock
ID: 39845632
You shouldn't need neither an Edit button, popup-form, a hidden textbox nor one bound to the memo field, just one unbound textbox.

/gustav
0
 
LVL 24

Author Comment

by:Eirman
ID: 39845646
On my database (attached earlier in the question) the bound memo field in question, is only visible on a popup form. It's an extra information field for about 5% of records.

Are you saying that I edit my text in the existing bound memo field and then copy the contents into the unbound field and then back again into the bound field?
0
 
LVL 51

Expert Comment

by:Gustav Brock
ID: 39845898
Then you would retrieve the memo when you open the popup and (if changed) save it when you close it.

/gustav
0
 
LVL 24

Author Comment

by:Eirman
ID: 39846001
Thanks gustav,

I'll do some experimenting and post back here in a couple of days.
0
 
LVL 24

Author Comment

by:Eirman
ID: 39861186
Whilst I understand the principal involved, I still haven't worked out how to practically apply it.
0
 
LVL 51

Accepted Solution

by:
Gustav Brock earned 1160 total points
ID: 39861267
Make your popup form and all its textboxes unbound.
Replace the code for the form with:
Option Compare Database
Option Explicit

Private Sub CommandCLOSE_Click()

    DoCmd.Close

End Sub

Private Sub CommandSAVE_Click()
    
    Forms("VOCAB")!EXTRA = Me!Text4
    Forms("VOCAB").Dirty = False

End Sub

Private Sub Form_Load()

    Me!Text2 = Forms("VOCAB")!THEWORD
    Me!Text4 = Forms("VOCAB")!EXTRA
    
End Sub

Private Sub Form_Unload(Cancel As Integer)

    Forms("VOCAB").Refresh

End Sub

Open in new window

/gustav
0
 
LVL 24

Author Comment

by:Eirman
ID: 39867819
Thanks gustav

I'll try it in a few days.
0
 
LVL 51

Expert Comment

by:Gustav Brock
ID: 39913543
So?

/gustav
0
 
LVL 24

Author Comment

by:Eirman
ID: 39917881
Apologies for neglecting EE and this question in particular gustav.
I'll sort it out very soon.

No need to reply
0
 
LVL 24

Author Closing Comment

by:Eirman
ID: 39928829
Thanks to everyone who contributed to this question and my apologies for not closing it sooner.

I've been too busy to even start Access for the past few weeks.  However, it's unfair to leave a question like this (where I got great help) in limbo.
I'm certain the solution is here and I'll implement it in a few weeks when I get the time.

.... Sure if I get stuck, I can always ask another question!
0

Featured Post

Office 365 Training for IT Pros

Learn how to provision tenants, synchronize on-premise Active Directory, implement Single Sign-On, customize Office deployment, and protect your organization with eDiscovery and DLP policies.  Only from Platform Scholar.

Question has a verified solution.

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

This article helps those who get the 0xc004d307 error when trying to rearm (reset the license) Office 2013 in a Virtual Desktop Infrastructure (VDI) and/or those trying to prep the master image for Microsoft Key Management (KMS) activation. (i.e.- C…
Ever wonder what it's like to get hit by ransomware? "Tom" gives you all the dirty details first-hand – and conveys the hard lessons his company learned in the aftermath.
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …
Visualize your data even better in Access queries. Given a date and a value, this lesson shows how to compare that value with the previous value, calculate the difference, and display a circle if the value is the same, an up triangle if it increased…

762 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