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.