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.
Join the community to see this answer!
Join our exclusive community to see this answer & millions of others.
Unlock 2 Answers and 33 Comments.
Join the Community
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 2 Answers and 33 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros