Richard Siddaway's Blog

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 can more or less ignore this because your standard formats work OK. If I run […]

The last of of our data manipulation tasks is to update the data – we have already seen how to create, read and delete. 001 002 003 004 005 006 007 008 009 010 011 012 013 function Set-AccessData { [CmdletBinding(SupportsShouldProcess=$true)] param (     [string]$table,     [string]$filter,     [string]$value,     [System.Data.OleDb.OleDbConnection]$connection )     $sql = "UPDATE $table SET $value WHERE […]

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 used to test the connection before we use it.  All we do is test the […]

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 – ideally we want to a mechanism to check what we are doing.  The PowerShell cmdlets […]

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 sets NOTE – This is a PowerShell v2 capability. 001 002 003 004 005 006 […]

We already have all the functionality we need to achieve this. Import-Module accessfunctions $db = Open-AccessDatabase -name test03.mdb -path c:\test Get-AccessData -sql "select * from test1" -connection $db | Export-Csv -Path c:\test\test1.csv –NoTypeInformation Open the csv file in Excel and the data is available.  If you open the csv file in notepad be aware that […]

I found twenty comments this morning – all adverts for things I don’t want – plastered over recent posts.  I don’t want to spend my time cleaning off inappropriate material from my blog so I have suspended comments on posts on this blog until further notice. If you want to leave a comment all posts, […]

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" }     […]

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. 001 002 003 004 005 006 007 008 009 010 011 012 function Add-AccessRecord { param (     [string]$sql, […]

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 can be set to a number between 1 and 56.  Use –4142 if you don’t […]