Differences
This shows you the differences between two versions of the page.
|
tech:msaccess:release_user_lock [2014/11/15 09:13] |
tech:msaccess:release_user_lock [2014/11/15 09:13] (current) |
||
|---|---|---|---|
| Line 1: | Line 1: | ||
| + | ====== 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. | ||
| + | |||
| + | ===== Issues ===== | ||
| + | There are two sets of issues involved here. One is where the users are simply connected 7x24 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. | ||
| + | |||
| + | ==== Closing Application everyday ==== | ||
| + | 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. | ||
| + | |||
| + | <code qbasic> | ||
| + | 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 | ||
| + | </code> | ||
| + | |||
| + | 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. | ||
| + | |||
| + | <code qbasic> | ||
| + | Private Sub Form_Close() | ||
| + | On Error Resume Next | ||
| + | Application.Quit | ||
| + | End Sub | ||
| + | </code> | ||
| + | |||
| + | |||
| + | ==== Closing Application On Demand ==== | ||
| + | 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 | ||
| + | <code qbasic> | ||
| + | quit_val = DLookup("[quit_sw]", "tbl_quit") | ||
| + | If quit_val = True Then | ||
| + | Application.Quit | ||
| + | End If | ||
| + | </code> | ||
| + | |||
| + | 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. | ||
| + | ===== Other considerations ===== | ||
| + | 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. | ||
| + | |||
