Hey, Scripting Guy! Working with Access Databases in Windows PowerShell

The Microsoft Scripting Guys

Databases are mysterious pieces of software. In their simplest form they are nothing more than filing cabinets for storing information. The real magic begins with the application of this stored information. Of course, the most beautifully designed database without any records is nothing more than an academic exercise. It is the data that makes the database. Whenever we hear about someone who has a huge database, people react with awe. Not due to the database, but for the data it contains.

How does all that data get into a database? Manual entry of data into a database is for the birds, and it went out with key punch cards. To build a database large enough to impress your friends and provide the potential to unlock the mysteries of your network, you must automate. Today, that means Windows PowerShell, and in this article, that's what we will use to collect some data about the local computer and write it to an Office Access database called ComputerData.mdb. This database can be created by hand, or you can use the script found in the article "How Can I Create a Database with More Than One Table?." We will call our script WriteToAccessDatabase.ps1 so we will know what it does.

We'll start by creating the Check-Path function, which will be used to ensure that the database exists. To create the function, we use the Function keyword, give the function a name, and define any input variables it may need to receive. The first thing Check-Path does is use the Test-Path cmdlet to see if the directory in the database path exists. To do this, it uses the Split-Path cmdlet to break the path into a parent portion and a child portion. We only need the parent portion of the path to verify the directory's existence. Here's how we use Split-Path to retrieve the parent path:

PS C:\> Split-Path C:\fso\ComputerData.mdb -Parent
C:\fso

Instead of checking for the presence of the path, we use the Not operator (!) to look for its absence. If the folder does not exist, the Throw keyword is used to raise an error:

Function Check-Path($Db)
{
 If(!(Test-Path -path (Split-Path -path $Db -parent)))
   { 
     Throw "$(Split-Path -path $Db -parent) Does not Exist" 
   }

Even if the folder exists, the database file might be missing. We use the ELSE keyword to introduce that alternate condition. Once again, we use the IF statement to look for the presence of the database file, and the Throw keyword to raise an error if it doesn't exist:

  ELSE
  { 
   If(!(Test-Path -Path $Db))
     {
      Throw "$db does not exist"
     }
  }
} #End Check-Path

We really don't need to use the IF…ELSE construction to verify the existence of the database. A simple call to the cmdlet Test-Path using the –path parameter would work. However, using IF…ELSE provides a higher level of feedback. We want to know if the directory exists and, if so, does the file exist? It is certainly possible the database might be missing from the folder, but it is also possible the folder itself could be missing. This gives more granular feedback and can aid in troubleshooting.

When we have ensured the database exists, we create the Get-Bios function to obtain the BIOS information from the Win32_Bios WMI class. Here's the Get-Bios function:

Function Get-Bios
{
 Get-WmiObject -Class Win32_Bios
} #End Get-Bios

By encapsulating the WMI call into a function, we gain the ability to easily change the function, such as adding remote capability or accepting credentials. The modification could be made here without impacting the rest of the script. In fact, from a testing perspective, if it doesn't work, you simply comment out the function code and the remaining script continues to work. For help in finding information related to the WMI classes, you can use the Windows PowerShell Scriptomatic shown in Figure 1. This tool lets you easily explore WMI namespaces and classes, and even creates the Windows PowerShell script to retrieve the information.

Figure 1 Windows PowerShell version of the Scriptomatic utility

Next, we'll create the Get-Video function to retrieve video information from the Win32_VideoController WMI class. As you can see, this function is similar to the Get-Bios function:

Function Get-Video
{
 Get-WmiObject -Class Win32_VideoController
} #End Get-Video

Now we need to make a connection to the database. To do this, we use the Connect-Database function. We create two input parameters for the Connect-Database function: –DB and –Tables whose values are stored in the $Db and $Tables variables inside the function. The first thing we do inside the Connect-Database function is to assign values to a couple of variables that are used to control the way the RecordSet is opened. The Open method of the RecordSet object can accept up to five different parameters, as follows:

RecordSet.Open Source, ActiveConnection, CursorType, LockType, Options 

The first is the source parameter, which evaluates to a valid command object, a SQL statement, a table name, a stored procedure call, a URL, or the name of a file or stream object containing a persistently stored Recordset. The second parameter is the ActiveConnection, a string that evaluates to a valid connection object, or a string that contains connectionstring parameters. The CursorType parameter is used to determine the type of cursor that will be used when opening the RecordSet. Allowable values for the cursor type are shown in Figure 2.

The LockType parameter is used to govern the type of lock to be used when updating records, and the options parameter is used to tell the provider how to evaluate the source parameter. The allowable values for the LockType parameter are shown in Figure 3.

All five of the parameters for the Open method of the RecordSet object are optional; generally, we use only the first four. After we have assigned values to use for the cursor enumeration and the lock type, we use the New-Object cmdlet to create a new ADODB.Connection object that we store in the $connection variable. We then use the Open method from the Connection object, which needs the provider name and the data source. We then call the Update-Records function and pass the $Tables variable. Here's the Connect-DataBase function:

Function Connect-Database($Db, $Tables)
{
  $OpenStatic = 3
  $LockOptimistic = 3
  $connection = New-Object -ComObject ADODB.Connection
  $connection.Open("Provider = Microsoft.Jet.OLEDB.4.0;Data Source=$Db" )
  Update-Records($Tables)
} #End Connect-DataBase

In the Update-Records function, the first thing we do is create an instance of the ADODB.RecordSet object. We use the New-Object cmdlet to do this and store the newly created RecordSet object in the $RecordSet variable. Next, we use the For Each statement to walk through our array of tables. The table names are stored in the $Tables variable and are assigned at the start of the script. Inside the ForEach loop, we first create our query, which is a rather generic Select * from $Table. The advantage of using a variable for the table name is we only need to write the code once; the table name in the query gets changed each time we loop through the array of table names.

We now come to the open method of the RecordSet object. We specify the query that is stored in the $Query variable, the connection object in the $Connection variable, the $OpenStatic value, and the $LockOptimistic value to control the way the RecordSet is opened. We then use the Invoke-Expression cmdlet to execute the value of a string. We do this because we have created two functions that are designed to update the different database tables. We named the functions after the tables that they update. We are not allowed to call a function name when half of it is a variable, so we need to resolve the variable and then call the function.

But that does not work either—at least not directly. What we want to do is to treat the function name as if it were a string and not a command. But we want to execute it like a command. To do this, we use Invoke-Expression. This cmdlet calls each of the different update functions. Inside the loop that goes through the array of table names, we close each of the RecordSet objects, then return to the next item in the array of table names, create a new query, open a new RecordSet object, and call a new function. This continues for each of the table names in the array of tables, like so:

Function Update-Records($Tables)
{
  $RecordSet = new-object -ComObject ADODB.Recordset
   ForEach($Table in $Tables)
     {
      $Query = "Select * from $Table"
      $RecordSet.Open($Query, $Connection, $OpenStatic, $LockOptimistic)
      Invoke-Expression "Update-$Table"
      $RecordSet.Close()
     }

After the records are updated, we can close the connection. To do this, we use the Close method from the Connection object:

   $connection.Close()
} #End Update-Records

The Update-Records function calls two support functions, Update-Bios and Update-Video, which are designed to update the appropriate fields in the respective database table. If you were to add additional tables to your database, you would need to add an additional Update* function to update the new tables. As a best practice, we recommend keeping the database field names the same as the WMI property names. It makes things much easier to keep track of. When writing a script to update an existing database, you may want to look at the database schema for the tables, columns, and data types contained in the fields. The database schema for the ComputerData database is shown in Figure 4. This view was generated by the script from the article "How Can I Tell Which Tables and Columns Are in a Database without Opening It?"

Figure 4 The database schema for the ComputerData database

In the Update-Bios function, we first post a message stating we are updating the BIOS information. We then call the Get-Bios function and store the returned WMI Win32_Bios object in the variable $BiosInfo. Now we need to add a record to the database table. To do this, we call the AddNew method from the RecordSet object. After we have a new record, we add information to each of the fields in the table. When all the fields have been updated, we call the Update method to commit the record to the table. The complete Update-Bios function is shown here:

Function Update-Bios
{
 "Updating Bios"
 $BiosInfo = Get-Bios
 $RecordSet.AddNew()
 $RecordSet.Fields.Item("DateRun") = Get-Date
 $RecordSet.Fields.Item("Manufacturer") = $BiosInfo.Manufacturer
 $RecordSet.Fields.Item("SerialNumber") = $BiosInfo.SerialNumber
 $RecordSet.Fields.Item("SMBIOSBIOSVersion") = $BiosInfo.SMBIOSBIOSVersion
 $RecordSet.Fields.Item("Version") = $BiosInfo.Version
 $RecordSet.Update()
} #End Update-Bios

When the BIOS table has been updated, we need to update the video table. To do this, we can call the Update-Video function, which is exactly the same as the Update-Bios function. We present a message stating we are updating the video, call the Get-Video function to retrieve the video information, call the AddNew method to add a new record to the Video table, and write all of the information to the appropriate fields. When we are done, we call the Update method.

A potential issue in collecting the video information is the number of video controllers on the computer. My personal computer has a daughter card and reports multiple video controllers. To handle this eventuality, we use the ForEach statement to iterate through a collection of Win32_VideoControllers. If you are not interested in the daughter card configuration information or if your video card is dual channel and reports the same information twice, you could remove the ForEach loop and select $VideoInfo[0] to index directly into the first record that is reported. The problem with this approach is that if the query returns a singleton, you will generate an error because you cannot index into a single record:

Function Update-Video
{ "Updating video" $VideoInformation = Get-Video 
Foreach($VideoInfo in $VideoInformation)  
  {
   $RecordSet.AddNew()   $RecordSet.Fields.Item("DateRun") = Get-Date
   $RecordSet.Fields.Item("AdapterCompatibility") = $VideoInfo.AdapterCompatibility
   $RecordSet.Fields.Item("AdapterDACType") = $VideoInfo.AdapterDACType
   $RecordSet.Fields.Item("AdapterRAM") = $VideoInfo.AdapterRAM
   $RecordSet.Fields.Item("Description") = $VideoInfo.Description
   $RecordSet.Fields.Item("DriverDate") = $VideoInfo.DriverDate
   $RecordSet.Fields.Item("DriverVersion") = $VideoInfo.DriverVersion
   $RecordSet.Update()
  }
} 
#End Update-Video

The entry point to the script points to the database, lists the tables, and then calls the connect-DataBase function, as shown here:

$Db = "C:\FSO\ComputerData.mdb"+
$Tables = "Bios","Video"
Check-Path -db $Db
Connect-DataBase -db $Db -tables $Tables

After the script is run, new records are written to the ComputerData.mdb database as shown in Figure 5. The complete WriteToAccessDatabase.ps1 script can be seen in Figure 6.

Figure 5 New records added to the ComputerData.mdb database

Figure 6 WriteToAccessDataBase.ps1

Function Check-Path($Db)
{
 If(!(Test-Path -path (Split-Path -path $Db -parent)))
   { 
     Throw "$(Split-Path -path $Db -parent) Does not Exist" 
   }
  ELSE
  { 
   If(!(Test-Path -Path $Db))
     {
      Throw "$db does not exist"
     }
  }
} #End Check-Path

Function Get-Bios
{
 Get-WmiObject -Class Win32_Bios
} #End Get-Bios

Function Get-Video
{
 Get-WmiObject -Class Win32_VideoController
} #End Get-Video

Function Connect-Database($Db, $Tables)
{
  $OpenStatic = 3
  $LockOptimistic = 3
  $connection = New-Object -ComObject ADODB.Connection
  $connection.Open("Provider = Microsoft.Jet.OLEDB.4.0;Data Source=$Db" )
  Update-Records($Tables)
} #End Connect-DataBase

Function Update-Records($Tables)
{
  $RecordSet = new-object -ComObject ADODB.Recordset
   ForEach($Table in $Tables)
     {
      $Query = "Select * from $Table"
      $RecordSet.Open($Query, $Connection, $OpenStatic, $LockOptimistic)
      Invoke-Expression "Update-$Table"
      $RecordSet.Close()
     }
   $connection.Close()
} #End Update-Records

Function Update-Bios
{
 "Updating Bios"
 $BiosInfo = Get-Bios

 $RecordSet.AddNew()
 $RecordSet.Fields.Item("DateRun") = Get-Date
 $RecordSet.Fields.Item("Manufacturer") = $BiosInfo.Manufacturer
 $RecordSet.Fields.Item("SerialNumber") = $BiosInfo.SerialNumber
 $RecordSet.Fields.Item("SMBIOSBIOSVersion") = $BiosInfo.SMBIOSBIOSVersion
 $RecordSet.Fields.Item("Version") = $BiosInfo.Version
 $RecordSet.Update()
} #End Update-Bios

Function Update-Video
{
 "Updating video"
 $VideoInformation = Get-Video
 Foreach($VideoInfo in $VideoInformation)
  { 
   $RecordSet.AddNew()
   $RecordSet.Fields.Item("DateRun") = Get-Date
   $RecordSet.Fields.Item("AdapterCompatibility") = $VideoInfo.AdapterCompatibility
   $RecordSet.Fields.Item("AdapterDACType") = $VideoInfo.AdapterDACType
   $RecordSet.Fields.Item("AdapterRAM") = $VideoInfo.AdapterRAM
   $RecordSet.Fields.Item("Description") = $VideoInfo.Description
   $RecordSet.Fields.Item("DriverDate") = $VideoInfo.DriverDate
   $RecordSet.Fields.Item("DriverVersion") = $VideoInfo.DriverVersion
   $RecordSet.Update()
  }
} #End Update-Video

# *** Entry Point to Script ***

$Db = "C:\FSO\ComputerData.mdb"
$Tables = "Bios","Video"
Check-Path -db $Db
Connect-DataBase -db $Db -tables $Tables

If you would like to learn more about working with Office Access databases from within Windows PowerShell, check out the "Hey, Scripting Guy!" archive for the week of February 20, 2009. Also, the 2009 Summer Scripting Games are coming soon! Visit scriptingguys.com for more information.

Ed Wilson, a well-known scripting expert, is the author of eight books, including Windows PowerShell Scripting Guide (2008) and Microsoft Windows PowerShell Step by Step (2007). Ed holds more than 20 industry certifications, including Microsoft Certified Systems Engineer (MCSE) and Certified Information Systems Security Professional (CISSP). In his spare time, he enjoys woodworking, underwater photography, and scuba diving. And tea.

Craig Liebendorfer is a wordsmith and longtime Microsoft Web editor. Craig still can't believe there's a job that pays him to work with words every day. One of his favorite things is irreverent humor, so he should fit right in here. He considers his greatest accomplishment in life to be his magnificent daughter.