Microsoft Access VBA Uses WMI to Control Windows Services

You can Stop, Start, Pause, and Resume Windows Services with Microsoft Access VBA by using the power of WMI (Windows Management Instrumentation). This is called “setting the State” of the Service. This can be useful, for example, if we want to make sure the SQL Server Reporting Services is running. We may also want to stop a service we do not need that is using up computer resources.

We can use the “Update Services Data” button on our Form to get the current information about all Windows Services on our computer. The current State of the Service is displayed in the “State” text box, which is encircled in orange.

Our Form provides essential information we need before we attempt to set the state of the Service. The “AcceptPause” value informs us if we can Pause the Service and the “AcceptStop” value indicates if the Service can be Stopped.

If we want to change the State of a Service we use the Combo box encircled in Red. The label displays the different States of a Service which are used as a Value List of the Combo box. They are Start, Stop, Pause, and Resume. When we select the desired State the Combo box AfterUpdate Event is used to set the State of the Service.

Private Sub cmdStopService_Click()

    Dim strServiceState As String
    Dim strServiceName As String

    ' Get the name of the current record's Service
    strServiceName = Me.txtName.Value

    ' Get the Selected State
    strServiceState = Me.cboSetServiceState.Value

    ' Call a Function to set the Service State
    ' passing the Service Name and the selected State
    Call SetServiceState(strServiceName, strServiceState)

End Sub

We could have used a button and a Function to set each State, but we decided to use just one procedure to set any of the four possible States. Much of the code in the next procedure has been discussed in previous articles.

Function SetServiceState(strServiceName As String, strServiceState As String) As Long

    Dim objWMIService As Object
    Dim colServices As Object
    Dim objService As Object
    Dim strComputer As String
    Dim strErrorMessage As String
    Dim errReturnCode As Long

    DoCmd.Hourglass True

    ' Set an error code not used by Microsoft
    errReturnCode = 9999

    strComputer = "."

    Set objWMIService = GetObject("winmgmts:" _
        & "{impersonationLevel=impersonate}!\\" _
        & strComputer & "\root\cimv2")

    Set colServices = objWMIService.ExecQuery _
        ("SELECT * FROM win32_Service" & _
        " WHERE Name = '" & strServiceName & "'")

    For Each objService In colServices
         ' Use Select Case to set the chosen Service State
        Select Case strServiceState
            Case "Start"
                errReturnCode = objService.StartService()

            Case "Stop"
                errReturnCode = objService.StopService()

            Case "Pause"
                errReturnCode = objService.PauseService()

            Case "Resume"
                errReturnCode = objService.ResumeService()
        End Select
    Next

    DoCmd.Hourglass False

    ' Get and display the results of the code
    Select Case errReturnCode
        Case Is = 0
            MsgBox "Service " & strServiceName & " has been changed to " _
                & strServiceState & " successfully.     ", vbInformation
        Case Is = 9999
            ' If errReturnCode has not changed the Service was not found
            MsgBox "Service """ & strServiceName & """ was not found" & _
                " and may not exist.       ", vbCritical
        Case Else
            ' Get the error messages supplied by Microsoft
            strErrorMessage = ErrReturnStateChange(errReturnCode)
            MsgBox "Service " & strServiceName _
                & " State change failed.      " & vbCrLf & "Reason: " _
                & strErrorMessage & "       ", vbExclamation _
                , "Service State Change Failed"
    End Select

    SetServiceState = errReturnCode

    Set objService = Nothing
    Set colServices = Nothing
    Set objWMIService = Nothing

End Function

Again we have used the error documentation provided by Microsoft to write the error code return procedure. Fortunately, the error codes for all four of the State Change Methods are the same, which enables us to use just one procedure.

Function ErrReturnStateChange(lngerrReturn As Long) As String

    Select Case lngerrReturn
        Case Is = 0
            ErrReturnStateChange = "Success"
        Case Is = 1
            ErrReturnStateChange = "Not Supported"
        Case Is = 2
            ErrReturnStateChange = "Access Denied"
        Case Is = 3
            ErrReturnStateChange = "Dependent Services Running"
        Case Is = 4
            ErrReturnStateChange = "Invalid Service Control"
        Case Is = 5
            ErrReturnStateChange = "Service Cannot Accept Control"
        Case Is = 6
            ErrReturnStateChange = "Service Not Active"
        Case Is = 7
            ErrReturnStateChange = "Service Request timeout"
        Case Is = 8
            ErrReturnStateChange = "Unknown Failure"
        Case Is = 9
            ErrReturnStateChange = "Path Not Found"
        Case Is = 10
            ErrReturnStateChange = "Service Already Stopped"
        Case Is = 11
            ErrReturnStateChange = "Service Database Locked"
        Case Is = 12
            ErrReturnStateChange = "Service Dependency Deleted"
        Case Is = 13
            ErrReturnStateChange = "Service Dependency Failure"
        Case Is = 14
            ErrReturnStateChange = "Service Disabled"
        Case Is = 15
            ErrReturnStateChange = "Service Logon Failed"
        Case Is = 16
            ErrReturnStateChange = "Service Marked For Deletion"
        Case Is = 17
            ErrReturnStateChange = "Service No Thread"
        Case Is = 18
            ErrReturnStateChange = "Status Circular Dependency"
        Case Is = 19
            ErrReturnStateChange = "Status Duplicate Name"
        Case Is = 20
            ErrReturnStateChange = "Status - Invalid Name"
        Case Is = 21
            ErrReturnStateChange = "Status - Invalid Parameter"
        Case Is = 22
            ErrReturnStateChange = "Status - Invalid Service Account"
        Case Is = 23
            ErrReturnStateChange = "Status - Service Exists"
        Case Is = 24
            ErrReturnStateChange = "Service Already Paused"
    End Select

End Function

So to change the State of a Windows Service with our Form, we first update the Services data with the “Update Services Data” button. Then we use the “Find Service” Combo box to go to the record of the selected Service. We can then read the “State” text box to see if the Service’s State is already set to what we want. We also need to check the “Start Mode” text box to see if the Service is Disabled. If so, we need to change the Start Mode to “Manual” or “Automatic” before we can set the State of the Service. We should also look at the “AcceptPause” and the “AcceptStop” text boxes to determine if we can set the State we want. We can then set the State of the Process using the Start, Stop, Pause, Resume Service combo box.

We look forward to discovering more of the power of using WMI with VBA in our next article.

You can download the free sample database, WMISample.zip which contains the Form and the code used in this article at our Gaining Access website.

More Free Downloads:
Us or UK/AU Pop-up Calendar
Report Date Dialog Form in US or UK/AU Version.
ScopeSight for Access Reports Demo: get the exact data you want on your Access Reports.
Free Church Management Software now with new Contributions management.
Code Samples

Get the Access and Outlook Appointment Manager when you purchase a Premium Membership with Total Access.

Happy computing,

Patrick (Pat) Wood
Gaining Access

Microsoft Access Controls Windows Services

You can Stop, Start, Pause, Resume, Disable, and Enable Windows Services with Microsoft Access VBA. Access VBA can do this by using the power of WMI (Windows Management Instrumentation). We are able to do this by using late binding to access the WbemScripting library. With late binding you do not have to worry about setting References or having broken references. But if you want to use early binding or use the Object Browser to learn more about using WMI you can set a reference to the Microsoft WMI Scripting V1.2 Library. The full name and path is C:\Windows\System32\wbem\wbemdisp.tlb on my computer.

In our last article we described Windows Services as the means by which Programs or Windows Processes to get things done as well as carry out vital functions for computer operations.

We recommend again that caution is used because changing Services settings can ruin a computer’s ability to function. It is vital to gain the knowledge necessary to safely manage Windows Services. We recommend a close reading of the Win32_Service Class webpage and related pages that document the Win32_Service Class Methods.

We want to show how you can use Access to stop and start Services which requires changing the “State” of the Service. But first we have know whether or not a Service is already running and if it can be started or stopped because you can’t start a Service if it is Disabled. You have to change the Start Mode first.

There are five Start Modes: Boot, System, Automatic, Manual, and Disabled. Using VBA and WMI provides even greater control than is provided by the Microsoft Management Console (MMC) Services Snap-in where your choices are usually just Automatic, Manual, or Disabled.

Microsoft provides the following information about the Start Mode of Services:

• Boot: Device driver started by the operating system loader. This value is valid only for driver services.
• System: Device driver started by the operating system initialization process. This value is valid only for driver services.
• Automatic: Service to be started automatically by the Service Control Manager during system startup.
• Manual: Service to be started by the Service Control Manager when a process calls the StartService method.
• Disabled: Service that can no longer be started.

As you can see from the list, it would be best if we do not change the Start Mode of most Services since changing from System to Boot, for example, may cause the Service to no longer work properly. No doubt that is why the MMC limits our Start Mode options. But there are some unwanted services we may want to to change to Disabled so they will not run at all. And there may be some services that we want to change from Disabled to Manual or Automatic so the Service will start as needed.

One of the benefits of using the Win32_Services Form is that we can gain information about all the services on our computer. It is very important to click the “Update Services Data” button to get the current information about a Service because old data may be incorrect. The Description is very helpful. We can see if the Service is Started, and the State informs us whether the service is running, stopped, or paused. We especially need to see the Start Mode, which we are preparing to set.

To enable changing the Start Mode of the Service in the Current Record we added a Combo box to the form. We have named our Combo box “cboSetStartMode”, set the Row Source Type to “Value List”, and added the following to the Row Source Property: Boot;System;Automatic;Manual;Disabled.

In the Combo box AfterUpdate Event we have this code:

Private Sub cboSetStartMode_AfterUpdate()

    Dim strServiceName As String
    Dim strSetStartMode As String

    ' Get the Name of the Service listed in this Record
    strServiceName = Me.txtName.Value
    ' Get the Start Mode selected in the Combo box
    strSetStartMode = Me.cboSetStartMode.Value
    ' Call the procedure to change the Start Mode
    Call SetStartMode(strServiceName, strSetStartMode)

End Sub

This procedure calls the SetStartMode Sub. The critical code of the SetStartMode procedure is errReturnCode = objService.ChangeStartMode(strStartMode). This code attempts to change the Start Mode and returns an error code integer signifying Success with 0 or an Error with any other value.

Since the errReturnCode’s default value is 0, we set it to a value not used by Windows near the beginning of the procedure, using the number 9999. We do this to avoid a silent, but significant error. If we did not change this value from 0, we could easily get a false return that the Start Mode had been successfully changed, when in fact it had not been changed at all! This would happen if there was no instance of the Service found, which would cause the line of code errReturnCode = objService.ChangeStartMode(strStartMode), that changes the Start Mode and gets the error return value, to be skipped.

If the errReturnCode remains at 9999 a Message box appears to report the error. Other error codes are changed to the corresponding error text by calling the errReturnStartMode Function.

Sub SetStartMode(strServiceName As String, strStartMode As String)

    Dim objWMIService As Object
    Dim colServices As Object
    Dim objService As Object
    Dim strComputer As String
    Dim strErrorMessage As String
    Dim errReturnCode As Long

    DoCmd.Hourglass True

    ' Set an error code not used by Microsoft
    errReturnCode = 9999

    strComputer = "."

    Set objWMIService = GetObject("winmgmts:" _
        & "{impersonationLevel=impersonate}!\\" _
        & strComputer & "\root\cimv2")

    Set colServices = objWMIService.ExecQuery _
        ("SELECT * FROM win32_Service" & _
        " WHERE Name = '" & strServiceName & "'")

    For Each objService In colServices
        ' Use the ChangeStartMode Method to change the Start Mode
        ' errReturnCode informs us of success or errors
        errReturnCode = objService.ChangeStartMode(strStartMode)
    Next

    DoCmd.Hourglass False

    ' Get the Success or Error message
    Select Case errReturnCode
        Case Is = 0
            MsgBox "Service " & strServiceName & " has been set to " _
                & strStartMode & " Start Mode successfully.     " _
                , vbInformation
        Case Is = 9999
            ' If errReturnCode has not changed the Service was not found
            MsgBox "Service """ & strServiceName & """ was not found" & _
                " and may not exist.       ", vbCritical
        Case Else
            ' Get the error messages supplied by Microsoft
            strErrorMessage = errReturnStartMode(errReturnCode)
            MsgBox "Service " & strServiceName _
                & " Start Mode change failed.     " & vbCrLf _
                & "Reason: " & strErrorMessage & "        ", _
                vbExclamation, "Service Start Mode Change Failed"
    End Select

    Set objService = Nothing
    Set colServices = Nothing
    Set objWMIService = Nothing

End Sub

When the errReturnCode is passed to this Function the error message provided by Microsoft is returned. We used data from Microsoft’s ChangeStartMode Method of the Win32_Service Class web page to build the Function.

Function errReturnStartMode(lngerrReturn As Long) As String

    Select Case lngerrReturn
        Case Is = 0
            errReturnStartMode = "Success"
        Case Is = 1
            errReturnStartMode = "Not Supported"
        Case Is = 2
            errReturnStartMode = "Access Denied"
        Case Is = 3
            errReturnStartMode = "Dependent Services Running"
        Case Is = 4
            errReturnStartMode = "Invalid Service Control"
        Case Is = 5
            errReturnStartMode = "Service Cannot Accept Control"
        Case Is = 6
            errReturnStartMode = "Service Not Active"
        Case Is = 7
            errReturnStartMode = "Service Request Timeout"
        Case Is = 8
            errReturnStartMode = "Unknown Failure"
        Case Is = 9
            errReturnStartMode = "Path Not Found"
        Case Is = 10
            errReturnStartMode = "Service Already Running"
        Case Is = 11
            errReturnStartMode = "Service Database Locked"
        Case Is = 12
            errReturnStartMode = "Service Dependency Deleted"
        Case Is = 13
            errReturnStartMode = "Service Dependency Failure"
        Case Is = 14
            errReturnStartMode = "Service Disabled"
        Case Is = 15
            errReturnStartMode = "Service Logon Failure"
        Case Is = 16
            errReturnStartMode = "Service Marked For Deletion"
        Case Is = 17
            errReturnStartMode = "Service No Thread"
        Case Is = 18
            errReturnStartMode = "Status Circular Dependency"
        Case Is = 19
            errReturnStartMode = "Status Duplicate Name"
        Case Is = 20
            errReturnStartMode = "Status Invalid Name"
        Case Is = 21
            errReturnStartMode = "Status Invalid Parameter - Selected Start Mode is Invalid"
        Case Is = 22
            errReturnStartMode = "Status Invalid Service Account"
        Case Is = 23
            errReturnStartMode = "Status Service Exists"
        Case Is = 24
    errReturnStartMode = "Service Already Paused"
End Select

End Function

So to change the Start Mode of a Windows Service with our Form, we use the “Find Service” Combo box to move to the Service’s Record. Next we carefully note the information on the Form about the Service so we make a safe decision about what we plan to do. Then we use the “Set Start Mode” Combo box to change the Service Start Mode. The message box will then let us know whether or not the Start Mode was changed, and if not, the reason for the failure.

In our next article we plan to demonstrate starting, stopping, pausing, and resuming services.

You can download the free sample database, WMISample.zip which contains the Form and the code used in this article at our Gaining Access website.

More Free Downloads:
Us or UK/AU Pop-up Calendar
Report Date Dialog Form in US or UK/AU Version.
ScopeSight for Access Reports Demo: get the exact data you want on your Access Reports.
Free Church Management Software now with new Contributions management.
Code Samples

Get the Access and Outlook Appointment Manager when you purchase a Premium Membership with Total Access.

Happy computing,

Patrick (Pat) Wood
Gaining Access

Microsoft Access Reads Windows Services

Our last two articles were about using WMI to manage Processes which are software programs that run on our computers. Processes are like CEOs that are up front and visible. Windows Services are like the CEO’s Secretary who is the one who really gets things done in the background. Some Processes are also Services, but most Services start when the computer boots or when a Program is opened or needs to perform a particular action such as Printing.

Managing Services can be useful to the Access Developer. You may need to pause, stop, disable, enable, or start a Service before performing some action with Access. You may need to manage some SQL Server Services or Network Services.

As I was working to build a table and form to manage Windows Services I found a browser toolbar that I knew nothing about. Other examples of Services are Software Updaters, SQL Server Services, Network Services, some Drivers, Logging and Reporting Services, and much more. In fact, Services enable most everything done by a Computer.

This brings us to an important point. When you deal with Services, you better know what you are doing or you can cause yourself a lot of grief. In future articles we will Stop, Start, Pause, Resume, Disable, and Enable some Services. We could easily demonstrate how to Delete Services, but I don’t want to help some poor soul accidentally cripple their Computer.

In this article we will show you how to get Services data, append it to a table, and display it in a Form. In upcoming articles we will use this Form to manage Services.

We have a command button on our Form that is used to get the Services data. Because Services information changes often, we don’t feel a need to keep past Services data so we delete all the existing records in our table and then fill it with the latest data.

Private Sub cmdGetServicesData_Click()
On Error GoTo Err_cmdGetServicesData_Click

    Dim db As DAO.Database
    Dim strSQL As String

    strSQL = "DELETE * FROM tblWin32_Service"

    Set db = CurrentDb

    ' Delete all records in the Table
    ' In case of error, no records are changed
    db.Execute strSQL, dbFailOnError

    Set db = Nothing

    ' The following code takes a while to run
    ' Let the user know Access is working
    DoCmd.Hourglass True

    ' Fill the Table with the current Service Data
    Call FilltblWin32_Services("tblWin32_Service", "Win32_Service")

    ' Fill the form with the new data
    Me.Requery

	' Update the Find Service Combo box
    Me.cboFindService.Requery

Exit_cmdGetServicesData_Click:
    ' Make sure the Hourglass is Off
    ' This code will run even if there is an error
    DoCmd.Hourglass False
    Exit Sub

Err_cmdGetServicesData_Click:
    MsgBox "Error " & Err.Number & " (" & Err.Description & ")" & _
    " In procedure cmdGetServicesData_Click"
    Resume Exit_cmdGetServicesData_Click

End Sub

The cmdGetServicesData_Click sub calls the FilltblWin32_Services Procedure that fills the table with data.

Sub FilltblWin32_Services(strTableName As String, strClass As String)

    Dim db As DAO.Database
    Dim rst As DAO.Recordset
    Dim objWMIService As Object
    Dim colInstances As Object
    Dim objInstance As Object
    Dim colProperties As Object
    Dim objProperty As Object
    Dim strComputer As String
    Dim strNameSpace As String
    Dim strPropName As String
    Dim strPropValue As String
    Dim varID

    strComputer = "."

    strNameSpace = "\root\cimv2"

    Set objWMIService = GetObject("winmgmts:" & _
        "{impersonationLevel=impersonate}!\\" & _
        strComputer & strNameSpace)

    Set colInstances = objWMIService.ExecQuery( _
        "SELECT * FROM " & strClass, , 48)

    'Set a pointer to the Database
    Set db = CurrentDb

    'Instantiate the recordset
    Set rst = db.OpenRecordset(strTableName, dbOpenDynaset)

    ' Get Each Instance
    For Each objInstance In colInstances
        'Add a New Record to the Recordset
        rst.AddNew
        ' Get the Property Names and Values in each Instance
        Set colProperties = objInstance.Properties_

        For Each objProperty In colProperties
            strPropName = objProperty.Name

            ' Avoid errors caused by Nulls
            strPropValue = CStr(Nz(objProperty.Value, vbNullString))

            ' Change Zero Length Strings to the string "Null"
            If Len(strPropValue & vbNullString) = 0 Then
                strPropValue = "Null"
            Else
                ' Some Values need to be converted to be understood
                Select Case strPropName
                    Case "ErrorControl"
                        strPropValue = GetErrorControl(strPropValue)
                    Case "StartMode"
                        strPropValue = GetStartMode(strPropValue)
                    Case "InstallDate"
                        strPropValue = WMIDateConvert(strPropValue)
                End Select
            End If
            ' Add the value to the Recordset
            rst(strPropName) = strPropValue
        Next objProperty

        ' This table does not use Autonumber so
        ' get the previous high number and add 1
        varID = DMax("ServiceID", "tblWin32_Service") + 1
        ' If this is first one, then value will be null
        If IsNull(varID) Then varID = 1
        rst!ServiceID = varID
        ' Get the current Date and Time
        rst!LastUpdated = Now()
        ' Do not forget to save the new Record to the Recordset
        rst.Update
    Next objInstance

    ' Release Memory
    rst.Close
    Set objProperty = Nothing
    Set colProperties = Nothing
    Set objInstance = Nothing
    Set colInstances = Nothing
    Set objWMIService = Nothing
    Set rst = Nothing
    Set db = Nothing

End Sub

After the table is filled with the data, the code returns to our initial sub and requeries the Form so it will display the Services information.

You may find some Services there you do not want or need and you may find some that are not running, but you want to start. We plan to write about how to manage Services in our next article.

You can download the free sample database, WMISample.zip which contains the Form and the code used in this article at our Gaining Access website.

More Free Downloads:
Us or UK/AU Pop-up Calendar
Report Date Dialog Form in US or UK/AU Version.
ScopeSight for Access Reports Demo: get the exact data you want on your Access Reports.
Free Church Management Software now with new Contributions management.
Code Samples

Get the Access and Outlook Appointment Manager when you purchase a Premium Membership with Total Access.

Happy computing,

Patrick (Pat) Wood
Gaining Access

Microsoft Access: The Terminator of Processes

Microsoft Access can Terminate Processes using WMI. In other words, Access can shut down an application that is running on a computer. This capability is very useful when using Office automation or automation with other applications. For example, you may want to open an Excel Spreadsheet, import data, and then close Excel using the code in this article.

Access VBA can also be used to terminate a program when it “hangs” or “freezes”. I have at times thorougly enjoyed terminiating some repeat offenders using Access or VBSripts.

The code below shows the basics of how we can terminate a Process. We are using the name of the process in the WQL Query so we do not have to loop through all of the running processes. For example, if we want to close Notepad we can call the procedure using the name of the process as an argument like this: Call TerminateProcess(”notepad.exe”)

Sub TerminateProcess(strProcess As String)

    Dim objWMIService As Object
    Dim colProcesses As Object
    Dim objProcess As Object
    Dim strComputer As String

    strComputer = "."

    Set objWMIService = GetObject("winmgmts:" _
        & "{impersonationLevel=impersonate}!\\" _
        & strComputer & "\root\cimv2")

    Set colProcesses = objWMIService.ExecQuery _
        ("SELECT * FROM Win32_Process WHERE" & _
        " Name = '" & strProcess & "'")

    For Each objProcess In colProcesses
        objProcess.Terminate
    Next objProcess

    Set objProcess = Nothing
    Set colProcesses = Nothing
    Set objWMIService = Nothing

End Sub

We can make this code more helpful by getting the result of the Terminate Method. We can change the procedure to a function that will return the result as an integer. We can also use a Message Box. The modified code below does both.

Function ProcessTerminate(strProcess As String) As Long

    Dim objWMIService As Object
    Dim colProcesses As Object
    Dim objProcess As Object
    Dim strComputer As String
    Dim strMsg As String
    Dim intReturn As Long
    Dim booFound As Boolean

    strComputer = "."

    Set objWMIService = GetObject("winmgmts:" _
        & "{impersonationLevel=impersonate}!\\" _
        & strComputer & "\root\cimv2")

    Set colProcesses = objWMIService.ExecQuery _
        ("SELECT * FROM Win32_Process WHERE" & _
        " Name = '" & strProcess & "'")

    For Each objProcess In colProcesses
        ' The Process is running - set the flag to True
        booFound = True
        ' End the Process and get the Return code
        intReturn = objProcess.Terminate
    Next objProcess

    ' Start a Message string
    strMsg = "   Process Name:  " & strProcess & "      " & vbCrLf & vbCrLf

    ' Determine the result
    Select Case intReturn
        Case 0
            If booFound = True Then
                strMsg = strMsg & "   Termination succeeded.       "
            Else
                strMsg = strMsg & "   Unknown failure.      "
                intReturn = 8
            End If
        Case 2
            strMsg = strMsg & "   Access denied.       "
        Case 3
            strMsg = strMsg & "   Insufficient privilege.       "
        Case 8
            strMsg = strMsg & "   Unknown failure.       "
        Case 9
            strMsg = strMsg & "   Path not found.       "
        Case 21
            strMsg = strMsg & "   Invalid parameter.       "
        Case Else
            strMsg = strMsg & "   Termination failed for unknown reason.       "
    End Select

    strMsg = strMsg & vbCrLf & vbCrLf & "   Time: " & Now
    MsgBox strMsg

    ProcessTerminate = intReturn

    Set objProcess = Nothing
    Set colProcesses = Nothing
    Set objWMIService = Nothing

End Function

We can also Terminate more than one process by creating an array of process names.

Sub ProcessesTerminate()

    Dim objWMIService As Object
    Dim colProcesses As Object
    Dim objProcess As Object
    Dim strComputer As String
    Dim intReturn As Long
    Dim strTargetProc
    Dim arrTargetProcs

    strComputer = "."

	' Create the array of process names
    arrTargetProcs = Array("mspaint.exe", "calc.exe", "notepad.exe")

    Set objWMIService = GetObject("winmgmts:" _
        & "{impersonationLevel=impersonate}!\\" _
        & strComputer & "\root\cimv2")

    Set colProcesses = objWMIService.ExecQuery( _
        "SELECT * FROM Win32_Process", , 48)

    Debug.Print "Checking for target processes ..."

    For Each objProcess In colProcesses
        For Each strTargetProc In arrTargetProcs
            ' Make all process names lowercase so they will match
            If LCase(objProcess.Name) = LCase(strTargetProc) Then
                Debug.Print vbCrLf & "Process Name: " & objProcess.Name
                Debug.Print "  Time: " & Now
                intReturn = objProcess.Terminate
                If intReturn = 0 Then
                    Debug.Print "  Terminated"
                Else
                    Debug.Print "  Unable to terminate"
                End If
            End If
        Next
    Next

    Set objProcess = Nothing
    Set colProcesses = Nothing
    Set objWMIService = Nothing

End Sub

We will learn more about how VBA can leverage the capabilities of WMI in the articles to come.

You can download the free sample database, WMISample.zip, which contains the code used in this article from our Gaining Access website. The code is located in the PAWWMIProcesses Module.

More Free Downloads:
Us or UK/AU Pop-up Calendar
Report Date Dialog Form in US or UK/AU Version.
ScopeSight for Access Reports Demo: get the exact data you want on your Access Reports.
Free Church Management Software now with new Contributions management.
Code Samples

Get the Access and Outlook Appointment Manager when you purchase a Premium Membership with Total Access.

Happy computing,

Patrick (Pat) Wood
Gaining Access

Microsoft Access Reads the Processes Running On Your Computer

Are you curious to know what processes are running on your computer? You may be surprised to discover exactly what programs are running and how many run when you boot up. Just a few minutes ago I had 68 processes running. By using VBA to access WMI, I gathered an abundance of information on those processes. There was so much information the Visual Basic Editor Immediate Window could not display it all, so I saved it to a text file.

In our last article we showed how we can get Win32_OperatingSystem Class information by using VBA to access the power of WMI (Windows Management Instrumentation). The the Win32_OperatingSystem Class only has one “instance” on my computer, but there are always a number of instances of processes on a running computer. We can get that information using VBA and WMI but we need to use different code.

In order to get the information on each process running on our computer we need to access each instance in the Win32_Process class. The procedure below shows how to loop through each instance and list all the Property Names and Values of the instance.

This procedure has two arguments we must pass to it. First we pass the Name of the WMI class, Win32_Process. The next argument is the full path and name of the text file to which we want to save the data. For example, the procedure can be run by using Call SaveWMIClassInstancesData(”Win32_Process”, “C:\Win32_ProcessProperties.txt”). Using these arguments gives the code flexibility and allows us to use the same code for other classes. Why write six or seven procedures when you only need to write just one?

Sub SaveWMIClassInstancesData(strClassName As String, strTextFilePath As String)

    Dim objWMIServices As Object
    Dim colInstances As Object
    Dim objInstance As Object
    Dim colProperties As Object
    Dim objProperty As Object
    Dim fso As Object
    Dim fsoFile As Object
    Dim strComputer As String
    Dim strNameSpace As String
    Dim intCount As Long

    ' There may be too much text for the immediate window
    ' so create a text file and save the data
    Set fso = CreateObject("Scripting.FileSystemObject")

	' Create an instance of the text file
    Set fsoFile = fso.CreateTextFile(strTextFilePath)

    strComputer = "."
    strNameSpace = "\root\cimv2"

    Set objWMIServices = GetObject("winmgmts:" & _
        "{impersonationLevel=impersonate}!\\" & _
        strComputer & strNameSpace)

	' Instantiate the Collection of Instances
    Set colInstances = objWMIServices.ExecQuery( _
        "SELECT * FROM " & strClassName, , 48)

    fsoFile.WriteLine "Properties of " & strClassName & " Class Instances "

	' Loop through each Instance
    intCount = 0
    For Each objInstance In colInstances
        intCount = intCount + 1
		' Get the Properties in the instance
        Set colProperties = objInstance.Properties_

		' Write some information to the text file
        fsoFile.WriteLine vbNewLine
        fsoFile.WriteLine "---------------------------------------"
        fsoFile.WriteLine "         Instance Number: " & intCount
        fsoFile.WriteLine "---------------------------------------"

		' Write the Property names and values to the text file
        For Each objProperty In colProperties
            fsoFile.WriteLine objProperty.Name & ": " & objProperty.Value
        Next objProperty
    Next objInstance

	fsoFile.Close

	' Release Memory
    Set fso = Nothing
    Set fsoFile = Nothing
    Set objProperty = Nothing
    Set colProperties = Nothing
    Set objInstance = Nothing
    Set colInstances = Nothing
    Set objWMIServices = Nothing

End Sub

Below is a sample of the data that was returned about Notepad.

—————————————
Instance Number: 67
—————————————
Caption: notepad.exe
CommandLine: “C:\WINDOWS\system32\notepad.exe”
CreationClassName: Win32_Process
CreationDate: 20090604134043.578125-240
CSCreationClassName: Win32_ComputerSystem
CSName: ACER-HO
Description: notepad.exe
ExecutablePath: C:\WINDOWS\system32\notepad.exe
ExecutionState:
Handle: 5640
HandleCount: 34
InstallDate:
KernelModeTime: 468750
MaximumWorkingSetSize: 1413120
MinimumWorkingSetSize: 204800
Name: notepad.exe
OSCreationClassName: Win32_OperatingSystem
OSName: Microsoft Windows XP Professional
OtherOperationCount: 111
OtherTransferCount: 400
PageFaults: 854
PageFileUsage: 1101824
ParentProcessId: 2248
PeakPageFileUsage: 1101824
PeakVirtualSize: 37982208
PeakWorkingSetSize: 3379200
Priority: 8
PrivatePageCount: 1101824
ProcessId: 5640
QuotaNonPagedPoolUsage: 2400
QuotaPagedPoolUsage: 62108
QuotaPeakNonPagedPoolUsage: 2944
QuotaPeakPagedPoolUsage: 77052
ReadOperationCount: 0
ReadTransferCount: 0
SessionId: 0
Status:
TerminationDate:
ThreadCount: 1
UserModeTime: 468750
VirtualSize: 30683136
WindowsVersion: 5.1.2600
WorkingSetSize: 3379200
WriteOperationCount: 0
WriteTransferCount: 0

The dates that are returned can be converted to a more readable format using the following procedure.

Function WMIDateConvert(dtmDate As String)
    WMIDateConvert = CDate(Mid(dtmDate, 5, 2) _
    & "/" & Mid(dtmDate, 7, 2) _
    & "/" & Left(dtmDate, 4) _
    & " " & Mid(dtmDate, 9, 2) _
    & ":" & Mid(dtmDate, 11, 2) _
    & ":" & Mid(dtmDate, 13, 2))
End Function

Since the processes change each time a program is opened, a text file is a suitable way to save the data. With some other WMI classes you may want to save the data to an Access Table and the SaveWMIClassInstancesData procedure is well suited to be modified for that purpose.

You can learn more about the meaning of the values returned by opening the Win32 Classes web page and scrolling down the list of classes until you find the name of the class you need.

There is also another way to get all of the data available from each instance of a WMI class. We can use the GetObjectText_ Method. This method returns the data in MOF (Managed Object Format). Like the previous procedure, we need to provide the WMI Class Name and the text file name and path as arguments: Call SaveWMIClassMOF(”Win32_Process”, “C:\Win32_ProcessMOFProperties.txt”)

Function SaveWMIClassMOF(strClass As String, Optional strTextFilePath As String) As String

    Dim objWMIService As Object
    Dim colItems As Object
    Dim objItem As Object
    Dim fso As Object
    Dim fsoFile As Object
    Dim strComputer As String
    Dim strNameSpace As String
    Dim strMOF As String

    Set fso = CreateObject("Scripting.FileSystemObject")
    Set fsoFile = fso.CreateTextFile(strTextFilePath)

    strComputer = "."
    strNameSpace = "\root\cimv2"

    Set objWMIService = GetObject("winmgmts:" & _
        "{impersonationLevel=impersonate}!\\" & _
        strComputer & strNameSpace)

    Set colItems = objWMIService.ExecQuery( _
        "SELECT * FROM " & strClass, , 48)

	' This code loops through each instance
	' and returns the instance's properties and values
    ' We build the complete MOF text with the strMOF variable
    For Each objItem In colItems
	    strMOF = strMOF & objItem.GetObjectText_
    Next

    fsoFile.Write strMOF

    fsoFile.Close

    Set fso = Nothing
    Set fsoFile = Nothing
    Set objItem = Nothing
    Set colItems = Nothing
    Set objWMIService = Nothing

End Function

Below is the MOF data about Notepad.

instance of Win32_Process
{
Caption = “notepad.exe”;
CommandLine = “\”C:\\WINDOWS\\system32\\notepad.exe\” “;
CreationClassName = “Win32_Process”;
CreationDate = “20090604134043.578125-240″;
CSCreationClassName = “Win32_ComputerSystem”;
CSName = “ACER-HO”;
Description = “notepad.exe”;
ExecutablePath = “C:\\WINDOWS\\system32\\notepad.exe”;
Handle = “5640″;
HandleCount = 34;
KernelModeTime = “468750″;
MaximumWorkingSetSize = 1413120;
MinimumWorkingSetSize = 204800;
Name = “notepad.exe”;
OSCreationClassName = “Win32_OperatingSystem”;
OSName = “Microsoft Windows XP Professional”;
OtherOperationCount = “111″;
OtherTransferCount = “400″;
PageFaults = 854;
PageFileUsage = 1101824;
ParentProcessId = 2248;
PeakPageFileUsage = 1101824;
PeakVirtualSize = “37982208″;
PeakWorkingSetSize = 3379200;
Priority = 8;
PrivatePageCount = “1101824″;
ProcessId = 5640;
QuotaNonPagedPoolUsage = 2400;
QuotaPagedPoolUsage = 62108;
QuotaPeakNonPagedPoolUsage = 2944;
QuotaPeakPagedPoolUsage = 77052;
ReadOperationCount = “0″;
ReadTransferCount = “0″;
SessionId = 0;
ThreadCount = 1;
UserModeTime = “468750″;
VirtualSize = “30683136″;
WindowsVersion = “5.1.2600″;
WorkingSetSize = “3379200″;
WriteOperationCount = “0″;
WriteTransferCount = “0″;
};

You may notice when you get MOF data all of the Properties may not be listed. If a Property Value is Null MOF does not return the Property.

The MOF data can be added to an Access table but requires a bit of work. We were able to use string functions to clean up the text and the Split Function to create an array of instances and another array of Properties to add the MOF data to a Table. Modifying the SaveWMIClassInstancesData procedure to add the data to a Table requires much less work and is less likely to produce errors in the data.

MOF is used not only to return data, but also to create new WMI Classes, Properties, and Methods.

Information about WMI Classes is contained in MOF files and describe all of the Properties and Methods of each WMI Class, provide information about Enumerations, and details the the data types that are used. We can access that information using code that returns amended qualifiers as well as the detailed Class information. As before, we use the Class name and a text file path as arguments in calling the Function: Call SaveWMIClassDescription(”Win32_Service”, “C:\Win32_ServiceMOFDescription.txt”)

Function SaveWMIClassDescription(strClass As String, strTextFilePath As String) As String

    Dim objClass As Object
    Dim objWMIService As Object
    Dim fso As Object
    Dim fsoFile As Object
    Dim strNameSpace As String
    Dim strComputer As String
    Dim strMOF As String

    Set fso = CreateObject("Scripting.FileSystemObject")
    Set fsoFile = fso.CreateTextFile(strTextFilePath)

    strComputer = "."
    strNameSpace = "\root\cimv2"

    'The wbemFlagUseAmendedQualifiers flag is required to get the amended
    ' Qualifiers and the Class Property and Method description
    Const wbemFlagUseAmendedQualifiers = &H20000

    Set objWMIService = GetObject("winmgmts:" & _
        "{impersonationLevel=impersonate}!\\" & _
        strComputer & strNameSpace)

    Set objClass = objWMIService.Get(strClass, wbemFlagUseAmendedQualifiers)

    strMOF = objClass.GetObjectText_

	' Formatting the text makes it much easier to read
	' Comment out this block of code to see the raw data
    strMOF = Replace(strMOF, ";", ";" & Chr(10))
    strMOF = Replace(strMOF, Chr(9), vbNullString)
    strMOF = Replace(strMOF, ".\n", "." & Chr(10) & "\n")
    strMOF = Replace(strMOF, ":\n", ":" & Chr(10) & "\n")
    strMOF = Replace(strMOF, ": \n", ":" & Chr(10) & "\n")
    strMOF = Replace(strMOF, "ToSubClass", Chr(10) & "ToSubClass")

    SaveWMIClassDescription = strMOF
    fsoFile.Write strMOF

    fsoFile.Close

    Set fsoFile = Nothing
    Set fso = Nothing
    Set objClass = Nothing
    Set objWMIService = Nothing

End Function

We will not take up the space to show a sample returned by this procedure since it will take up a lot of space to display a fairly representative sample.

As you can see VBA can use WMI to provide an abundance of information. We can also read and write to the Registry, read Event Logs, start and stop processes, change hardware settings, install and uninstall software, and much more. We look forward to discovering more about using the capabilities of VBA and WMI in our next article.

You can download the free sample database, WMISample.zip which contains the code used here at our Gaining Access website.

More Free Downloads:
Report Date Dialog Form in US or UK Version.
Pop-up Calendar
ScopeSight for Access Reports Demo: get the exact data you want on your Access Reports.
Free Church Management Software now with new Contributions management.
Code Samples

Get the Access and Outlook Appointment Manager when you purchase a Premium Membership with Total Access.

Happy computing,

Patrick (Pat) Wood
Gaining Access