Delete Access Table

This times lets go mad and delete a whole table.

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

Again we use the SupportsShouldProcess – have to love the amount of functionality we get for one line of code.

Parameters are our table and connection.

We create the SQL and test if we’ve called –whatif or –confirm and then perform the appropriate action.

As an example of deleting a column and a table consider this code

PS> Import-Module accessfunctions -Force
PS> $db = Open-AccessDatabase -name test03.mdb -path c:\test
PS> Remove-AccessColumn -table NewTable2 -column MyReal -connection $db -WhatIf
What if: Performing operation "ALTER TABLE NewTable2 DROP COLUMN MyReal" on Target "c:\test\test03.mdb".
PS> Remove-AccessColumn -table NewTable2 -column MyReal -connection $db
0
PS> Remove-AccessTable -table NewTable2  -connection $db -WhatIf
What if: Performing operation "DROP TABLE NewTable2 " on Target "c:\test\test03.mdb".
PS> Remove-AccessTable -table NewTable2  -connection $db
0
PS> Close-AccessDatabase $db

One thing to remember is that if you are developing module using the –Force on an import-module refreshes the functions with the latest versions.

Leave a Reply