Categories

Reading Access records

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.

3 Responses to Reading Access records

  • Tony says:

    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

  • RichardSiddaway says:

    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

  • Tony says:

    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.

Leave a Reply