Solved

Creating Table For Memo Field

Posted on 2014-01-20
28
1,120 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
  • 13
  • 5
  • 3
  • +1
28 Comments
 
LVL 57

Expert Comment

by:Jim Dettman (Microsoft MVP/ EE MVE)
Comment Utility
<<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 57

Expert Comment

by:Jim Dettman (Microsoft MVP/ EE MVE)
Comment Utility
You other option would be to convert the backend to SQL Server, which does locking differently.

Jim.
0
 
LVL 23

Author Comment

by:Eirman
Comment Utility
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
 
LVL 57

Assisted Solution

by:Jim Dettman (Microsoft MVP/ EE MVE)
Jim Dettman (Microsoft MVP/ EE MVE) earned 125 total points
Comment Utility
<<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 34

Assisted Solution

by:PatHartman
PatHartman earned 80 total points
Comment Utility
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 23

Author Comment

by:Eirman
Comment Utility
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 34

Expert Comment

by:PatHartman
Comment Utility
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 34

Expert Comment

by:PatHartman
Comment Utility
Thanks for the explanation.  I felt like I was talking to my daughter:)
0
 
LVL 23

Author Comment

by:Eirman
Comment Utility
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 23

Author Comment

by:Eirman
Comment Utility
I'll sort this question out over the weekend.
0
 
LVL 23

Author Comment

by:Eirman
Comment Utility
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 49

Assisted Solution

by:Gustav Brock
Gustav Brock earned 290 total points
Comment Utility
> "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
Better Security Awareness With Threat Intelligence

See how one of the leading financial services organizations uses Recorded Future as part of a holistic threat intelligence program to promote security awareness and proactively and efficiently identify threats.

 
LVL 23

Author Comment

by:Eirman
Comment Utility
I will attend to this question on Friday.
0
 
LVL 23

Author Comment

by:Eirman
Comment Utility
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 49

Expert Comment

by:Gustav Brock
Comment Utility
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 23

Author Comment

by:Eirman
Comment Utility
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 49

Expert Comment

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

/gustav
0
 
LVL 23

Author Comment

by:Eirman
Comment Utility
Thanks gustav,

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

Author Comment

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

Accepted Solution

by:
Gustav Brock earned 290 total points
Comment Utility
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 23

Author Comment

by:Eirman
Comment Utility
Thanks gustav

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

Expert Comment

by:Gustav Brock
Comment Utility
So?

/gustav
0
 
LVL 23

Author Comment

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

No need to reply
0
 
LVL 23

Author Closing Comment

by:Eirman
Comment Utility
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

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

This article will guide you to convert a grid from a picture into Excel format using Microsoft OneNote and no other 3rd party application.
My experience with Windows 10 over a one year period and suggestions for smooth operation
In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…

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

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now