Using PowerShell with an MS-Access Database

As promised, here is another version of the PowerShell code I said I would post for querying a Microsoft Access database without relying on COM InterOp (i.e. the ADO COM interface).  As expected, the performance is incredibly faster than with using a COM-based (ADO) connection and processing model.  I have to give credit to Lee Holmes’ example from the book PowerShell Cookbook (O’Reilly Press, ISBN: 978-0596528492)  An excerpt of the Invoke-SqlCommand.ps1 code can be found here.  Lee has more goodies at http://www.leeholmes.com/guide.

#****************************************************************
# Filename..: ms-access2.ps1
# Author....: skatterbrainz
# Date......: 05/06/09
# Purpose...: query ms-access database table, get first 10 records
# SQL.......: (path and .mdb filename)
#****************************************************************
$dataSource = "c:\Northwind.mdb"
$strQuery = "SELECT TOP 10 * FROM Transactions"

$dsn = "Provider=Microsoft.Jet.OLEDB.4.0; Data Source=$dataSource;"

## create connection object and open the database
$objConn = New-Object System.Data.OleDb.OleDbConnection $dsn
$objCmd  = New-Object System.Data.OleDb.OleDbCommand $strQuery,$objConn
$objConn.Open()

## get query results, populate data-adapter, close connection
$adapter = New-Object System.Data.OleDb.OleDbDataAdapter $objCmd
$dataset = New-Object System.Data.DataSet
[void] $adapter.Fill($dataSet)
$objConn.Close()

## display query results
$dataSet.Tables | Select-Object -Expand Rows

$dataSet = $null
$adapter = $null
$objCmd  = $null
$objConn = $null

Lee’s example code shows a more adaptable, flexible approach by allowing you to connect to multiple data source types, such as SQL Server, Office Excel and so on.  Pretty much anything ADO.NET can work with, PowerShell 2.0 can work with also.