Records locked that are not being edited

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?

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

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?

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.

Oh and one other option; convert to SQL, which is probably the best choice.

Jim.

Please clarify... convert to SQL?  From VBA?

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.

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

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.

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.

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.

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

Convert to SQL... that would require a server correct?

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.