Can you force Access to quit when PC goes into Standby mode?

[TheSmileyCoder]

In the office users often leave my application running when they go for lunch. While they are away the PC will go into standby or suspension, and the network access will be lost, causing a error message when the users return to their machines, not to mention that the backend is usually blocked from edits even though noone is actually using the database.

Is there any way to react to windoze going into standby and force - quit access?

[twinnyfo]

Smiley,

They key may be to keep it from going into standby mode....

See this thread, which was actually spawned by your question:

Reset System Idle Time

This may be a workaround....

[Rabbit]

You can use WMI for this. You'll want to put it in a .VBS file and call it when the database is opened.

Option Explicit
 
Dim objWMIService, colItems, objLatestEvent
 
Set objWMIService = GetObject("winmgmts:\\.\root\cimv2")
Set colItems = objWMIService.ExecNotificationQuery("Select * from Win32_PowerManagementEvent")
 
While True
    Set objLatestEvent = colItems.NextEvent
    WScript.Echo "A power management event happened" & vbcrlf & "Event type: " & objLatestEvent.EventType
    ' EventType
    ' 4  - Entering suspend
    ' 7  - Resuming from suspend
    ' 18 - Resume Automatic happened
WEnd

[Rabbit]

I was wondering if the code worked for you?

[TheSmileyCoder]

Sorry for not getting back to you. To be honest I dont immediately understand your solution and need to spend a bit of time on google first.

[Rabbit]

Basically, windows has this object called the WMI, or Windows Management Instrumentation. This object allows you to access all kinds of information about windows, hardware, software, and events happening on the computer.

Lines 1 and 3 are self explanatory.

Line 5 retrieves the WMI object for the current computer. (It can be used to get info about and manage remote computers as well.)

Line 6 runs a query for power management events.

Lines 8 and 15 establish an infinite loop to check for events.

Line 9 retrieves the next power management event.

Line 10 is a message box to describe what event just occurred.

And so what you want to do is replace line 10 with something like, if the event type is 4, then the computer is about to enter suspend mode, so quit out of Access.

[TheSmileyCoder]

Thank you. I still need to check up on how to run a vbscript from Access and how to make the vbscript quit access, and preferably the right session in case the user has another database open.

[Rabbit]

I suspect the answer may be that the users run a vbscript that creates an access application and opens the database and then sits in the background and monitors.

[TheSmileyCoder]

Would there be any benefit or trouble from running the access application from a script?

[Rabbit]

The main benefit would be that you get to run code outside the Access process. Which allows you to do stuff without tying up Access. If you plan on doing other stuff aside from monitoring when the computer goes into sleep mode however, you will need to use the asynchronous version of the WMI query.

For example, you can use it to update the front end, monitor computer resources, idle time, lock state, etc. While all of this, with the exception of updating the front end, can be done within the access application itself, it ties up the application to do the monitoring tasks.

As far as drawbacks, I haven't seen any nor do I forsee any. There might be some slight hiccups, such as if the script fails to initialize, then the database won't open. You can get around that by creating and opening the database first. Because even if the script crashes during it's run, it won't close the database unceremoniously.

A fully functioning script could look something like this

Option Explicit
 
Dim objAccess, objWMIService, colItems
 
Set objAccess = CreateObject("Access.Application")
objAccess.Visible = True
objAccess.OpenCurrentDatabase "C:\Database1.accdb"
 
Set objWMIService = GetObject("winmgmts:\\.\root\cimv2")
Set colItems = objWMIService.ExecNotificationQuery("Select * from Win32_PowerManagementEvent")
 
Do While True
    If colItems.NextEvent.EventType = 4 Then
        objAccess.Quit
        Set objAccess = Nothing
        Set colItems = Nothing
        Set objWMIService = Nothing
        Exit Do
    End If
Loop

[twinnyfo]

Would it also be possible to run this from a blank, hidden form that starts any time the database is opened, and simply runs this code in the background?

I use that trick to force users out remotely when I want to run updates.



And.... methinks I could learn much from this Jedi named Rabbit.....

[Rabbit]

@twinny, unfortunately no. Here's why.

The NextEvent method will not return until an event has occurred. So Access will freeze up until a power management event occurs. E.G. the computer goes to sleep.

Having said that, there is an asynchronous version of the WMI query. Unfortunately, I have not found a way to get that working within Access itself. You get some error about it not being able to find the remote host. And I haven't found a way around that error yet.

[Rabbit]

Having said that I had yet to find a way to get the asynchronous query to work, I have gotten the asynchronous query to work.

I used a class module for this. Conceivably you can do it all in a form if you wanted. You will have to add a reference to the Microsoft WMI Scripting Vx.xx Library. Whatever your version turns out to be.

Option Explicit
Dim WithEvents sink As SWbemSink
 
Private Sub sink_OnObjectReady(ByVal objWbemObject As SWbemObject, ByVal objWbemAsyncContext As SWbemNamedValueSet)
    If objWbemObject.EventType = 4 Then
        Application.Quit
    End If
    Set objWbemObject = Nothing
End Sub
 
Private Sub Class_Initialize()
    Dim services As SWbemServices
    Set sink = New SWbemSink
    Set services = GetObject("winmgmts:\\.\root\cimv2")
    services.ExecNotificationQueryAsync sink, "Select * from Win32_PowerManagementEvent"
End Sub

And in your form, something like this.

Option Compare Database
Option Explicit
Public sinker As Class1
 
Private Sub Form_Load()
    Set sinker = New Class1
End Sub

[TheSmileyCoder]

Thank you very much for your help so far. I will try to implement this during the next week.

[Rabbit]

Just wanted to follow up, were you able to get this to work?

[TheSmileyCoder]

Embarrassed to say that I have not found the time to test this yet. I did notice you putting together an article as well, which looks very nice.

[Josh Reagan]

Thank you Rabbit! The class module and form worked for me. Is there a way to use late binding instead of Microsoft WMI Scripting library reference?

[Rabbit]

It's been forever since I've looked at this. I don't know if you can late bind it due to the events, but I've never tried.