Solved

Records locked that are not being edited

Posted on 2013-12-05
17
444 Views
Last Modified: 2013-12-13
Running a MS Access 2003 Multi-user DB... all working well.

We have one computer that when its editing a record it is locked about 6 sequential records near the editted record.  All Access settings are the same for all computers.

Lock edited record
Use record level locking (have tried to disable this option)
Open DB Shared

Any thoughts or things I can try to prevent this one computer from hogging records that are not being edited would greatly be appreciated.

Thank you.
0
Comment
Question by:thandel
  • 7
  • 6
  • 3
  • +1
17 Comments
 
LVL 75

Assisted Solution

by:DatabaseMX (Joe Anderson - Access MVP)
DatabaseMX (Joe Anderson - Access MVP) earned 167 total points
Comment Utility
Unfortunately Row Level lock is somewhat of a myth, misleading at best - as described in the KB below. You can use the 'ADO trick' to implement row level locking. I have done this.

Jet 4.0 Row-Level Locking Is **Not** Available with DAO 3.60
http://support.microsoft.com/kb/306435
0
 
LVL 57

Assisted Solution

by:Jim Dettman (Microsoft MVP/ EE MVE)
Jim Dettman (Microsoft MVP/ EE MVE) earned 166 total points
Comment Utility
along with that, if your table has a memo field, then you never get row level locking.

Jim.
0
 

Author Comment

by:thandel
Comment Utility
Really Jim, funny you mention that the table does have a memo field, 2 actually.  So you are saying there is not way to implement row level locking?
0
 
LVL 57

Expert Comment

by:Jim Dettman (Microsoft MVP/ EE MVE)
Comment Utility
<<Really Jim, funny you mention that the table does have a memo field, 2 actually.  So you are saying there is not way to implement row level locking? >>

  That is correct.  LVP's (Long Value Pages) where memo fields are stored are always page locked regardless of settings.  Index operations and all DML operations are also always page locked, but those make sense.

  So that means you can tie up more then one record even if your using record level locking.

 Record level locking was an add-on to JET and it wasn't done well.

 
Jim.
0
 

Author Comment

by:thandel
Comment Utility
OK thanks, any suggestions as a work around (besides removing the memo field?  Change to a text box (although 255 might be too short) or some how save data after making a memo change to perhaps release?
0
 
LVL 57

Expert Comment

by:Jim Dettman (Microsoft MVP/ EE MVE)
Comment Utility
Well you can use no locks (which means no EDIT locks, not that there are no locks), which gives you opportunistic locking rather then pessimistic.

The difference is when the lock is placed.  With no locks, the lock is only placed when the record is saved.   With pessimistic, the lock is placed when the record starts to be edited and not released until the update is done or a rollback.

 While that gets rid of most of the concurrency issues, you are then stuck with users posssibly overwriting each others changes, which is why you want edited locks along with record level locking in the first place, so this is really not a great choice.

You can use an unbound control and have a seperate table for the memo field(s) alone and update after the main table record is updated.   Of course that also means you need to populate that control as well.

and of course the last option is as you suggested; don't use memo fields.  That's typically not an great option though because of the 255 limit, which is why you used memo fields in the first place<g>.

But a possible work around there is to use multiple records.  Many apps allow for notes to be kept and it's something like this:

01/01/13 - Contacted customer.  Left message.
01/04/13 - Still trying to contact customer.  Left message.
01/06/13 - Reached customer, informed of options available.

so if the notes are short, you can get away with multiple records.   If the notes are more lengthy, you can either:

1. Use multiple text fields and unpack/pack to a text control in chunks of 255 in the main record.   need to be carefull with this though as it's easy to get a "record too long" error (record in JET cannot span pages).   two or three fields at most is what you could combine.

2. do a notes setup like this:

tblNotes
NoteID - Autonumber - PK
EnteredOn - D/T

tblNoteDetail
NoteID - Long - PKa - Foreign Key to tblNotes
SequenceNo - Long - PKb
Note - Text  

Here notes are grouped and you can have as many as you need and each can be 64,000 characters (limit of a text control).

You can also do a hybrid type thing where you combine the two methods and improve performance a bit by doing:

tblNoteDetail
NoteID - Long - PKa - Foreign Key to tblNotes
SequenceNo - Long - PKb
NotePartA - Text  
NotePartB - Text  
NotePartC - Text  
NotePartD - Text  
NotePartE - Text  
NotePartF - Text  
NotePartG - Text  

 Taking parts a-g and combining them into a single chunk.

 Which method you use depends on the app and what type of notes you might have.

Jim.
0
 
LVL 57

Expert Comment

by:Jim Dettman (Microsoft MVP/ EE MVE)
Comment Utility
Oh and one other option; convert to SQL, which is probably the best choice.

Jim.
0
 

Author Comment

by:thandel
Comment Utility
Please clarify... convert to SQL?  From VBA?
0
What Should I Do With This Threat Intelligence?

Are you wondering if you actually need threat intelligence? The answer is yes. We explain the basics for creating useful threat intelligence.

 
LVL 34

Expert Comment

by:PatHartman
Comment Utility
Convert to SQL server means your tables.  Instead of the tables being stored in an Access database, they are stored in a SQL server database that Access links to.
@JDetterman - you are then stuck with users possibly overwriting each others changes - that isn't how it works.  Optimistic locking (the no locks setting) locks the record only for the time it is being updated.  If you attempt to save changes to a record that someone else changed after you read it, Access gives you a warning message with three choices - overlay the other user's changes, discard your changes, save your changes to the clipboard.  Also, if you are using Jet/ACE and your forms have record selectors, Access gives you visual clues.  For example, you'll see a circle with a line through it in the record selector if another user is editing the record you are viewing.

Another way to solve the problem of the memo columns is to move them to a second table.  You can create a 1-1 relationship which will mimic what you currently have or expand to a 1-many and allow multiple comments per record.  The query used for the form's RecordSource would join to the memo table if you go with 1-1.  For the 1-many relationship, you would need a subform for the notes.
0
 
LVL 57

Expert Comment

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

<<@JDetterman - you are then stuck with users possibly overwriting each others changes - that isn't how it works.  Optimistic locking (the no locks setting) locks the record only for the time it is being updated.  If you attempt to save changes to a record that someone else changed after you read it, Access gives you a warning message with three choices - overlay the other user's changes, discard your changes, save your changes to the clipboard.>>

 Note that when I said "you are then stuck with users posssibly overwriting each others changes",  that's what I was talking about, but it probably wasn't clear.  

  Many users find the dialog confusing and unless trained, will often over write thinking they don't want to loose their changes.  If they are trained, well then they loose their changes, which is not great either.

<< Also, if you are using Jet/ACE and your forms have record selectors, Access gives you visual clues.  For example, you'll see a circle with a line through it in the record selector if another user is editing the record you are viewing.>>

 With no locks, that is not correct.  There's no lock to tell JET another user is working on the record.   Each user will get a pencil icon when they start to edit the record, but neither will have any clue another is working on the record.

Jim.
0
 

Author Comment

by:thandel
Comment Utility
Thanks for the notes... what I don't understand is that we have had this format for years... not sure why all of a sudden this is an issue with with our DB.
0
 
LVL 57

Expert Comment

by:Jim Dettman (Microsoft MVP/ EE MVE)
Comment Utility
A number of things can effect it;  how the app is used, how the records land on the page, size of the notes, etc.

Jim.
0
 
LVL 34

Accepted Solution

by:
PatHartman earned 167 total points
Comment Utility
You may also need to train your users to not leave forms open with edits in process.  You may have a new user who is causing the entire problem by starting to change something and then leaving the form open unfinished while going off to do something else.  Or, it just could be that you've reached critical mass with the number of concurrent users.

SQL Server does do real record locking so you won't run into finding adjacent records locked so the clashes will be minimized.  In Jet/ACE all records are added to the end of the physical file and so it is more likely that adds will end up with conflicts than changes since everyone is trying to add into the same block (that's the mainframe term, I think the PC term might be sector).

You can actually segregate the memo fairly easily.  Before you start, don't forget to make several backups.  You never know when you'll need them.  Rename the existing table.  Create a new table to hold the memo.  Copy all the data.  Then delete the memo column.  The last step is to create a new query that joins the renamed table with the new memo table so you end up with a query that looks just like the original table.  Save the new query with the name of the original table.  Select Queries and Tables are interchangeable for most things in Access and in this case, it saves you a substantial amount of work.  If the test solves the problem, you can leave the solution in place or "do it right" by actually renaming everything and recreating all your queries.
0
 

Author Comment

by:thandel
Comment Utility
Thanks for the suggestions I'll give them a try....

"You may also need to train your users to not leave forms open"

I actually have a timer event that will close the form after 5 minutes of inactivity.
0
 
LVL 57

Expert Comment

by:Jim Dettman (Microsoft MVP/ EE MVE)
Comment Utility
<<Create a new table to hold the memo.  Copy all the data.  Then delete the memo column.  The last step is to create a new query that joins the renamed table with the new memo table so you end up with a query that looks just like the original table.  Save the new query with the name of the original table.  Select Queries and Tables are interchangeable for most things in Access and in this case, it saves you a substantial amount of work.  >>

 That doesn't work as it puts you right back in the same boat.  Memo and OLE fields are stored seperate from the main "fixed" part of the record on long value pages if their > 30 bytes and LVP's are always locked at page level.

 So as soon as that happens, you've got a problem whether it's on a base table or through a query.

 The only way to use memo fields and get record level locking is to store them in a seperate table, and then do the CRUD operations yourself (unbound approach) so they don't lock when the rest of the record locks.

 I think the best solution is a conversion to SQL.  Depending on the number of tables involved, it would probably be the least amount of work and you pick up so many benefits as well (like rollback/forward, on-line backups, stability, etc).

Jim.
0
 

Author Comment

by:thandel
Comment Utility
Convert to SQL... that would require a server correct?
0
 
LVL 34

Expert Comment

by:PatHartman
Comment Utility
Depends on how much horsepower you need.  You can run SQL Server from your own PC and expose the database for use across your LAN.  I wouldn't recommend it.  SQL Server should be installed on a PC/Server that is not used as a workstation.
0

Featured Post

Find Ransomware Secrets With All-Source Analysis

Ransomware has become a major concern for organizations; its prevalence has grown due to past successes achieved by threat actors. While each ransomware variant is different, we’ve seen some common tactics and trends used among the authors of the malware.

Join & Write a Comment

It took me quite some time to sort out all the different properties of combo and list boxes available from Visual Basic at run-time. Not that the documentation is lacking: the help pages are quite thorough and well written. The problem was rather wh…
Today's users almost expect this to happen in all search boxes. After all, if their favourite search engine juggles with tens of thousand keywords while they type, and suggests matching phrases on the fly, why shouldn't they expect the same from you…
Basics of query design. Shows you how to construct a simple query by adding tables, perform joins, defining output columns, perform sorting, and apply criteria.
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

743 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

17 Experts available now in Live!

Get 1:1 Help Now