Using the Power of VBScripts and WMI in Microsoft Access VBA


Note: This article has been modified to reflect the present common use of more than one processor in computers.

Lately I have been exploring the powerful capabilities of Scripting and WMI. It was very exciting to discover that it is very easy to convert VBScript for WMI into VBA code that works just like the scripts. While there are a few VBSript functions that cannot be used in VBA, most scripts can be easily modified to run from Access or other Office Applications. Why am I excited? With VBA and WMI you can do many things that are not possible using Windows API’s. WMI is so powerful one needs to be careful not to do some serious damage to your local Computer, a remote PC. or Windows Server!

Why is WMI so powerful? “…WMI is the core management-enabling technology built into Windows 2000, Windows XP, and the Windows Server 2003 family of operating systems. Based on industry standards overseen by the Distributed Management Task Force (DMTF), WMI is the instrumentation and plumbing through which all—well, almost all—Windows resources can be accessed, configured, managed, and monitored.” The last sentence is the one that lays out the power of WMI, which you, yes you, have in your VBA typing fingertips!

Using VBA and WMI here are a few of the things you can do on Local or Remote Computers:

  • Manage Users and Group Policies
  • Read and save Event Logs to files
  • Get the size of a hard drive’s free Memory
  • Get the Version of Microsoft Office
  • Get the attributes of Printers
  • Get the Operating System Service Pack Version (CSDVersion–ServicePackLast)
  • Get the Operating System Name
  • Get the Operating System Version Number
  • Get the Operating System Build Number
  • Windows Operating System Product/Serial Number
  • Get Scheduled Jobs
  • Count Running Processes
  • List Running Processes
  • Terminate Processes
  • Delete software
  • Install software
  • Upgrade software
  • List all Windows installed software
  • List Microsoft software Product IDs
  • Get the Registered User

In fact, the greatest difficulty with using WMI is there are thousands of Properties and Methods that are available for you to access. WMI has Classes, Properties, and Methods and its own VBScript query dialect called WQL. WMI uses the Common Information Model (CIM) to access information and perform actions. The CIM grants us access to almost everything about a PC. You can learn more about CIM here.

The following is an example of VBA code using WMI. This code gets the ProcessorIDs of a local computer which can be very useful.  This code can be used as a template of sorts to access much of the information you will want to get using WMI.

Function ProcessorIDs() As String

    ' Using late binding to avoid a Reference problem
    Dim strComputer As String
    Dim objWMIService As Object
    Dim colItems As Object
    Dim objItem As Object
    Dim strProcessorIDs As String

    strComputer = "."

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

    Set colItems = objWMIService.ExecQuery( _
        "SELECT * FROM Win32_Processor", , 48)
        
    For Each objItem In colItems
        strProcessorIDs = strProcessorIDs & ";" & Nz(objItem.ProcessorID, "")
    Next
    
    ' Remove the leading ; from the string  
    Do While Left(strProcessorIDs, 1) = ";"
        strProcessorIDs = Right(strProcessorIDs, (Len(strProcessorIDs) - 1))
    Loop
   
    ProcessorIDs = strProcessorIDs

    Set objItem = Nothing
    Set colItems = Nothing
    Set objWMIService = Nothing

End Function

How does the code work?

After declaring some variables, we create an instance of a WMI Object using GetObject(“winmgmts:”)

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

We use winmgmts, the CIM Object Manager, to access the “root” (“\root\cimv2″) of the CIM library. If we are accessing a remote computer “{impersonationLevel=impersonate}!\\” grants us access if we have the right security credentials.

The strComputer variable is given a value of “.” when we are accessing the local computer. If we want to access a remote computer, instead of using “.” we need to use the path and name of the remote computer: GetObject(“winmgmts:\TargetComputer”) or GetObject(“winmgmts:\DomainName\TargetComputer”).

Next we instantiate a collection of properties of the WMI Win32_Processor Class.

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

We use a WQL query to select all the Properties in the Class. You can learn more about WQL, the WMI Query Language, here.

Finally we get the Processor ID serial number which is actually a string.

    For Each objItem In colItems
        ProcessorID = Nz(objItem.ProcessorID, 0)
    Next

Getting the ProcessorIDs can help provide some level of security, enabling us to know if the database has been copied to a different computer. If we have already saved at least one of the ProcessorIDs to a Custom Database Property we can check to see if the value has changed using the following code which can be called by the AutoExec Macro or a Splash Form. Warning: Make backup copy of your database files before using the following code.

Function StartSafe() As Boolean
    Dim strProcessorID As String

    ' Get the stored ProcessorIDs from
    ' the custom Database property
    strProcessorID = GetDBPropValue("dbPrpProcessorID")

    If InStr(1, ProcessorIDs, strProcessorID) > 0 Then
        StartSafe= True
    Else
        StartSafe= False
        ' The database has been copied to an unauthorized computer!
        ' Delete the Back End Database to protect your data
        ' Uncomment the next line of code to delete the back end database
        'Kill (CurrentProject.Path & "\BackEndDB.mdb")
        ' If this is the back end add code to Delete all tables
        ' Add code to Delete all Modules
        DoCmd.Quit
    End If
End Function

You can get the code for the GetDBPropValue Function and other Database Properties Functions at
Custom Database Properties Creation and Use.

In our next article we will learn more about using the power of WMI with VBA.

I still have a lot to learn about VBScript and WMI. I would appreciate any corrections if I make an error in these posts.

You can download the free sample database, WMISample.zip, which contains the code used in this article plus forms and many other procedures using WMI.

Other free downloads include our new Microsoft Access and SQL Azure and demonstration application which uses Microsoft Access and SQL Azure to leverage the power of cloud computing.

More Free Downloads:
Us or UK/AU Pop-up Calendar
Report Date Dialog Form in US or UK/AU Version.
Free Church Management Software with Contributions management.
Code Samples


Get the Access and Outlook Appointment Manager to manage all of your Outlook Calendar Appointments and Access dated information.


Happy computing,

Patrick (Pat) Wood
Gaining Access

About these ads

8 Comments

  1. Posted May 21, 2009 at 6:54 AM | Permalink | Reply

    Да,aleks,побороть лень, действительно иногда очень сложно..

  2. Posted May 21, 2009 at 9:22 AM | Permalink | Reply

    Это почему мы получаем оплащенными настолько очень наилучшим образом. Я сделал шутку. Я надеюсь что это переводит наилучшим образом.

    Без шутить, его более менее осложненных и более безопасных для использования VBA и WMI чем API’ s который может загубить вашу базу данных если ошибка памяти API происходит.

    That is why we get paid so very well. I made a joke. I hope this translates well.

    Without joking, it less complicated and safer to use VBA and WMI than API’s which can ruin your Database if an API memory error occurs.

  3. Posted May 24, 2009 at 1:22 PM | Permalink | Reply

    Мне кажется очень полезная штука

    • Posted May 24, 2009 at 1:37 PM | Permalink | Reply

      Спасибо за ваши добрые слова.

      In English: Thank you for your kind words.

  4. Posted May 26, 2009 at 2:36 AM | Permalink | Reply

    Спасибо, интересно было прочитать.

    • Posted May 26, 2009 at 10:21 AM | Permalink | Reply

      Пожалуйста. Я рад, что Вы любите статью. Я планирую объявить другую статью сегодня.

      English:
      You are welcome. I am glad you like the article. I plan to post another article today.

  5. Michael Modica
    Posted November 8, 2010 at 12:56 PM | Permalink | Reply

    I got errors until I put a double backslash after the exclamation point instead of a single backslash in the GetObject line:

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

    Also, the Nz function is not defined here. I just didn’t call it, and it seemed to work:

    ProcessorId = objItem.ProcessorId

    Thanks so much, this was very helpful.

    • Posted November 8, 2010 at 2:07 PM | Permalink | Reply

      Thank you for your kind words. And thank you for catching the missing \. There does need to be two there and I will correct it.

      The Nz Function is used as a precaution to avoid an error in case no value is returned.

Leave a Reply

Please log in using one of these methods to post your comment:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Follow

Get every new post delivered to your Inbox.

Join 72 other followers

%d bloggers like this: