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