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.