troubleshooting Question

Gracefully handling 'Record Locked' Errors

Avatar of mlcktmguy
mlcktmguyFlag for United States of America asked on
Microsoft Access
33 Comments2 Solutions285 ViewsLast Modified:
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.
Log in to continue reading
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform for $9.99/mo
View membership options
Unlock 2 Answers and 33 Comments.
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
See how we're fighting big data
The Value of Experts Exchange in My Daily IT Life

Experts Exchange (EE) has become my company's go-to resource to get answers. I've used EE to make decisions, solve problems and even save customers. OutagesIO has been a challenging project and... Keep reading >>


Owner of Outages.IO
Phoenix, Arizona, United States
Member Since 2016
Join a full scale community that combines the best parts of other tools into one platform.
Unlock 2 Answers and 33 Comments.
View membership options
“All of life is about relationships, and EE has made a virtual community a real community. It lifts everyone's boat.”
William Peck

Member since 2004