Solved

Records locked that are not being edited

Posted on 2013-12-05
17
456 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
ID: 39700243
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
ID: 39700605
along with that, if your table has a memo field, then you never get row level locking.

Jim.
0
 

Author Comment

by:thandel
ID: 39701149
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
The Eight Noble Truths of Backup and Recovery

How can IT departments tackle the challenges of a Big Data world? This white paper provides a roadmap to success and helps companies ensure that all their data is safe and secure, no matter if it resides on-premise with physical or virtual machines or in the cloud.

 
LVL 57
ID: 39701191
<<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
ID: 39701383
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
ID: 39701650
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
ID: 39701652
Oh and one other option; convert to SQL, which is probably the best choice.

Jim.
0
 

Author Comment

by:thandel
ID: 39702278
Please clarify... convert to SQL?  From VBA?
0
 
LVL 35

Expert Comment

by:PatHartman
ID: 39702746
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
ID: 39703026
@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
ID: 39706659
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
ID: 39706734
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 35

Accepted Solution

by:
PatHartman earned 167 total points
ID: 39706811
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
ID: 39708530
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
ID: 39708595
<<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
ID: 39716777
Convert to SQL... that would require a server correct?
0
 
LVL 35

Expert Comment

by:PatHartman
ID: 39716858
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

Three Reasons Why Backup is Strategic

Backup is strategic to your business because your data is strategic to your business. Without backup, your business will fail. This white paper explains why it is vital for you to design and immediately execute a backup strategy to protect 100 percent of your data.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
type of query 11 42
Search for text in a .txt file 14 44
Create macro from runcode 30 26
Cascading Combo boxes between 2 sub navigation forms 1 12
I originally created this report in Crystal Reports 2008 where there is an option to underlay sections. I initially came across the problem in Access Reports where I was unable to run my border lines down through the entire page as I was using the P…
Describes a method of obtaining an object variable to an already running instance of Microsoft Access so that it can be controlled via automation.
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
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 …

770 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