header image

Add a column to an Access Table

Posted by: | December 10, 2009 | No Comment |

Now we have created our Table we can start adding columns

001
002
003
004
005
006
007
008
009
010
011
012
013
014
015
016
017
018
019
020
021
022
023
024
025
026
027
028
029
030
031
032
033
034
035
036
037
038
039
040
041
042
043
044
045
046
047
048
049
050
051
052
053
054
055
056
057
058
059
060
061
062
063
064
065
066
067
068
069
070
071
072
073
074
075
076
077
078
079
080
081
082
083
084
085
086
function New-AccessColumn {
[CmdletBinding()]
param (
    [System.Data.OleDb.OleDbConnection]$connection,
    [string]$table,
    [switch]$notnull,

    [parameter(ParameterSetName="datetime")] 
    [string]$dtname,

    [parameter(ParameterSetName="unique")] 
    [string]$uniquename,

    [parameter(ParameterSetName="binary")] 
    [string]$binname,

    [parameter(ParameterSetName="bit")] 
    [string]$bitname,

    [parameter(ParameterSetName="tinyinteger")] 
    [string]$tnyintname,

    [parameter(ParameterSetName="smallinteger")] 
    [string]$smlintname,
  
    [parameter(ParameterSetName="integer")] 
    [string]$intname,

    [parameter(ParameterSetName="double")]  
    [string]$dblname,

    [parameter(ParameterSetName="real")] 
    [string]$realname,

    [parameter(ParameterSetName="float")] 
    [string]$floatname,
   
    [parameter(ParameterSetName="decimal")] 
    [string]$decname,
   
    [parameter(ParameterSetName="money")] 
    [string]$mnyname,
   
    [parameter(ParameterSetName="char")] 
    [string]$charname,
   
    [parameter(ParameterSetName="text")] 
    [string]$textname,

    [parameter(ParameterSetName="image")] 
    [string]$imgname,
   
    [parameter(ParameterSetName="char")]
    [parameter(ParameterSetName="text")] 
    [int]$size = 10
)   
    switch ($psCmdlet.ParameterSetName){
        datetime     {$sql = "ALTER TABLE $table ADD COLUMN $dtname DATETIME" } 

        binary       {$sql = "ALTER TABLE $table ADD COLUMN $binname BINARY" } 
        bit          {$sql = "ALTER TABLE $table ADD COLUMN $bitname BIT" } 
       
        unique       {$sql = "ALTER TABLE $table ADD COLUMN $uniquename UNIQUEIDENTIFIER" } 

        tinyinteger  {$sql = "ALTER TABLE $table ADD COLUMN $tnyintname TINYINT" } 
        smallinteger {$sql = "ALTER TABLE $table ADD COLUMN $smlintname SMALLINT" } 
        integer      {$sql = "ALTER TABLE $table ADD COLUMN $intname INTEGER" } 

        double       {$sql = "ALTER TABLE $table ADD COLUMN $dblname DOUBLE" } 
        float        {$sql = "ALTER TABLE $table ADD COLUMN $floatname FLOAT" } 
        real         {$sql = "ALTER TABLE $table ADD COLUMN $realname REAL" } 
        decimal      {$sql = "ALTER TABLE $table ADD COLUMN $decname DECIMAL" } 
        money        {$sql = "ALTER TABLE $table ADD COLUMN $mnyname MONEY" } 
       
        char         {$sql = "ALTER TABLE $table ADD COLUMN $charname CHARACTER($size)" }
        text         {$sql = "ALTER TABLE $table ADD COLUMN $textname TEXT($size)" }
        image        {$sql = "ALTER TABLE $table ADD COLUMN $imgname IMAGE" }                
               
    }
    if ($notnull) {$sql = $sql + " NOT NULL"}
   
    Write-Debug $sql
    $cmd = New-Object System.Data.OleDb.OleDbCommand($sql, $connection)
    $cmd.ExecuteNonQuery()
}

 

The first set of parameters define the connection and table name. The $notnull parameter adds the NOT NULL statement to the SQL we create. This forces us to add a value for the column when we insert a row. These parameters are not part of a parameter set – this means they work with all parameter sets.

Parameter sets are used in this function to make the data types mutually exclusive – i.e. one call to Add-AccessColumn can add one column of one, and only one, data type. The size parameter applies to character and text data types so is a member of both parameter sets.

The parameter set is used in a switch statement to create the SQL to add a column of the correct type. If the $notnull switch is set we add the appropriate commands to the SQL.

With advanced functions in PowerShell v2 we get the ability to use the common parameters so I can use

New-AccessColumn -connection $db -table $table -dtname Mydate2 –notnull  -Debug

and the Write-Debug statements will be triggered. In this case I get a dump of the SQL I have created. The function finishes by executing the SQL against the database as we have seen before.

As an example of using the function

001
002
003
004
005
006
007
008
009
010
011
012
013
014
015
016
017
018
019
020
021
022
023
024
025
026
027
028
029
030
031
032
033
034
035
036
037
038
039
040
041
042
043
044
045
046
047
048
049
Import-Module accessfunctions -Force
$db = Open-AccessDatabase -name test03.mdb -path c:\test

$table = "NewTable2"
New-AccessTable -table $table -connection $db

New-AccessColumn -connection $db -table $table -dtname Mydate
New-AccessColumn -connection $db -table $table -dtname Mydate2 -notnull 

New-AccessColumn -connection $db -table $table -uniquename MyUnique
New-AccessColumn -connection $db -table $table -uniquename MyUnique2 -notnull

New-AccessColumn -connection $db -table $table -binname MyBinary
New-AccessColumn -connection $db -table $table -bitname MyBit
New-AccessColumn -connection $db -table $table -binname MyBinary2 -notnull
New-AccessColumn -connection $db -table $table -bitname MyBit2 -notnull

New-AccessColumn -connection $db -table $table -tnyintname MyTiny
New-AccessColumn -connection $db -table $table -smlintname MySmall
New-AccessColumn -connection $db -table $table -intname MyInt

New-AccessColumn -connection $db -table $table -tnyintname MyTiny2 -notnull
New-AccessColumn -connection $db -table $table -smlintname MySmall2 -notnull
New-AccessColumn -connection $db -table $table -intname MyInt2 -notnull

New-AccessColumn -connection $db -table $table -dblname MyDouble
New-AccessColumn -connection $db -table $table -realname MyReal
New-AccessColumn -connection $db -table $table -floatname MyFloat
New-AccessColumn -connection $db -table $table -decname MyDecimal
New-AccessColumn -connection $db -table $table -mnyname MyMoney

New-AccessColumn -connection $db -table $table -dblname MyDouble2 -notnull
New-AccessColumn -connection $db -table $table -realname MyReal2 -notnull
New-AccessColumn -connection $db -table $table -floatname MyFloat2 -notnull
New-AccessColumn -connection $db -table $table -decname MyDecimal2 -notnull
New-AccessColumn -connection $db -table $table -mnyname MyMoney2 -notnull

New-AccessColumn -connection $db -table $table -charname MyChar
New-AccessColumn -connection $db -table $table -charname MyChar2 -size 20
New-AccessColumn -connection $db -table $table -charname MyChar3 -size 20 -notnull

New-AccessColumn -connection $db -table $table -textname MyText
New-AccessColumn -connection $db -table $table -textname MyText2 -size 20
New-AccessColumn -connection $db -table $table -textname MyText3 -size 20 -notnull

New-AccessColumn -connection $db -table $table -imgname MyImg 
New-AccessColumn -connection $db -table $table -imgname MyImg2  -notnull

Close-AccessDatabase $db

 

Producing this becomes easy when using cut and paste within an editor.

Next we will look at removing columns and tables

under: Office 2010, PowerShell original, PowerShellV2
«
»