Categories

Updating Access data

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 $filter"
    $cmd = New-Object System.Data.OleDb.OleDbCommand($sql, $connection)
   
    if ($psCmdlet.ShouldProcess("$($connection.DataSource)", "$sql")){$cmd.ExecuteNonQuery()}
}

 

This function is very similar to the delete function. We have an additional parameter to pass in the values we are updating.

PS> Import-Module accessfunctions
PS> $db = Open-AccessDatabase -name test03.mdb -path c:\test
PS> Get-AccessData -sql "select * from test1" -connection $db

FirstName                          LastName                           DOB
---------                          --------                           ---
Fred                           ... Smith                          ... 01/09/1979 00:00:00
John                           ... Smith                          ... 01/07/1981 00:00:00
James                          ... Jones                          ... 18/04/1981 00:00:00

 

PS> Set-AccessData -table test1 -filter "LastName='Smith'" -value "LastName='Green'" -connection $db
2
PS> Get-AccessData -sql "select * from test1" -connection $db

FirstName                          LastName                           DOB
---------                          --------                           ---
Fred                           ... Green                          ... 01/09/1979 00:00:00
John                           ... Green                          ... 01/07/1981 00:00:00
James                          ... Jones                          ... 18/04/1981 00:00:00

Leave a Reply

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>