Weird error messages in Access front-ends when updating DB table on timer event
Posted on 2014-04-30
I have since a couple of days 2 Access front-ends which run the the whole day waiting for users to trigger their processing. The trigger is through an field value in separate records (one per app) in a table in the Access backend. So the 2 apps have each a form timer event every 15 seconds which does the following:
- resets the timer inerval to 0
- checks if the processing flag field is set to "Yes". If yes, it does the corresponding processing and resets the processing flag to "No" and goes to the next step, if not it goes to the next step
- stores, in a dedicated row in one table, a heartbeat (current date/time) so that other apps know that is still alive
- resets the timer to 15 secs
- exits the timer routine
So these 2 apps are frequently accessing the 2 tables, but each of them access a different record in these 2 tables, their "own" record, which bears the name of the app in one field which they select upon.
Since I put these 2 apps in production, they run, but they get lots of errors, always the same errors, but kind of randomly distributed:
- "The database has been placed in a state by user 'Admin' on machine 'VDAT001CH' that prevents it from being opened or locked", in the routine that stores the heartbeat
- "Could not use <db name>; file already in use", in the routine that stores the heartbeat
- "Error: Too many active users. ", in a routine that reads the heartbeat record of the other app to check if it is running
- "Error: Object required", in the Form_Timer routine
As I have record-level locking set and of course shared backend enabled, I just don't understand why these 2 apps get bogged down by each other. Because I resume and exit all the corresponding routines when errors happen, the apps continue to run, but I find a large number of errors in the apps logs (yet another table where all apps log what they do, including trapped errors).
The apps are not sharing any routines, they have their own copy of a series of generic modules and their own specific module and behind-form code. The only thing they share are these 2 tables, and that sems to go badly...
I open all table using CurrentDB.OpenRecordSet(<SQL statement>, dbOpenDynaSet).
Can someone tell me why there are so many collisions between these 2 apps on these 2 tables ?