Monthly Archives: November 2009

Following on from the previous post about updating records one data type that will be a little awkward to work with is dates. If you use a US locale or any other that uses a date format of Month/Day/Year you … Continue reading →

The last of of our data manipulation tasks is to update the data – we have already seen how to create, read and delete. 001002003004005006007008009010011012013 function Set-AccessData {[CmdletBinding(SupportsShouldProcess=$true)]param (    [string]$table,    [string]$filter,    [string]$value,    [System.Data.OleDb.OleDbConnection]$connection)    $sql = "UPDATE $table SET $value WHERE $filter"    $cmd = New-Object System.Data.OleDb.OleDbCommand($sql, $connection)        if ($psCmdlet.ShouldProcess("$($connection.DataSource)", "$sql")){$cmd.ExecuteNonQuery()}}   … Continue reading →

Many of the functions we have created so far have taken a connection to an Access database as a parameter.  At the time we pass in the connection we don’t actually know if the connection is open. Test-AccessConnection can be … Continue reading →

So far we have seen how to add data to a table in an access database – now we want to delete some records.  This is an action that can cause problems especially if we get the wrong records – … Continue reading →

Last time we added the option of inputting the table and values to our function but we needed a way to discriminate between that and using a full SQL statement.  We can achieve this by dividing the parameters into parameter … Continue reading →

We already have all the functionality we need to achieve this. Import-Module accessfunctions$db = Open-AccessDatabase -name test03.mdb -path c:testGet-AccessData -sql "select * from test1" -connection $db | Export-Csv -Path c:testtest1.csv –NoTypeInformation   Open the csv file in Excel and the … Continue reading →

Reading data from an Access database is similar to the functionality we have already seen. 001002003004005006007008009010011012013014015016017 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 … Continue reading →

We have seen how to add a record to an Access table by passing in the whole SQL string.  This is OK when we want to add a single record or possibly not fill all fields in a row. 001002003004005006007008009010011012 … Continue reading →

I needed to set the background colour of a cell in an Excel spreadsheet recently.  The way to do it is to set the ColorIndex property of the Interior properties of the cell as shown in line 14.  The ColorIndex … Continue reading →