When you’re managing a multi-user Microsoft Access application, there are times when you need all users out of the database so that you can make changes to table structures and other items. Asking everyone to shut down is unreliable and using the Windows Task Manager to forcibly shut down instances of Microsoft Access is risky at best. Most multi-user apps should be split into separate front-end / back-end files with each user having a separate copy of the front-end so you need something that will signal all those copies to shut down and stay shut down until whatever maintenance you’re performing is complete.

In summary, my preferred way to do this is to use a settings table in the back-end database file that includes a boolean field which can be set to True to activate maintenance mode. The front-end files contain a form that stays open but hidden in the background as long as the application is open. This form uses a timer to check the value of the boolean field every 10 minutes or so. If the field is set to True, the application will notify the user, give them a minute or two to finish what they’re doing and then use the Applicaiton.Quit command to shut down automatically.

Ideally, you should still communicate with the users to let them know the database will be down for a period of time but this method helps with users who leave the app open overnight or while out to lunch, etc..

Creating the Settings Table

The settings table doesn’t have to be complicated and only needs one row. The boolean field is the one essential setting here. The example shows additional fields for timing settings that can be hard-coded if you prefer.

I would name this table starting with “USys”. This marks it as a user-defined system table so it will be hidden from the navigation pane unless system tables are set to visible. You can also right-click on the table, select Properties and set the table to be hidden to give it an extra bit of obscurity.

Remember that this table goes in the back-end file and should appear as a linked table in any front-end files that you create. You will need to reference the values in the table as part of the process.

Checking for Maintenance Mode

Once the table is in place, you need a form in your front-end file that will check the table field every so often to see if maintenance mode has been activated. If you’re using a Switchboard menu as part of your application, the code can be inserted there. Either way, the form must open as soon as the user starts the application and stay open until the app closes. You can do this by setting whatever form you choose as the Display Form under the Current Database options as shown below.

In the On Open event for whatever Display Form you’re using, you would need code similar to the following.

Dim blnMaintActive As Boolean

blnMaintActive = DFirst("[MaintActive]", "USysDBSettings")
intMaintCheck = Nz(DFirst("[CheckMinutes]", "USysDBSettings"), 10)

If blnMaintActive Then
    DoCmd.OpenForm "USysShutdown"
Else
    Me.TimerInterval = intMaintCheck * 60000
End If

(The latest version of Access in Office365 uses embedded macros instead of VBA to manage actions on the Switchboard. I found it was easy enough to recode those macro commands in VBA and add them to the code you see above.)

This code looks at the MaintActive field in the settings table and returns the boolean value it finds. If it finds a True value, it then opens the shutdown form that I’ll show you in a minute. The code also looks at the CheckMinutes setting which, in this case, specifies the interval in minutes at which the program should check for maintenance mode. I’d set it to something like 10 minutes to keep from adding too much activity to the program. You could simply hard-code this second value but flexibility is good, too. Remember that the TimerInterval property is specified in milliseconds.

You will also need to add the code to the OnTimer event of your startup form. This way the program will check for maintenance mode when it first starts and on every firing of the timer.

Notifying the User and Shutting Down

The USysShutdown form from the last section is the form that’s displayed to the user to let them know that the program is about to be shutdown. Its OnTimer event takes care of that. It doesn’t have to be anything elaborate.

In this example, the USysSettings table also includes a ShutdownMinutes field which specifies how long the user should have to take care of business after they’ve been notified. This can be set in this form’s On Open event.

Dim intShutdownMinutes As Integer
intShutdownMinutes = Nz(DFirst("[ShutDownMinutes]", "USysDBSettings"), 1)
Me.TimerInterval = intShutdownMinutes * 60000

Once the Timer on this form runs out, the OnTimer event of the USysShutdown form just needs to run the following command.

Application.Quit

Managing Shutdowns

Once you have all the code in place, you can shut down all remote instances just by opening the USysSettings table, setting the MaintActive field to True and waiting until all the instances complete their check of the table and their shutdown period. You’ll probably want to start Access while holding down the SHIFT key to bypass the startup code so that your copy doesn’t shut down as well.

If your network setup allows you to see the other instances of Access in Task Manager, you can watch them shutdown one by one. Otherwise, just wait for the amount of time you’ve set in the table and see if the locking file for the database (*.lccdb or *.ldb depending on version) is deleted. Sometimes it isn’t, even when the last instance shuts down, but you should still be able to get exclusive access to the database so you can make your changes.

In practice, I only use this feature after-hours unless there’s an absolute need to fix something immediately and that’s rare. Users often leave apps open overnight so it still comes in handy. It’s a good idea to make sure the users know that the feature is there and that their Access program might be shutdown automatically overnight for updates and, when possible, to notify them of specific shutdowns, especially if your app has any long-running processes in it.

Comeau Software Solutions can help you with your Microsoft Access project or find alternative solutions for your database needs. If you need assistance and would like more information, please contact us for details.

---

Logitech C-U0007 Unifying Receiver for Mouse and Keyboard Works with Any Logitech Product That Display The Unifying Logo (Orange Star, Connects up to 6 Devices) (C-U0007)

• Logitech Unifying receiver for mouse and keyboard
• Will work with any product that display the Unifying Logo

Check Price

ComeauSoftware.com uses affiliate links through which we earn commissions for sales.

---