Reading data from an Access database is similar to the functionality we have already seen.
001
002 003 004 005 006 007 008 009 010 011 012 013 014 015 016 017 |
function Get-AccessData {
param ( [string]$sql, [System.Data.OleDb.OleDbConnection]$connection, [switch]$grid ) $cmd = New-Object System.Data.OleDb.OleDbCommand($sql, $connection) $reader = $cmd.ExecuteReader() $dt = New-Object System.Data.DataTable $dt.Load($reader) if ($grid) {$dt | Out-GridView -Title "$sql" } else {$dt} } |
We can pass a SQL statement and the connection information
$dt = Get-AccessData -sql "Select * FROM test1" -connection $db
$dt | ft
in which case we get a DataTable object that we can put through a formatting cmdlet or that we could use for further processing.
if we use the –grid switch
Get-AccessData -sql "Select * FROM test1" -connection $db –grid
We are using the out-gridview cmdlet from within the function to display the data
Get-AccessData -sql "Select * FROM test1" -connection $db | out-gridview
would achieve the same goal.
By: Tony on November 26, 2009 at 7:10 pm
This is an interesting series of postings I’d like to mention in my own blog. But could you add Access to your “Filed under” so I can give folks a generic tag to search on?
Thanks, Tony Toews, Microsoft Access MVP
By: RichardSiddaway on November 27, 2009 at 3:07 am
Thanks for the feedback. I’ve added Access to the “Filed Under” tags as requested.
If there is anything you would like covered in this series let me know.
I will eventually publish all the functions as a PowerShell module for download
By: Tony on November 28, 2009 at 12:56 am
Thanks muchly. I’ve blogged this as well so others will ssee this. I was rather startled to see your first posting on PowerShell and Access. I had no idea.