Releasing Ms Access Lock File and Database

When you have an Access Application on the network used by multiple people, you often run into issues where the database is locked up. Usually there is this .laccdb file (.ldb is older version of access) that seems to never go away. This prevents you from doing clean backups, updating the structure of tables, etc. The only resort is to go around asking everyone to shut down their Access Application before you can get this done - and usually that is quite an impossible task. So I have a few suggetions to accomplish this rather easily.

There are two sets of issues involved here. One is where the users are simply connected 7×24 even though they are not actively using the application. The 2nd issue is that you want to take this database application offline in the middle of the day while the users are still connected and prevent new users from getting on the system. So there are two parts to the solution as described below.

The 1st is to force users to quit the application everyday at the end of the workday. This is accomplished by a simple timer event on the main form. On the main form set the Timer Interval property to 300000, which is 5 minutes. On the On Timer Event Procedure, paste the following code.

Private SUB Form_Timer()
    ' Will quit the application if Open during 12.00 am and 12.07 am
    ' This will facilitate clean backups if required
    ON ERROR RESUME NEXT
    IF TIMER > 0 AND TIMER < 420 THEN
        Application.Quit
    END IF
 
    quit_val = DLookup("[quit_sw]", "tbl_quit")
    IF quit_val = True THEN
        Application.Quit
    END IF
END SUB

And on the forms On Close Procedure write the following code. This code ensures that if the user closes the main from (thus bypassing our timer but keeping the application alive), is not really possible as it will close the application, if you close the main form.

Private SUB Form_Close()
    ON ERROR RESUME NEXT
    Application.Quit
END SUB

Now if you want to close the application on the middle of the day we need to do something different. Actually the above code does that as well with this section of the code snippet

    quit_val = DLookup("[quit_sw]", "tbl_quit")
    IF quit_val = True THEN
        Application.Quit
    END IF

For that portion of the code to work you will of course need a table called tbl_quit. This table has a single Yes/No column called quit_sw with a single row with the column value unchecked. The above code checks every 5 minutes if this table has a check on that single row/column and if it does it quits the application. So anytime you want your users to leave the application then you will have to just open the database (backend) and set the single row/column to checked. In about 6 minutes all users will be out of the system.

To prevent new users from not entering the system, you will have to check this on application start (I have not included the code for that here). So the 1st thing the main form Open event should do is to see if the flag has been set and quit right away if it has been set. Now a quit like that will puzzle the users and keep them clicking on the database shortcut repeatedly! To avoid that, I would rather you open another form informing the users that they should close the database as it is in maintenance mode (or is going to be soon). Of course if they don't pay heed to the request it will close in the next 5 mintues automatically.

Remember to unset the flags once you are done with your regular maintenance. Take advantage of the unlocked database to compact and repair the database periodically.