Hey, Scripting Guy! Can I Query a Microsoft Access Database with a Windows PowerShell Script?

Hey, Scripting Guy! Can I Query a Microsoft Access Database with a Windows PowerShell Script?

  • Comments 8
  • Likes

 

Hey, Scripting Guy! Question

Hey Scripting Guy! I have a Microsoft Access database that I need to query from a Windows PowerShell script. I have seen a number of VBScript examples, but I cannot find any Windows PowerShell samples. Can you point me in the right direction? I do not want you to write it for me, but I would like a hint.

-- KS

Hey, Scripting Guy! Answer

 Hello KS,

Microsoft Scripting Guy Ed Wilson here. Give me a sextant, a chart, and a sharp pencil and I can navigate across the Atlantic Ocean. But the other day, I got lost coming home from the office. This is because for the last five years I traveled around the world and spent more time outside the United States than inside it. Luckily, my Windows Mobile 6.5 phone has GPS built into it, and I was able to pull up my home address from my contact list and find my way home. So I can completely sympathize with wanting a nudge in the right direction.

This week we will be reviewing some of the scripts that were submitted during the recently held 2009 Summer Scripting Games. The description of the 2009 Summer Scripting Games details all of the events. Each of the events was answered by a globally recognized expert in the field. There were some cool prizes and winners were recognized from around the world. Additionally, just like at the "real Olympics," because there was a lot going on, an "if you get lost page" was created. Communication with participants was maintained via Twitter, Facebook, and a special forum. The special forum has been taken down, but we still use Twitter and Facebook to communicate with Hey, Scripting Guy! fans. We will be focusing on solutions that used Windows PowerShell. We have several good introduction to Windows PowerShell Hey, Scripting Guy! articles that you will find helpful.

I was recently spelunking over at PoshCode and perusing the 2009 Summer Scripting Games entries, and I ran across an excellent script by Peter Sukus that was submitted for the Beginner Event 4 High Jump event. If you run Peter's script that you can copy from PoshCode, an error will be generated. This is seen here:

Image of error generated by running Peter's script


Because the script is cool and is easily fixed, I thought I would select it for our example. The error states that the object does not have a method named MoveRecordFirst. This error is true; the ADO RecordSet object contains a method named MoveFirst and another method named MoveNext. Replacing the errant line of code resolves the error and results in a fine Windows PowerShell example of a classic ADO script.

The ScriptingGamesBeginnerEvent4.ps1 script begins by assigning the path to the HighJumperDatabase.mdb file and defining two variables that are used by ADO to control the way the database is opened. This is seen here:

$path = "C:\data\ScriptingGuys\HSG_8_10_09\HighJumperDatabase.mdb"
$adOpenStatic = 3
$adLockOptimistic = 3

Two objects are used to work with the Microsoft Access database. The first is a connection object and the second is a recordset object. The New-Object cmdlet is used to create these objects. This is seen here:

$cn = new-object -comobject ADODB.Connection
$rs = new-object -comobject ADODB.Recordset

The next line of code uses the open method from the connection object to specify the provider that is to be used to read the database. This is seen here:

$cn.Open("Provider = Microsoft.Jet.OLEDB.4.0;Data Source = $path")

To know which tables to query and which records to select requires knowledge of the database. The High Jumper database is seen here.

Image of The High Jumper database


Most of the work is done by using a SQL select statement, which is supplied to the Open method of the RecordSet object. The table and the field are placed inside square brackets in a dotted notation. The Top keyword chooses the number 1 match. The From keyword specifies the table name, which was discovered when the database was opened in Microsoft Access. Order By determines the way the data will be returned. The Desc command is used to sort the list in descending order. The RecordSet Open command is seen here:

$rs.Open("SELECT TOP 1 [High Jumper Data].[Name],
  [High Jumper Data].[Personal Best], [High Jumper Data].[Season Best]
  FROM [High Jumper Data]
  ORDER BY [High Jumper Data].[Personal Best]
  DESC , [High Jumper Data].[Season Best] DESC",
  $cn, $adOpenStatic, $adLockOptimistic)

After moving to the first record in the RecordSet, the results are displayed by using the Write-Host cmdlet. The RecordSet object has a Fields property that has an Item method that is used to choose the field by name. The value of the property is displayed. This is seen here:

$rs.MoveFirst()
Write-host "The winner will likely be " $rs.Fields.Item("Name").Value

The complete ScriptingGamesBeginnerEvent4.ps1 script is seen here.

ScriptingGamesBeginnerEvent4.ps1

$path = "C:\data\ScriptingGuys\HSG_8_10_09\HighJumperDatabase.mdb"
$adOpenStatic = 3
$adLockOptimistic = 3

$cn = new-object -comobject ADODB.Connection
$rs = new-object -comobject ADODB.Recordset

$cn.Open("Provider = Microsoft.Jet.OLEDB.4.0;Data Source = $path")
$rs.Open("SELECT TOP 1 [High Jumper Data].[Name],
  [High Jumper Data].[Personal Best], [High Jumper Data].[Season Best]
  FROM [High Jumper Data]
  ORDER BY [High Jumper Data].[Personal Best]
  DESC , [High Jumper Data].[Season Best] DESC",
  $cn, $adOpenStatic, $adLockOptimistic)

$rs.MoveFirst()
Write-host "The winner will likely be " $rs.Fields.Item("Name").Value

 

LKH also contributed an excellent script. This is one of the things that make me glad the 2009 Summer Olympics were not about competition; I would not want to have to make a decision between the two scripts. They are both awesome contributions.

The ScriptingGamesBeginnerEvent4-lkh.ps1 script uses a similar SQL query. You may want to compare the query with the one from the previous script. Here is the LKH query:

$strQuery = "SELECT TOP 1
 [High Jumper Data].Name ,[High Jumper Data].[Country] ,[High Jumper Data].[Personal Best]
 FROM [High Jumper Data]
 ORDER BY [Personal Best] DESC ;"

LKH next specifies the provider and the data source for the ADO connection. One of the cool things about this script is the use of the $PWD automatic variable. You can think of it as standing for PowerShell Working Directory. The connection string is seen here:

$strConn = "Provider=Microsoft.Jet.OLEDB.4.0; Data Source=$PWD\HighJumperDatabase.mdb"

The LKH script uses ADO.NET instead of classic COM ADO. The two objects that are created are a connection object and a command object. These are seen here:

$oConn = New-Object System.Data.OleDb.OleDbConnection $strConn
$oCmd  = New-Object System.Data.OleDb.OleDbCommand($strQuery, $oConn)

After the two objects have been created, the Open method is used to open the connection to the database and the ExecuteReader method from the Command object. This is seen here:

$oConn.Open()
$oReader = $oCmd.ExecuteReader()

Another interesting feature of the ScriptingGamesBeginnerEvent4-lkh.ps1 script is the way LKH creates a custom object to display the results of the SQL query. To do this, LKH first uses the New-Object cmdlet to create an instance of a PSObject. The newly created PSObject is stored in the variable $oJumper. This is seen here:

$oJumper = New-Object PSObject

When the new PSObject is created, three properties are added to it and they correspond to the three properties that were selected from the Microsoft Access database. The thing that is a bit unusual is the way the properties are added to the PSObject: They are added in the pipeline. This section of the script is shown here:

$oJumper = New-Object PSObject
$oJumper | Add-Member NoteProperty Name     $oReader[0]
$oJumper | Add-Member NoteProperty Country  $oReader[1]
$oJumper | Add-Member NoteProperty PBest    $oReader[2]

The last thing that is done is to display the PSObject contained in the $oJumper variable and to close both the connection object and the connection object. Because the data that was returned from the SQL query is contained in an object, Windows PowerShell automatically displays all of the properties from the object when it is called. This is seen here:

$oJumper
$oReader.Close()
$oConn.Close()

 

ScriptingGamesBeginnerEvent4-lkh.ps1

$strQuery = "SELECT TOP 1
 [High Jumper Data].Name ,[High Jumper Data].[Country] ,[High Jumper Data].[Personal Best]
 FROM [High Jumper Data]
 ORDER BY [Personal Best] DESC ;"

$strConn = "Provider=Microsoft.Jet.OLEDB.4.0; Data Source=$PWD\HighJumperDatabase.mdb"
$oConn = New-Object System.Data.OleDb.OleDbConnection $strConn
$oCmd  = New-Object System.Data.OleDb.OleDbCommand($strQuery, $oConn)
$oConn.Open()
$oReader = $oCmd.ExecuteReader()
[void]$oReader.Read()
    $oJumper = New-Object PSObject
    $oJumper | Add-Member NoteProperty Name     $oReader[0]
    $oJumper | Add-Member NoteProperty Country  $oReader[1]
    $oJumper | Add-Member NoteProperty PBest    $oReader[2]
    $oJumper
$oReader.Close()
$oConn.Close()

When you run the LKH script, this is the output you receive:

Out put of the LHK script

 

Well, KS, we have shown you two different ways of querying a Microsoft Access database. Both solutions were submitted during the 2009 Summer Scripting Games. The first script, submitted by Peter Sukus, used classic COM-based ADO, and the second submitted by LKH used ADO.NET. Both methods work and both are supported from within Windows PowerShell.

If you want to be the first to know what is happening on the Script Center, follow us on Twitter or on Facebook. If you need assistance with a script, you can post questions to the Official Scripting Guys Forum, or send an e-mail to scripter@microsoft.com. The 2009 Summer Scripting Games wrap-up will continue tomorrow. Until then, peace.

Ed Wilson and Craig Liebendorfer, Scripting Guys

Your comment has been posted.   Close
Thank you, your comment requires moderation so it may take a while to appear.   Close
Leave a Comment
  • Challenge!  Write a powershell script that will take a list of MS-Access databases and for each one, extract the stored procedures (queries?) and table definitions and write them to text files.

  • Work with ms access databases by using of repair acces file accdb

    www.repairaccdb.accessrepairtoolbox.com

  • Great Examples ! Thanks for sharing them!

  • So i have a version of the 2nd code sample set up. But it doesn't seem to be giving me any output it just spits out a blank line, in powershell.

    -G

  • repair damage ms access databases with the help of mdb repair

    www.mdbfile.com

  • An advance or effective access recovery software, you can use Kernel for Access Recovery Tool. It can efficient to repair damage damaged MDB/ACCDB files without any hassles. http://www.accessrecovery.mdbfile.com/

  • Expert to recover and repair of Access file. The software supports with all version of MS Access. More details visit this site:- http://www.filesrecoverytool.com/access-file-recovery.html

  • It will automatically find, undelete and recover deleted and damaged Access databases as well as MDB and ACCDB files stored on formatted disks and flash drives.To get more relevant info and FREE trail of this software visit here:- http://www.recoverfilesdownload.com/access-file-recovery.html