We help IT Professionals succeed at work.

Gracefully handling 'Record Locked'  Errors

mlcktmguy
mlcktmguy asked
on
267 Views
Last Modified: 2017-03-20
Background:
We have an application that's been installed for 4 years in a client's office.  It's written and running in Access 2003 and the application is split, every user has there own copy of the front end.  All users connect to the same backend which is on the network.

Recently we seem to have an increase in the number of record lock errors.  The application has a general error handler that all modules call in the event of an error.  The error module shows the error to the user, logs the error in an error log table and then exits the application with 'Application.Quit acQuitNosave'.  We instruct all users to 'refresh', which gets the copies the clean version of the FE stored to their machine, before re-entering the application.

All of the bound forms are set up with 'No Locks' as the Locking option.  It is my understanding that Access handles the occasional lock clash even when this option is selected.  And that using 'Edited' Record' on this option actually locks far more than the edited record.

We also have procedures that write records to files.  They are all set up as:

Dim rs As ADODB.Recordset
Set rs = New ADODB.Recordset
rs.Open "tblStoreGlobals_Local", CurrentProject.Connection, adOpenKeyset, adLockOptimistic


I'd like to get a better flow for handling these errors.  Perhaps trapping them in the module that is actually getting the error rather than passing them to a common error routine that just exist the application.

Which event in a bound form would be the appropriate place to trap a record lock error?

How do the EE'rs handle this in their application.  Once I know where to trap them I thought about putting in a loop that would re-try transaction a specified number of times or for a specified time before presenting the user with a lock message allow them to choose between continuing to try or abandoning the transaction.

If any you have ideas about why the frequency of these would be increasing please let me know.  There has not been an increase in users or transaction processing.


Any ideas on why this might be occurring more frequently.
Comment
Watch Question

John TsioumprisSoftware & Systems Engineer
CERTIFIED EXPERT
Distinguished Expert 2019

Commented:
What is your BE ? if it is Access you should compact/repair from time to time...
Also if you had corruptions you should check if you have broken missing keys/indexes...
Jim Dettman (EE MVE)President / Owner
CERTIFIED EXPERT
Fellow
Most Valuable Expert 2017

Commented:
<<All of the bound forms are set up with 'No Locks' as the Locking option.  It is my understanding that Access handles the occasional lock clash even when this option is selected.>>

  Yes, but only to a point before it signals an error.

<< And that using 'Edited' Record' on this option actually locks far more than the edited record.>>

  Yes and no.   Edit record vs no locks controls when the lock is placed.   "No locks" is a misnomer; it means no Edited record lock, not that there is no locking.

  In both cases, you are more than likely locking a page rather than a record.

<<Which event in a bound form would be the appropriate place to trap a record lock error?>>

  On Error event

<<How do the EE'rs handle this in their application.  Once I know where to trap them I thought about putting in a loop that would re-try transaction a specified number of times or for a specified time before presenting the user with a lock message allow them to choose between continuing to try or abandoning the transaction.>>

 That, or you can modify the registry settings for JET/ACE.

 I generally use "no locks", no transactions on any Insert/delete/update queries if I can get away with it.   If I still have concurrency issues after that, then I pad out records in the table so they span more than half a page.

 This forces no more than one record on a page, and thus you get record level locking.

Jim.

Author

Commented:
Thanks for the quick responses.  

John: The back end is an Access MDB.  We run a compact/repair on the BE MDBe very day at 5:00am, long before any users are in the system.

Jim: I didn't expect the answer to be 'On Error'  I was thinking it would be something like in the 'Before Update' or 'After Update' event of a bound form.

I use MZTools to populate all of my error handling logic.  This is the way my typical routine is set up:

Private Sub Form_Activate()
'- - - - - - - - - - - - - - - - - - - - - - - - - - G E N E R A T E D  E R R O R  C O D E ----------------
                               If IsDeveloper Then
                               Else
                                 On Error GoTo Form_Activate_Error
                               End If
'- - - - - - - - - - - - - - - - - - - - - - - - - - G E N E R A T E D  E R R O R  C O D E ----------------

Me.Requery
'

'- - - - - - - - - - - - - - - - - - - - - - - - - - G E N E R A T E D  E R R O R  C O D E ----------------
                               On Error GoTo 0
                               Exit Sub
Form_Activate_Error:
                               sysErrorHandler Err.Number, Err.Description, "Form_Activate", "Form_frmEvents_Pre", "VBA Document"
'- - - - - - - - - - - - - - - - - - - - - - - - - - G E N E R A T E D  E R R O R  C O D E ----------------


End Sub
I have this logic in every event, sub and function in the application.  MZTools automatically populates the pertinent routine name based on where you invoke the error generation option.

With a setup like this I envisioned revising the logic continue and loop after the 'Form_Activate_Error:' label to intercept and recognize a record lock error and take appropriate action rather that handing directly to the system wide error handler.  That's why I asked which event throws the record lock error.  I didn't think it would be necessary to check/intercept the 'lock record' error in every event on the bound form.

Are you suggesting an alternate approach to the error handling I have now?  If so do you have some examples of how you have this set up?
Dale FyeOwner, Dev-Soln LLC
CERTIFIED EXPERT
Most Valuable Expert 2014
Top Expert 2010

Commented:
@micktmguy,

One way I've dealt with this is to prevent two users from editing a record at the same time.  Don't have the code handy but here is the general process I used.

Store the username, tablename and the PK from your table in another table (tblUserRecordLocks);  I used the form Dirty event to do this, but before I do it, I check to see whether there is already a record in the table tblUserRecordLocks for the table and PK I'm trying to edit.  If there is, then i display a message and undo any form changes.  If there isn't, then I write a record to the table, which will prevent someone else from making changes to the table while I'm working with it.  As I recall, I use the Form_Current and Form_Close events to remove any records from that table related to my username.  Also, when you open the application, Splash screen, I would remove any records related to the current user.

You will need to provide another way to remove records from this table (admin user) in the event that a users connection gets terminated or the application is closed abnormally, which would leave a "lock" on a particular record.

HTH
Dale
Jim Dettman (EE MVE)President / Owner
CERTIFIED EXPERT
Fellow
Most Valuable Expert 2017

Commented:
<<Jim: I didn't expect the answer to be 'On Error'  I was thinking it would be something like in the 'Before Update' or 'After Update' event of a bound form.>>

   With a bound form, any error generated as a result of internal form operations by Access flow through the OnError event of the form.

<< I didn't think it would be necessary to check/intercept the 'lock record' error in every event on the bound form.>>

 If your working in code with record sets, it would be in that event.  But if it's a bound form and Access doing the work, then it will pop in OnError.

 and with error handling, one thing I would add since your using MZ tools is the vba.ERL to your error handler.  This will return the line number of the error (MZ tools has a feature built-in to number the lines in a procedure or module).

 As for the problem, I would try the cheap way out; adjust the LockDelay (default is 100 ms) and LockRetry (default 20).  Do this with dbEngine.SetOption  

 Also, do a review of indexing.  Make sure the critical things are indexed, and non-critical things are not (updating an index takes time, so if your not using it often, then your better not to have it).

 Also on indexing, couple other points:

1. Don't index low cardinality fields.   That is fields with few values.   A yes / no is a good example.  There are only two possible values and a ISAM/B-Tree index will not be efficient.  You'd be better off letting it scan the table.

2. Compound indexes - If your not searching them in the order of the fields listed, don't use them.  Instead index each of the fields separately.  

Jim.
Jim Dettman (EE MVE)President / Owner
CERTIFIED EXPERT
Fellow
Most Valuable Expert 2017

Commented:
and if your going to go the "do your own locking" route as Dale suggested, you can use this:

https://www.experts-exchange.com/articles/5328/Resource-locking-in-your-applications.html

 and you'd want no locks on the forms.

Jim.

Author

Commented:
As usual, great information from the EE community.

Jim: As you suggested my first try will be to try changing the default parameters.  I've never done that before and I'm not getting the syntax right.

I'm using variations of DBEngine.SetOption LockDelay(20)  but getting compile errors on all of them.

Is there a way to display the values in the DBEngine options?  Once I get this working I want to be sure the revision worked.
Jim Dettman (EE MVE)President / Owner
CERTIFIED EXPERT
Fellow
Most Valuable Expert 2017

Commented:
Syntax would be:

 dbEngine.SetOption "LockDelay", 200
 dbEngine.SetOption "LockRetry", 40

  This would increase the time between retries to 200 ms, and increase the number of tries from 20 to 40.    Note that the more you increase these, the longer the pause will be that a user sees before they get an error if a lock conflict occurs.

Jim.

Author

Commented:
Thanks but I'm getting a type mismatch error when I try to execute either one of those statements..

Is there any way to show what the current values of these options are?
CERTIFIED EXPERT
Most Valuable Expert 2015
Distinguished Expert 2018
Commented:
Unlock this solution and get a sample of our free trial.
(No credit card required)
UNLOCK SOLUTION

Author

Commented:
Thanks for the suggestion Gustav, it looks pretty slick but I have some questions.

First, I noticed in the downloaded code that you are trapping err 3197 in 'SetEdit'.  My current error handler displays the error number and message when it encounters an error.  The error code displayed does not match the description displayed.  Here's what the user sees.  Am I wrong about this being a record locking error?

Lock Error
I typically use ADO instead of DAO.  TO implement your functions I will have to convert my file handling to DAO.  I will probably choose a few critical ones first.  I see how to implement your functions on an update transaction but ADO does not require an .Edit before an update.

What would the following insert look like in DAO?

'
Dim rsOut As ADODB.Recordset
Set rsOut = New ADODB.Recordset
rsOut.Open "tblEvents", CurrentProject.Connection, adOpenKeyset, adLockOptimistic
'
With rsOut
    .AddNew
        !EventID = passedEventID
        !Comment = wkComment
        !Amount1 = PassedAmount1
        !Amount2 = PassedAmount2
        !Amount3 = PassedAmount3
        !BatchNumber = passedBatchNumber
        '
        !DateAdded = wkDateAdded
        !UserAdded = wkUserAdded
    .Update
End With
'
rsOut.Close
Set rsOut = Nothing


Also your method doesn't address locked records being inserted or updated form bound forms.  Do you use the suggestions that Jim and Dale provided above.  I was trying to implement Jim's increase of the 'LockDelay and "LockRetry' to help in bound form lock issues.

Last:  I noticed a note that version 1.0.2 addresses getting in an endless loop if the edit doesn't ever happen but I don't see the safety valve in the code.  What am I missing?
'
' 2016-02-06. Gustav Brock, Cactus Data ApS, CPH.
'
Public Sub SetEdit(ByRef rs As DAO.Recordset)

    On Error GoTo Err_SetEdit

    ' Attempt to set rs into edit mode.
    Do While rs.EditMode <> dbEditInProgress
        rs.Edit
        If rs.EditMode = dbEditInProgress Then
            ' rs is ready for edit.
            Exit Do
        End If
    Loop

Exit_SetEdit:
    Exit Sub

Err_SetEdit:
    If DebugMode Then Debug.Print "    Edit", Timer, Err.Description
    If Err.Number = 3197 Then
        ' Concurrent edit.
        ' Continue in the loop.
        ' Will normally happen ONCE only for each call of SetEdit.
        Resume Next
    Else
        ' Other error, like deleted record.
        ' Pass error handling to the calling procedure.
        Resume Exit_SetEdit
    End If
CERTIFIED EXPERT
Most Valuable Expert 2015
Distinguished Expert 2018

Commented:
First, your error doesn't seem like an Access error. Such errors cannot be handled from within Access.

Second, The code is for DAO. For ADO similar code could work, but error numbers will most likely be different, and you will have to introduce a pseudo edit method to indicate that editing is now taking place.

I haven't considered implementing the code in bound forms. It could be fun, but lack of spare time currently doesn't allow for this.

As for the error loop, I haven't seen this being an issue, as the error code should be different (thus forcing an exit of the loop) but, of course, if the record in no way can be updated and if the error code should be the same, it might be. It could be managed with a time-out.

/gustav
Jim Dettman (EE MVE)President / Owner
CERTIFIED EXPERT
Fellow
Most Valuable Expert 2017

Commented:
<<Is there any way to show what the current values of these options are?>>

.GetOption method.

Jim.

Author

Commented:
Thanks for your interest in my problem.

Jim but could you please clarify the syntax of these statements?

I'm still getting 'Type Mismatch' errors when I try to execute these statements
 dbEngine.SetOption "LockDelay", 200
  dbEngine.SetOption "LockRetry", 40

Once I get the "LockDelay' and "LockRetry" to work, how persistent are they?  Do they have to be set before every Insert or Update or perhaps once they are set they are in effect until the application closes?

I can't get any variation I tried of '.GetOption method.' to compile.  How would that look to display the setting of "LockDelay"

Gustav:
I realize your methods only work with DAO and to use them I would have to change my ADO to DAO.  I would like to try your methods but I am unfamiliar with DAO so I was asking how my example code block would look if done using DAO instead of ADO.

What would the following insert look like in DAO?

 '
 Dim rsOut As ADODB.Recordset
 Set rsOut = New ADODB.Recordset
 rsOut.Open "tblEvents", CurrentProject.Connection, adOpenKeyset, adLockOptimistic
 '
 With rsOut
     .AddNew
         !EventID = passedEventID
         !Comment = wkComment
         !Amount1 = PassedAmount1
         !Amount2 = PassedAmount2
         !Amount3 = PassedAmount3
         !BatchNumber = passedBatchNumber
         '
         !DateAdded = wkDateAdded
         !UserAdded = wkUserAdded
     .Update
 End With
 '
 rsOut.Close
 Set rsOut = Nothing
CERTIFIED EXPERT
Most Valuable Expert 2015
Distinguished Expert 2018

Commented:
I can't tell. I haven't been working with ADO for ten years or so. I'm away most of the weekend, so I hardly can find time to play with it before next week or so.

That said, you shouldn't have an issue for adding records. Concurrent locks is a result of two users trying to edit the same record.

/gustav
President / Owner
CERTIFIED EXPERT
Fellow
Most Valuable Expert 2017
Commented:
Unlock this solution and get a sample of our free trial.
(No credit card required)
UNLOCK SOLUTION

Author

Commented:
Thanks Jim, those commands run.

I also took your advice on reviewing and eliminating some indexes on the DB files.  

Based on your << 1. Don't index low cardinality fields. >>, I removed some indexes that only had two values.  

I also have indexes on some fields with just a few values like 'PayStatus', where 1=Unpaid,2=PartPaid and 3=FullPaid.  Since there are only three values would this be a candidate for deletion?  In the application, I do pull and segregate records based on the payment status so I wanted to check before I remove it.
Jim Dettman (EE MVE)President / Owner
CERTIFIED EXPERT
Fellow
Most Valuable Expert 2017

Commented:
<<I also have indexes on some fields with just a few values like 'PayStatus', where 1=Unpaid,2=PartPaid and 3=FullPaid. >>

 No clear answer, just "depends".    Indexing in JET/ACE is B-Tree based, so it works best when you have a lot of discrete values.

 So on a table with a few thousand records, a table scan might be faster.   Hundreds of thousands, probably not.    But if you have other criteria that you always use in conjunction with that (say a date range), then it still might be faster with a table scan even with hundred's of thousands as the query parser would select the records based on date first using the index, then scan the results for checking the paystatus.

 It actually takes a lot of work to do indexing well as there are so many factors involved (number of records, pattern of usage, engine settings, etc), and it's always a moving target to some extent (i.e. data sets grow in size over time).

  What I would do is let your existing changes settle, then get rid of the index.   Leave it like that for a bit and see if things get better or worse.  Users love to complain<g>, so you'll find out pretty quick if things are "slow".

  You can by the way dig into the numbers.   An ISAMStats call will give you numbers on pages and locks, and JET SHOWPLAN will let you see what the queries are doing in terms of the indexes their using.   But there are very few people that go to that level of detail.

Jim.

Author

Commented:
Gustav:
<<That said, you shouldn't have an issue for adding records. Concurrent locks is a result of two users trying to edit the same record.>>

It is my understanding adding a new record to a table locks the entire table briefly.  My error is 'Cannot Update Currently Locked'.  Couldn't that occur because another user is adding a record to the table while the other is trying to edit a single record in the table.  Or, two users are adding to a table at the same time?

Jim, I've been trying to get more up to speed in understanding the record locking behavior of Access.  I've been working with Access a long time and never run into these issues.  As a read more I stumbled across a setting in the 'Options' area of MS Access, even back in Access 2003.  These are the options:  Are the arrowed values the same ones that are changed with the 'SetOp0tion' commands you provided?  I found the same settings available is my Access 2013options as well.

Record Lock Options
John TsioumprisSoftware & Systems Engineer
CERTIFIED EXPERT
Distinguished Expert 2019

Commented:
I am getting the impression that something simple is missing..i don't know what but after all these talk and tricks i am thinking that something trivial is the culprit and not an exotic option that somehow changed...something like corruption or a null value or a change on client's configuration.

Author

Commented:
Thanks again to all for your input and ideas.  I tracked the locking issues down to inserts to the tables.  They are standard inserts, right now they are ADO but I can easily change them to DAO to implement Gustav's Concurrency Functions

Gustav:  I downloaded your Concurrency test and was able to run it successfully to handle the .Edit lock clashes.  Since I've now identified that my issue is on inserts I set up an insert test in your module but still called 'SetEdit' which did not trap locking errors on the insert.  I got a 3260 when locks were found.

This is the code I used to insert a record, calling my 'SetAddNew' function, which currently is exactly the same as 'SetEdit'.

        Do
            ' Count the attempts to update in this loop.
            Attempts = Attempts + 1
            ' Attempt edit and update until success.
            SetAddNew rs
                looper = looper + 1
                rs.AddNew
                    rs![EventID] = looper
                    rs![BRT] = looper + 10
                    rs![PropertyID] = looper + 20
                    rs![TaxRecID] = looper + 30
                    rs![PayPlanID] = looper + 40
                    rs![PaymentID] = looper + 50
        Loop Until GetUpdate(rs)

I was going to try and set up a 'SetAddNew' function to handled .AddNew situation but didn't know what to change.  There is no corresponding rs.AddNewMode to rs.EditMode and no corresponding 'dbAddNewInProgress' to 'dbEditInProgress'.  Do you what I would need to modify in the 'SetAddNew' process below to trap errors on an 'AddNew'?


Public Sub SetAddNew(ByRef rs As DAO.Recordset)

    On Error GoTo Err_SetAddNew

    ' Attempt to set rs into edit mode.
    Do While rs.EditMode <> dbEditInProgress
        rs.Edit
        If rs.EditMode = dbEditInProgress Then
            ' rs is ready for edit.
            Exit Do
        End If
    Loop

Exit_SetAddNew:
    Exit Sub

Err_SetAddNew:
    If DebugMode Then Debug.Print "CTEST    SetAddNew", Timer, Err.Description
    writeLog "CTEST    SetAddNew " & Err.Description & "Error Num: " & Trim(Str(Err.Number))
    If Err.Number = 3197 Or Err.Number = 3260 Then
        ' Concurrent edit.
        ' Continue in the loop.
        ' Will normally happen ONCE only for each call of SetAddNew.
        Resume Next
    Else
        ' Other error, like deleted record.
        ' Pass error handling to the calling procedure.
        Resume Exit_SetAddNew
    End If

End Sub
Jim Dettman (EE MVE)President / Owner
CERTIFIED EXPERT
Fellow
Most Valuable Expert 2017

Commented:
<<Are the arrowed values the same ones that are changed with the 'SetOp0tion' commands you provided? >>

  No, those are for ODBC data sources.

  Let's talk about locking in a JET/ACE DB.   Records are stored on pages and a page may contain one or more records, but records are not allowed to span pages.  A record must fit entirely on a single page.

  All fields in the record reside here, *except* memo and OLE fields if they are > 30 bytes (this was less in the past).   If > 30 bytes, the field is stored in a LVP (Long value page).   Like regular data pages, there can be fields from many records.

 Locks are placed at the page level by default.   Record level locking was added later, but Microsoft has never documented how record level locking actually was implemented.  Also, certain operations (DML SQL Statements or index operations are always done at page level).  Page level locks can be escalated to table level locks with a registry setting.

 My advice; forget that record level locking exists.   It was an add on, doesn't work well, and you can never be sure your getting it.

  The "edited record" lock setting  (pessimistic locking) vs "no locks" (optimistic locking) controls when a lock is placed.    For edited record, a lock is placed as soon as the user starts to edit the record and is not released until the save is completed.   If they start an edit and leave for lunch, the lock is held all that time.

  If "no locks", the lock is placed at the start of the save (not edit) and released after the save is complete.   So it's very brief.  Downside; if two users edit the same record, one is going to loose their changes or the other will have their changes over written (you get a dialog from Access asking you what you want to do - you can cancel the save as well).

 When a lock is taken, you get one for the "fixed" portion of the record, and if memo or OLE fields >30 bytes, a lock for *all* of the LVP's involved as well.

 In general then, if not using record level locking, you'd want "no locks" so locking is as brief as possible. However if you get a lot of concurrency issues, you'd be forced to switch to "Edited record".   If you used record level locking, you'd want to use "Edited record".     But because it was not implemented well, it doesn't do what it should.

 The other technique here is to use "no locks" along with doing your own locking scheme.  This should be a last resort as you really can't control things well.

 In regards to adding new records, the table is not locked and you will not get a lock conflict when doing so.   New records are added to the last page of a table if possible (might be > remaining page space),   If no room or the page is locked, it will add a new page.  


If your seeing a lot of lock conflicts, I would:
1. Raise the lock delay and lock retry limits.
2. Consider switching to no locks if possible.
3. If not possible, pad the records so that only one record will fit on a page (>2048 bytes in length) and use "edited record".  This gives you record level locking for the "fixed" portion of the record on a table by table basis.  If no memo or OLE fields, you'll be good.  If you do have them, then your in a bit of a spot possibly still.  Some go to the length of updating those fields separate from everything else, which helps (your not holding a lock very long.
4. use "no locks" along with doing your own locking scheme

Jim.
CERTIFIED EXPERT
Most Valuable Expert 2015
Distinguished Expert 2018

Commented:
 There is no corresponding rs.AddNewMode to rs.EditMode

Exactly. I would know of no other method than simulating it by setting a variable.
However - as Jim mentions - an insert should normally be so fast that it cannot lock anything.

Sadly, I'm busy with real work so I don't have the time to play with it right now; it's on my to-do list.

/gustav

Author

Commented:
Thanks once more for the information.  Your help in my situation is much appreciated.  

Jim: In general record locking has not been an issue in this application (over four years in production with 40 -50 concurrent users) or any of the other dozens of Access applications we’ve created and installed for other clients.

For some unknown reason, last Thursday 3/16/17, the record lock message I show above became epidemic. Many, many user were getting the error and being forced out of the system.  I had all user exit the system and ‘refresh’ their front end copies of the DB while a compact/repair was done on both date MDB’s.  Everything has been OK since we came back up.  That was the incentive to learn all I could about record locking even though I had never had an issue before.  I’m trying to get ahead of the situation in case it does become a problem.   Days like last Thursday literally keep me awake at night.

Based on your advice last Thursday I did review all the indexes on the tables and removed many.

I keep an error log table of all unexpected errors in the application.  We don’t get a lock issue every work day but they do seem to occur most days.  On the days they occur there is usually only 1 or 2.  The most in a day, prior to last Thursday was 7 and that only occurred once.  Last Thursday there were 126 in a couple of hours.

The locking issues almost all occur on two tables in the system.  Both of the problem tables ‘tblEvents’ with 1.5 million records and tblComments’ with 1.8 million records, have Memo fields defined.  tblComments’  has a couple numeric fields and the Memo field, which holds comments.  Sounds like the Memo fields can be trouble in the record locking schemes.  These two tables are by far the biggest tables in the DB.  I have them segregated from the rest of the application data in their own MDB.

There is no editing of ‘tblEvents’, only inserts, so these lock errors can only have come on an insert.  That’s why I was trying to create an .addnew routine similar to Gustav’s ‘edit’ routine.  The events are inserted and tracked behind the scenes as an audit trail.  There are screens that view events and only one form where the users can manually enter an event but I verified that every form in the application is set to ‘No Locks’.

Jim I am definitely going to revise the locking parameters.  Prior to doing so, I wanted to be sure of the default settings so I could adjust them gradually.  I looked for the key you mentioned in the registry “HKLM\Software\Microsoft\Office\12.0\Access Connectivity Engine\Engines\ACE” but didn’t have a matching key.  That’s when I noticed the settings available in the options of MS Access.  I saw the default number of tries was 2 and you had mentioned the default as 20.  I’d still feel better about altering the settings if I could see the defaults.  If it really is 2 adjusting it to 22 would be a big jump.  If it really is 20, adjusting it to 4 would likely cause more issues.  If there are any other methods that would allow me to see these values prior to changing them, please let me know.

I’m setting up values for lockretry and lockdelay in my backend ‘tblParameters’.  That way I can tweak them without having to change the application.  I created a routine that accesses the values in parmfile and uses those values in the pertinent dbSetOption command.

Gutav:  If you can think of a way to handle the ‘addnew’ it would be great to have in my tool kit in case this become problematic again.

A day like last Thursday may never happen again but I'd like to have some options if it does.  I don't remember the more than thousand days this application ran trouble free but days like last Thursday stay with me for a very long time.
Jim Dettman (EE MVE)President / Owner
CERTIFIED EXPERT
Fellow
Most Valuable Expert 2017

Commented:
<<There is no editing of ‘tblEvents’, only inserts, so these lock errors can only have come on an insert.  That’s why I was trying to create an .addnew routine similar to Gustav’s ‘edit’ routine. >>

  Watch the indexing on these....there should be none except the PK given the table sizes and usage.

Jim.

Author

Commented:
Jim, thanks.  After your suggestion on Thursday I stripped most of the indexes off both tables.  There were way too many indexes on tblEvents.  MS Access automatically adds indexes on numeric fields when you design the table and I had not checked the indexes.

Other than the Primary key, I only left indexes on two fields that are used as foreign keys.  Property ID is used to link records in both of these files with Main forms so I left the PropertyID index in place.
Jim Dettman (EE MVE)President / Owner
CERTIFIED EXPERT
Fellow
Most Valuable Expert 2017

Commented:
Do you enforce RI  on the relationship? If so be aware that that JET will create a Hidden index on the many side of the relationship. If you're indexing that as well you've got a duplicate index.

Jim

Author

Commented:
Thanks for the heads up.  I do not use Access to reinforce RI.  I have my own routine that checks RI prior to a delete.

Author

Commented:
Jim, I am reviewing and paying close attention to the indexes on my tables.  In most tables I have an autonumber field called 'ID' that is the primary key.

On just about every table including big ones, 'tblEvent' and tblComment', I see the same two indexes.
1.  Index Name 'PrimaryKey' on the field 'ID' with Attributes Primary=Yes, Unique=Yes, IgnoreNulls=No.
2. I also see another Index called 'ID' on the field ID with Attributes Primary=No, Uni1que=YNo, IgnoreNulls=No.

Is the index in number 2 'ID' adding anything not accomplished with the index in 1 'PrimaryKey'?

As I examine these indexes more closely it seems that index 'ID' is not needed at all.  I am tempted to delete it but wanted to check with you first.
CERTIFIED EXPERT
Most Valuable Expert 2015
Distinguished Expert 2018

Commented:
You can safely delete any index that you don't need. You can always add them back.

Also note, that under settings, Access has an entry with a list of field names that - if created - will be assigned an index automatically. That may be where your ID index come from as - by default - ID is one of these names.

/gustav
Jim Dettman (EE MVE)President / Owner
CERTIFIED EXPERT
Fellow
Most Valuable Expert 2017

Commented:
<<You can safely delete any index that you don't need. You can always add them back.>>

gustav covered it well, but there is one gotcha where deleting an index can raise an error, and that is if you open a table as a table and do a seek on the index.  When you do this, you actually have to name the index.  Few apps do this however.

 Outside of that, no harm in deleting an index except for performance.   That ID though is a dup and just extra overhead every time you stick a record in.

Jim.

Author

Commented:
Great thanks to Jim and Gustav.  I chose two post to assign the points but all the posts provided information on getting towards an understanding and handling of the issue.

If I have more questions on this area I will open another questions so I can award more points.

Gustav: If you should ever have time and inclination to expand your concurrency logic to include 'AddNew' please let me know.
CERTIFIED EXPERT
Most Valuable Expert 2015
Distinguished Expert 2018

Commented:
Yes I will. Thanks!

/gustav
Unlock the solution to this question.
Thanks for using Experts Exchange.

Please provide your email to receive a sample view!

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.