Removing Access Records

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 that change system state have –whatif and –confirm parameters to enable us to test our actions.  The Advanced Function capability in PowerShell v2 enables us to duplicate this functionality very simply.

001
002
003
004
005
006
007
008
009
010
011
012
function Remove-AccessData {
[CmdletBinding(SupportsShouldProcess=$true)]
param (
    [string]$table,
    [string]$filter,
    [System.Data.OleDb.OleDbConnection]$connection
)
    $sql = "DELETE FROM $table WHERE $filter"
    $cmd = New-Object System.Data.OleDb.OleDbCommand($sql, $connection)
   
    if ($psCmdlet.ShouldProcess("$($connection.DataSource)", "$sql")){$cmd.ExecuteNonQuery()}
}

 

Our function uses the CmdletBinding parameter we saw in our last version of Adding a record.  This time we have added the SupportsShouldProcess attribute.

Our parameters are the table and the connection which we have seen before.  The filter parameter is the stuff in the WHERE clause of the DELETE statement without the WHERE.

As an example

Remove-AccessData -table test1 -filter "LastName = ‘Bloggs’" -connection $db

Translates to a SQL statement of

DELETE FROM test1 WHERE LastName = ‘Bloggs’

inside the function.

We create the SQL by string substitution and create the command.  Instead of directly executing the command we use the $psCmdlet.ShouldProcess() method to test if –whatif or –confirm have been used before executing the command.

These examples should show how it works.

First we’ll test that the data we want to delete actually exists

PS> Get-AccessData -sql "select * from test1 where LastName = ‘Bloggs’" -connection $db

FirstName                               LastName                                DOB
———                               ——–                                —
Jo                                  … Bloggs                              … 24/12/1980 00:00:00

 

Now we use the –whatif parameter to show us what would happen if we ran the command

PS> Remove-AccessData -table test1 -filter "LastName = ‘Bloggs’" -connection $db -whatif
What if: Performing operation "DELETE FROM test1 WHERE LastName = ‘Bloggs’" on Target "c:testtest03.mdb".

and we can confirm that the data is still there

PS> Get-AccessData -sql "select * from test1 where LastName = ‘Bloggs’" -connection $db

FirstName                               LastName                                DOB
———                               ——–                                —
Jo                                  … Bloggs                              … 24/12/1980 00:00:00

 

Now we can ask for confirmation on the delete

PS> Remove-AccessData -table test1 -filter "LastName = ‘Bloggs’" -connection $db -confirm

Confirm
Are you sure you want to perform this action?
Performing operation "DELETE FROM test1 WHERE LastName = ‘Bloggs’" on Target "c:testtest03.mdb".
[Y] Yes  [A] Yes to All  [N] No  [L] No to All  [S] Suspend  [?] Help (default is "Y"): n

We have not confirmed so the data is still present.

PS> Get-AccessData -sql "select * from test1 where LastName = ‘Bloggs’" -connection $db

FirstName                               LastName                                DOB
———                               ——–                                —
Jo                                  … Bloggs                              … 24/12/1980 00:00:00

 

This time we do confirm

PS> Remove-AccessData -table test1 -filter "LastName = ‘Bloggs’" -connection $db -confirm

Confirm
Are you sure you want to perform this action?
Performing operation "DELETE FROM test1 WHERE LastName = ‘Bloggs’" on Target "c:testtest03.mdb".
[Y] Yes  [A] Yes to All  [N] No  [L] No to All  [S] Suspend  [?] Help (default is "Y"): y
1

And the data is gone

PS> Get-AccessData -sql "select * from test1 where LastName = ‘Bloggs’" -connection $db
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
Alex                                … Green                               … 17/05/1981 00:00:00

If you are 150% sure of what you are deleting then just run the function without –whatif or –confirm otherwise use them as a final test.

The ability to add a very powerful check to our functions with minimal coding is a great bonus from PowerShell v2.  We will see this technique in more functions as we develop our access module.

About these ads
This entry was posted in Powershell. Bookmark the permalink.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s