Richard Siddaway's Blog : Powershell

Add a column to an Access Table

RichardSiddaway — Thu, 10 Dec 2009 17:36:44 GMT

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

Technorati Tags: PowerShell,Office 2010,Access,Add column

New Access Table

RichardSiddaway — Thu, 10 Dec 2009 17:10:51 GMT

I have already presented a function to create a new access table but I wasn’t very happy with it because we had to supply the full table creation SQL script.

I have altered that function so it creates an empty table. We can then use the Add-AccessColumn function to add columns. This also means I have a function to add columns to a table as and when I need them.

The function to create a table becomes

            001              
002               
003               
004               
005               
006               
007               
008               
009               
010               
          function New-AccessTable {               
## assumes database is open               
param (               
    [string]$table,               
    [System.Data.OleDb.OleDbConnection]$connection               
)               
    $sql = " CREATE TABLE $table"               
    $cmd = New-Object System.Data.OleDb.OleDbCommand($sql, $connection)               
    $cmd.ExecuteNonQuery()               
} 
       
 

Our parameters become the connection and a table name. I don’t check that the table name exists – that will be a future refinement. We then issue a simple CREATE TABLE command.

Technorati Tags: PowerShell,Office 2010,Access,Create Table

Creating Objects

RichardSiddaway — Sat, 05 Dec 2009 14:45:05 GMT

There is a post on the PowerShell Team blog about using New-Object - http://blogs.msdn.com/powershell/archive/2009/12/05/new-object-psobject-property-hashtable.aspx

The –property parameter discussed in this post is something that I had only come across recently. In a number of recent posts I have used Add-Type to create a new object by using C# code to define a new class rather than using New-Object and Add-Member. The post on the team blog, plus questions I’d been asked about why I used add-type got me thinking about creating objects.

In PowerShell v1 we used to do this

            001              
002               
003               
          $new1 = New-Object -TypeName PSObject               
$new1 | Add-Member -Name p1 -Value 1 -MemberType NoteProperty -PassThru |               
        Add-Member -Name p2 -Value "a" -MemberType NoteProperty 
       
 

We would create an object and then use Add-Member to add the properties. The object could then be used

            001              
002               
003               
004               
          $new1 | ft -AutoSize               
              
$new1.p1 = "b"               
$new1 | ft -AutoSize 
       
 

This would give the results we expect

p1 p2
-- --
1 a

p1 p2
-- --
b a

Notice that we have been able to change the type of p1 from an integer to a string.

In v2 we get the –property parameter on New-Object

            001              
002               
003               
004               
005               
          $p = @{               
    p1 = 1               
    p2 = "a"               
}               
$new2 = New-Object -TypeName PSObject -Property $p 
       
 

In this method we create a hash table holding the properties and the values. It can be used when we create the object so that the properties are immediately populated. This is a neater method than the multiple calls to Add-Member we saw earlier.

The object can be used as before

            001              
002               
003               
004               
          $new2 | ft -AutoSize               
$new2.p1 = "b"               
              
$new2 | ft -AutoSize 
       
 

with the following results

p2 p1
-- --
a 1

p2 p1
-- --
a b

Again notice that we can change the type of p1.

If we want to use a PSObject and create properties then using a hash table in v2 involves less typing, looks neater and is probably easier to understand.

In a number of recent posts I have been creating a C# class rather than using PSObject. This is a better variant of that technique showed to me by Doug Finke (thanks Doug)

            001              
002               
003               
004               
005               
006               
007               
008               
009               
010               
011               
012               
013               
          $code = @"                
public class testobject {                 
    public int p1 {get; set;}                 
    public string p2 {get; set;}                 
}                 
"@               
Add-Type -TypeDefinition $code -Language CSharpversion3               
$p = @{               
    p1 = 1               
    p2 = "a"               
}               
              
$new3 = New-Object -TypeName testobject -Property $p 
       
 

I have created a simple .NET class with our two properties. Add-Type is used to add the definition into PowerShell. I can then create an object using a hash table for the properties. This probably looks more complicated but there is a significant difference when we come to use it

            001              
002               
003               
004               
          $new3 | ft -AutoSize               
$new3.p1 = "b"               
              
$new3 | ft -AutoSize 
       
 

gives us

p1 p2
-- --
1 a

Exception setting "p1": "Cannot convert value "b" to type "System.Int32". Error: "Input string was not in a correct format.""
At line:16 char:7
+ $new3. <<<< p1 = "b"
+ CategoryInfo : InvalidOperation: (:) [], RuntimeException
+ FullyQualifiedErrorId : PropertyAssignmentException

p1 p2
-- --
1 a

We can’t change the type of p1 because it is defined by the class we created. This gives me an extra level of protection on what I’m doing as I can’t make a mistake and set the property to the wrong type.

PowerShell often gives us a number of ways of achieving the goal. In this case choose the one that best fits your requirements.

Technorati Tags: PowerShell,Objects

Access Bulk Load data

RichardSiddaway — Fri, 04 Dec 2009 11:40:53 GMT

We have already seen how to load individual records into an Access Table. Sometime we require the ability to add multiple records. We can easily adapt the way we use our Add-AccessRecord function to accommodate a bulk load scenario.

Lets create a csv file with the information

FirstName,LastName,DOB
Dave,Brown,15/06/1982
Jo,Black,27/07/1982
Alex,White,30/06/1982
Bill,Smith,17/07/1982

We can then write a script to read the csv file and add the data

            001              
002               
003               
004               
005               
006               
007               
008               
009               
010               
011               
          Import-Module AccessFunctions               
$db = Open-AccessDatabase -name test03.mdb -path c:\test               
Import-Csv -Path c:\test\names.csv | foreach {               
    $value = " ""$($_.FirstName)"", ""$($_.LastName)"", ""$($_.DOB)"" "               
    $value               
    Add-AccessRecord -connection $db -table test1 -values $value               
}               
              
Get-AccessData -sql "select * from test1" -connection $db -grid               
Close-AccessDatabase $db               
Remove-Module AccessFunctions 
       
 

Start by importing the accessfunctions module and open the database.

use Import-Csv ro read the data file. Pipe the data into a foreach where we concatenate the values to give a single string. I’m using string substitution to achieve this. Notice the use of “” round the data values. This is to ensure that we get a each value wrapped as “value” when it is passed into the function.

Once we have added the data we can use the Get-AccessData function to check our data has been inserted.

Final actions are to close the database and remove the module

Technorati Tags: PowerShell,office 2010,Access,Bulk load

Working with Access dates

RichardSiddaway — Mon, 30 Nov 2009 11:18:08 GMT

Following on from the previous post about updating records one data type that will be a little awkward to work with is dates.

If you use a US locale or any other that uses a date format of Month/Day/Year you can more or less ignore this because your standard formats work OK.

If I run

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

In the first record the date is first of September 1979 because I am using a UK date format of day/month/year

if I try to query on that date

PS> Get-AccessData -sql "select * from test1 where DOB = #01/09/1979#" -connection $db

I don’t get anything returned but if I use

PS> Get-AccessData -sql "select * from test1 where DOB = #09/01/1979#" -connection $db

FirstName                         LastName                          DOB
---------                         --------                          ---
Fred                          ... Green                         ... 01/09/1979 00:00:00

I get the data I expect.

This is a PowerShell issue.

PS> $host

Name             : ConsoleHost
Version          : 2.0
InstanceId       : 98b88ef9-297c-46a8-bc2c-8cbb4cb273df
UI               : System.Management.Automation.Internal.Host.InternalHostUserInterface
CurrentCulture   : en-GB
CurrentUICulture : en-US
PrivateData      : Microsoft.PowerShell.ConsoleHost+ConsoleColorProxy
IsRunspacePushed : False
Runspace         : System.Management.Automation.Runspaces.LocalRunspace

Notice that the CurrentUICulture is en-US but my CurrentCulture is en-GB. This means that when I input dates at the PowerShell prompt or in scripts they will be interpreted following the US format.

My recommendation for dealing with this is to use this format

PS> Get-AccessData -sql "select * from test1 where DOB = #01-Sep-79#" -connection $db

This removes the ambiguity and leaves us able to apply the same commands anywhere (at least if you are using English :-))

Our update command then becomes

Set-AccessData -table test1 -filter "DOB = #01-Sep-1979#" -value "DOB = #02-Sep-1979#" -connection $db

Technorati Tags: PowerShell,Office 2010,Access,dates

Updating Access data

RichardSiddaway — Mon, 30 Nov 2009 10:58:15 GMT

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

Technorati Tags: PowerShell,office 2010,Access,update

Testing Connection to Access database

RichardSiddaway — Sun, 29 Nov 2009 20:46:31 GMT

Many of the functions we have created so far have taken a connection to an Access database as a parameter. At the time we pass in the connection we don’t actually know if the connection is open. Test-AccessConnection can be used to test the connection before we use it. All we do is test the value of the state property on the connection object. if it is Open we return true other wise false. Only values at present are open and closed.

            001              
002               
003               
004               
005               
006               
007               
008               
          function Test-AccessConnection {               
param (               
    [System.Data.OleDb.OleDbConnection]$connection               
)                 
    if ($connection.State -eq "Open"){$open = $true}               
    else {$open = $false}               
    $open                  
} 
       
 

We can use the Test-AccessConnection function like this

PS> Import-Module accessfunctions
PS> $db = Open-AccessDatabase -name test03.mdb -path c:\test
PS> if (Test-AccessConnection $db){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> Close-AccessDatabase $db
PS> if (Test-AccessConnection $db){Get-AccessData -sql "select * from test1" -connection $db}else{Write-Host "Database Closed"}
Database Closed
PS>

Use the Test-AccessConnection function in an if statement. If $true is returned we can execute our command. This could be extended to give us a message if the connection is closed.

Technorati Tags: PowerShell,Office 2010,Access,test,connection

Removing Access Records

RichardSiddaway — Sun, 29 Nov 2009 11:18:00 GMT

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:\test\test03.mdb".

and we can confirm that the data is still there

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

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:\test\test03.mdb".
(Y) Yes (A) Yes to All (N) No (L) No to All (S) Suspend [?] Help (default is "Y"): n

note – all () in above line should be []

We have not confirmed so the data is still present.

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

This time we do confirm

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

note – all () in above line should be []

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.

Technorati Tags: PowerShell,Office 2010,Access,delete record,shouldprocess,pscmdlet

Add Access Record Pt III – parameter sets

RichardSiddaway — Fri, 27 Nov 2009 16:33:01 GMT

Last time we added the option of inputting the table and values to our function but we needed a way to discriminate between that and using a full SQL statement. We can achieve this by dividing the parameters into parameter sets NOTE – This is a PowerShell v2 capability.

            001              
002               
003               
004               
005               
006               
007               
008               
009               
010               
011               
012               
013               
014               
015               
016               
017               
018               
019               
020               
021               
          function Add-AccessRecord {               
[CmdletBinding()]               
param (               
    [parameter(ParameterSetName="sql")]               
    [string]$sql,               
                  
    [System.Data.OleDb.OleDbConnection]$connection,               
                  
    [parameter(ParameterSetName="value")]               
    [string]$table,               
                  
    [parameter(ParameterSetName="value")]               
    [string]$values               
)               
    if($psCmdlet.ParameterSetName -eq "value"){               
        $sql = "INSERT INTO $table VALUES ($values)"               
    }               
                  
    $cmd = New-Object System.Data.OleDb.OleDbCommand($sql, $connection)               
    $cmd.ExecuteNonQuery()               
} 
       
 

We add the [CmdletBinding()] parameter and on the sql,varaible we add a parameter set name of “sql”. The table and values parameters are put into a parameter set of “value”. The connection parameter is not explicitly placed into either parameter set which means it is in both!

We can then check the ParameterSetName property and create the $sql variable if required. The function can be used as follows

Import-Module accessfunctions
$db = Open-AccessDatabase -name test03.mdb -path c:\test

Add-AccessRecord -sql 'insert into test1 values("James","Jones","18/04/1981")' -connection $db

Add-AccessRecord -connection $db -table test1 -values '"Alex","Green","17/05/1981"'

In these two examples we have restricted ourselves to one or the other of the parameter sets. if we try to mix parameters across the parameter sets

PS> Add-AccessRecord -sql 'insert into test1 values("Rab","Burns","08/06/1981")' -connection $db -table test1
Add-AccessRecord : Parameter set cannot be resolved using the specified named parameters.
At line:1 char:17
+ Add-AccessRecord <<<< -sql 'insert into test1 values("Rab","Burns","08/06/1981")' -connection $db -table test1
+ CategoryInfo : InvalidArgument: (:) [Add-AccessRecord], ParameterBindingException
+ FullyQualifiedErrorId : AmbiguousParameterSet,Add-AccessRecord

we get an error message.

We now have a function that allows us to work in the most efficient way at a particular moment. Next we’ll see how we can feed the values from a csv file.

Technorati Tags: PowerShell,Office 2010,Access,Add record,parameter sets

Export Access data to csv file

RichardSiddaway — Fri, 27 Nov 2009 11:56:32 GMT

We already have all the functionality we need to achieve this.

Import-Module accessfunctions
$db = Open-AccessDatabase -name test03.mdb -path c:\test
Get-AccessData -sql "select * from test1" -connection $db | Export-Csv -Path c:\test\test1.csv –NoTypeInformation

Open the csv file in Excel and the data is available. If you open the csv file in notepad be aware that the fields are padded and you need to scroll to the right to see all the data.

We could add the export functionality into our Get-AccessData function but if we stick with the PowerShell composability concepts we want to keep the functions small and focussed. If you want to change the function – feel free – just add a switch for csv output and a parameter for the csv path. Then use another if statement to control writing to the csv.

Technorati Tags: Powershell,Office 2010,Access,csv file

Reading Access records

RichardSiddaway — Thu, 26 Nov 2009 21:17:56 GMT

Reading data from an Access database is similar to the functionality we have already seen.

            001              
002               
003               
004               
005               
006               
007               
008               
009               
010               
011               
012               
013               
014               
015               
016               
017               
          function Get-AccessData {               
param (               
    [string]$sql,               
    [System.Data.OleDb.OleDbConnection]$connection,               
    [switch]$grid               
)               
                  
    $cmd = New-Object System.Data.OleDb.OleDbCommand($sql, $connection)               
    $reader = $cmd.ExecuteReader()               
                  
    $dt = New-Object System.Data.DataTable               
    $dt.Load($reader)               
                  
    if ($grid) {$dt | Out-GridView -Title "$sql" }               
    else {$dt}               
              
} 
       
 

We can pass a SQL statement and the connection information

$dt = Get-AccessData -sql "Select * FROM test1" -connection $db
$dt | ft

in which case we get a DataTable object that we can put through a formatting cmdlet or that we could use for further processing.

if we use the –grid switch

Get-AccessData -sql "Select * FROM test1" -connection $db –grid

We are using the out-gridview cmdlet from within the function to display the data

Get-AccessData -sql "Select * FROM test1" -connection $db | out-gridview

would achieve the same goal.

Technorati Tags: PowerShell,office 2010,Access,Read data

Add Access Record PtII

RichardSiddaway — Thu, 26 Nov 2009 20:52:31 GMT

We have seen how to add a record to an Access table by passing in the whole SQL string. This is OK when we want to add a single record or possibly not fill all fields in a row.

            001              
002               
003               
004               
005               
006               
007               
008               
009               
010               
011               
012               
          function Add-AccessRecord {               
param (               
    [string]$sql,               
    [System.Data.OleDb.OleDbConnection]$connection,               
    [string]$table,               
    [string]$values               
)               
    $sql = "INSERT INTO $table VALUES ($values)"               
                  
    $cmd = New-Object System.Data.OleDb.OleDbCommand($sql, $connection)               
    $cmd.ExecuteNonQuery()               
} 
       
 

If we alter our function to create the SQL as shown we can pass the table name and values into the function.

Add-AccessRecord -connection $db -table test1 -values '"John","Smith","01/07/1981"'

This will add a record and start applying the values in sequence to the row. it assumes that values are supplied for all fields.

At the moment the function effectively breaks our previous version because $sql is redefined in the function. We need to test whether we should create a SQL record or use the SQL statement supplied. We could check if the $sql parameter is given a value but a better way would be to use parameter sets which we will look at after we have learned to read data from a table.

Technorati Tags: PowerShell,Office 2010,Access,insert record

Set Background colour of Excel cell

RichardSiddaway — Wed, 25 Nov 2009 20:21:20 GMT

I needed to set the background colour of a cell in an Excel spreadsheet recently. The way to do it is to set the ColorIndex property of the Interior properties of the cell as shown in line 14. The ColorIndex can be set to a number between 1 and 56. Use –4142 if you don’t want a coloured background.

Using a ColorIndex is OK but what colours are related to the index values. I found a VBScript amongst the Office information on the Microsoft site and modified it as shown below.

            001              
002               
003               
004               
005               
006               
007               
008               
009               
010               
011               
012               
013               
014               
015               
016               
017               
018               
019               
020               
          $xl = New-Object -ComObject "Excel.Application"               
$wkbk = $xl.WorkBooks.Add()               
$sheet = $wkbk.WorkSheets.Item(1)               
$xl.Visible = $true               
              
for($i=1; $i -le 56; $i++) {               
    switch ($i) {               
        {$_ -le 14}                {$row = $i;    $col = 1}               
        {$_ -ge 15 -and $_ -le 28} {$row = $i-14; $col = 3}               
        {$_ -ge 29 -and $_ -le 42} {$row = $i-28; $col = 5}               
        {$_ -ge 43 -and $_ -le 56} {$row = $i-42; $col = 7}                                  
    }               
    $sheet.Cells.Item($row, $col).FormulaLocal = $i               
    $sheet.Cells.Item($row, $col+1).Interior.ColorIndex = $i               
              
}               
              
$wkbk.SaveAs("c:\test\XLColours.xlsx")               
$wkbk.Close()               
$xl.Quit() 
       
 

I am using Excel 2010 so WorkBooks.Add() will work OK. If you are using an earlier version of Excel and are not using US English then you need to use the alternative method of adding workbooks as I discussed in earlier posts.

Create an object for Excel and then add a workbook and use the first worksheet. Make it visible so we can see what happens.

Use a for loop to work through the values 1 to 56. I want the information arranging in 4 columns so use a switch statement to determine the row and column to use.

The ColorIndex value is displayed and the next cell on the row is coloured using that index.

We can then save and close the workbook.

Technorati Tags: PowerShell,Office 2010,Excel,Cell Colour

Add Access record

RichardSiddaway — Mon, 23 Nov 2009 19:56:26 GMT

We’ve seen how to create a database and a table. Now we need to know how to add a record to that table.

            001              
002               
003               
004               
005               
006               
007               
008               
          function Add-AccessRecord {               
param (               
    [string]$sql,               
    [System.Data.OleDb.OleDbConnection]$connection               
)               
    $cmd = New-Object System.Data.OleDb.OleDbCommand($sql, $connection)               
    $cmd.ExecuteNonQuery()               
} 
       
 

This takes a SQL INSERT statement and executes it against the table in our open database.

PS> Import-Module accessfunctions
PS> $db = Open-AccessDatabase -name test03.mdb -path c:\test
PS> $sql = @"
>> INSERT INTO test1
>> (FirstName, Lastname, DOB)
>> VALUES ("Fred", "Smith", "01/09/1979")
>> "@
>>
PS> Add-AccessRecord -sql $sql -connection $db
1
PS> Close-AccessDatabase $db

if you think this is identical to the function we used to create a table you are right. We are also unlikely to want to add a single record at a time to the table. next job is how we can add bulk data to the table.

Technorati Tags: PowerShell,Access,Add record

Adding a Table to an Access database

RichardSiddaway — Mon, 23 Nov 2009 17:32:48 GMT

After reviewing the function I produced in the last post I realised i had made it over complicated. I’m working with Office 2010 and 2007 predominantly so I should have the 2007 format as my default. If I do that and change the switch parameter so it is used to create an Access 2003 format file I can simplify the New-AccessDatabase function to this.

            001              
002               
003               
004               
005               
006               
007               
008               
009               
010               
011               
012               
013               
014               
015               
016               
017               
018               
          function New-AccessDatabase {               
param (               
    [string]$name,               
    [string]$path,               
    [switch]$acc3               
)                  
              
    if (!(Test-Path $path)){Throw "Invaild Folder"}               
    $file = Join-Path -Path $path -ChildPath $name                
    if (Test-Path $file){Throw "File Already Exists"}               
                  
    $cat = New-Object -ComObject 'ADOX.Catalog'               
                  
    if ($acc3) {$cat.Create("Provider=Microsoft.Jet.OLEDB.4.0; Data Source=$file")}               
    else {$cat.Create("Provider=Microsoft.ACE.OLEDB.12.0; Data Source=$file")}               
              
    $cat.ActiveConnection.Close()               
} 
       
 

Which is then used like this to create a 2003, 2007 and 2007 (.accdb) format database respectively.

New-AccessDatabase -name test03.mdb -path c:\test -acc3
New-AccessDatabase -name test07.mdb -path c:\test
New-AccessDatabase -name test0.accdb -path c:\test

So having created a database we need to be able to open it and to add some tables. At this point I discovered that I really should be working with the 2003 format as some things don’t seem to work with the Microsoft.ACE.OLEDB.12.0 provider. Heh ho.

Before we can do anything with a database we have to be able to open a connection to it. Once we have finished with it we need to close it. This gives the following two functions.

001
002
003
004
005
006
007
008
009
010
011
012
013
014
015
016
017
018
019

function Open-AccessDatabase {             
param (             
    [string]$name,             
    [string]$path             
)                 
    $file = Join-Path -Path $path -ChildPath $name              
    if (!(Test-Path $file)){Throw "File Does Not Exists"}             
            
    $connection = New-Object System.Data.OleDb.OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0; Data Source=$file")             
    $connection.Open()             
    $connection             
}             
            
function Close-AccessDatabase {             
param (             
    [System.Data.OleDb.OleDbConnection]$connection             
)             
    $connection.Close()                
} 

I’m using the ADO.NET functionality to create a connection use the Access 2007 provider. Nice thing is that it works with the 2003 format as well.

After opening the database we need to add a table. We can load our module, create a database and then open the database

Import-Module accessfunctions
New-AccessDatabase -name test03.mdb -path c:\test -acc3
$db = Open-AccessDatabase -name test03.mdb -path c:\test

To add the table we create an SQL statement and input that to the database

PS> $sql=@"
>> CREATE TABLE test1
>> (FirstName CHAR,
>> LastName CHAR,
>> DOB DATETIME)
>> "@
>>
PS> New-AccessTable -sql $sql -connection $db

A here string is a good way to create the SQL statement as we can build it up in a format that is easily readable.

The New-AccessTable function takes the sql statement and the connection to the database as parameters. The function is simply this

            001              
002               
003               
004               
005               
006               
007               
008               
009               
          function New-AccessTable {               
## assumes database is open               
param (               
    [string]$sql,               
    [System.Data.OleDb.OleDbConnection]$connection               
)               
    $cmd = New-Object System.Data.OleDb.OleDbCommand($sql, $connection)               
    $cmd.ExecuteNonQuery()               
} 
       
 

We use the ADO.NET OleDbCommand class to execute a NonQuery. This is where we don’t expect any data to be returned.

One thing that is obvious is that I am not checking the SQL to ensure that it is a CREATE TABLE command that is being passed in. This is a job for another time.

Technorati Tags: PowerShell,Access,Create Table

Creating an Access database

RichardSiddaway — Sun, 22 Nov 2009 19:07:08 GMT

I’ve blogged a bit about using SQL Server with PowerShell, and using Word and Excel through PowerShell. I realised that I hadn’t seen much about using Access. Access is part of the Office suite and is present on many desktops. It forms a handy data store for using with PowerShell.

In this series of articles I’ll show how to work with data contained in Access databases using PowerShell.

First job is to create a database.

            001              
002               
003               
004               
005               
006               
007               
008               
009               
010               
011               
012               
013               
014               
015               
016               
017               
018               
019               
020               
021               
          function New-AccessDatabase {               
param (               
    [string]$name,               
    [string]$path,               
    [switch]$acc7               
)                  
              
    if (!(Test-Path $path)){Throw "Invaild Folder"}               
    $file = Join-Path -Path $path -ChildPath $name                
    if (Test-Path $file){Throw "File Already Exists"}               
                  
    $cat = New-Object -ComObject 'ADOX.Catalog'               
                  
    if ($acc7) {$cat.Create("Provider=Microsoft.ACE.OLEDB.12.0; Data Source=$file")}               
    else {               
        if ($name -match "\.accdb") {$cat.Create("Provider=Microsoft.ACE.OLEDB.12.0; Data Source=$file")}                  
        else {$cat.Create("Provider=Microsoft.Jet.OLEDB.4.0; Data Source=$file")}               
    }               
              
    $cat.ActiveConnection.Close()               
} 
       
 

My function takes three parameters – a file name, a path and switch. The path is tested and the function aborts if the folder isn’t available. Likewise if the file already exists in that folder an error is thrown.

If the –aac7 switch is set I want to create an Access 2007 database with an .mdb extension. Otherwise if the extension is .accdb an Access 2007 database is created. if the extension is .mdb without the –acc7 switch an Access 2002-2003 file is created. These examples illustrate the choices.

To create database with Access 2002-2003 file format

New-AccessDatabase -name test03.mdb -path c:\test

To create database with Access 2007 file format

New-AccessDatabase -name test07.mdb -path c:\test -acc7

To create database with Access 2007 accdb file format

New-AccessDatabase -name atest07.accdb -path c:\test

I’m using the providers loaded with Office 2010. The Microsoft.ACE.OLEDB.12.0 provider is delivered with Office 2007 SP1 or can be downloaded from http://www.microsoft.com/downloads/details.aspx?familyid=7554F536-8C28-4598-9B72-EF94E038C891&displaylang=en

Now we’ve created our database the next job is to create some tables.

Technorati Tags: PowerShell,Access

Excel 2010 beta

RichardSiddaway — Thu, 19 Nov 2009 19:09:26 GMT

The beta still allows those of us that aren’t in the USA to use the simple method of creating a new spreadsheet using PowerShell.

            001              
002               
003               
          $xl = New-Object -comobject "excel.application"                
$xl.visible = $true               
$xlbooks =$xl.workbooks.Add() 
       
 

Makes life easier.

In case you are wondering – if you don’t have your machine set to US English and you are using Excel 2007 or earlier you have to do this.

            001              
002               
003               
004               
005               
          $xl = New-Object -comobject "excel.application"                
$xl.visible = $true               
$xlbooks =$xl.workbooks               
$newci = [System.Globalization.CultureInfo]"en-US"               
$xlbooks.PSBase.GetType().InvokeMember("Add", [Reflection.BindingFlags]::InvokeMethod, $null, $xlbooks, $null, $newci) 
       
 

Technorati Tags: PowerShell,Excel 2010,Excel 2007 and earlier

Reminders via WPF

RichardSiddaway — Wed, 18 Nov 2009 19:13:41 GMT

If I am working on my home machine I don’t necessarily have Outlook or any other application that gives me calendaring capability open. There are times when I need a simple reminder to do something. For some reason I always seem to have PowerShell open so I thought of using the eventing system to give me a reminder. I could also do this via the task scheduler functions in the PowerShellPack (Windows 7 Resource kit) which I’ll look at another day.

            001              
002               
003               
004               
005               
006               
007               
008               
009               
010               
011               
012               
013               
014               
015               
016               
017               
018               
019               
020               
          function Set-Alarm {               
param (               
        [datetime]$time,               
                      
        [string]$msg = "Alert Issued"               
      )                    
              
    $now = Get-Date                
    if ($time -gt $now) {$ts = $time - $now}               
    else {throw "Time has to be in future"}               
                      
    $timer = New-Object -TypeName System.Timers.Timer               
    $timer.Interval = $ts.TotalMilliseconds               
    $timer.AutoReset = $false               
    $timer.Enabled = $true               
                  
    $global:act = "Start-Process powershell -ArgumentList ""-Sta -WindowStyle Hidden -File C:\Scripts\WPF\show-alert.ps1 """"$msg"""" "" "               
                  
    Register-ObjectEvent -InputObject $timer -EventName Elapsed -SourceIdentifier TimeAlert  -Action {Invoke-Expression -Command $act }               
} 
       
 

My function accepts a time and a message

Set-Alarm "18:47" "Test1"

It then gets the current time, compares the two times and assuming the alert is to be issued in the future creates a Timespan object be subtracting the times as shown.

We can then create .NET timer object and set the interval to the total number of milliseconds in our timespan. We only want it to fire once so we set autoReset to false and then enable the timer.

I then create a global variable containing the powershell start up commands. In this case I want it to start in Single Thread mode so I can use the WPF classes. I call a script when PowerShell starts and pass the script the message. Note the number of quotes around the $msg variable – this is to make sure the string passed to invoke-expression is correct. This is messy but needed.

The $act variable has to be global because the action scriptblock for Register-objectevent isn’t evaluated until the event fires. If $act is in the script scope it won’t be found and the event won’t fire correctly.

            001              
002               
003               
004               
005               
006               
007               
008               
009               
010               
011               
012               
013               
014               
015               
016               
017               
018               
          param (               
[string]$msg = "Testing",               
[string]$title = "Alert"               
)               
## load WPF assemblies               
Add-Type –assemblyName PresentationFramework               
Add-Type –assemblyName PresentationCore               
Add-Type –assemblyName WindowsBase               
              
## create a window               
$window = New-Object -TypeName System.Windows.Window               
$window.Title = $title               
$window.Content = $msg               
$window.FontSize = 36               
$window.SizeToContent = "WidthAndHeight"               
              
## display window               
$null = $window.ShowDialog() 
       
 

The script loads the WPF assemblies I need and then creates a window and writes out the message thats been passed in.

This is a bit messy with having to create a global variable but I can’t think of a simpler way to access the variable in the scriptblock for Register-objectevent. The other issue is that I can only have a single event of this type defined because of the variable. I would need to create the variable with a random name and create another string of the Register-objectevent invocation.

Technorati Tags: PowerShell v2,Events,Timer

WMI CookBook: WMI Presentation

RichardSiddaway — Tue, 17 Nov 2009 19:13:36 GMT

As with other PowerShell objects there is a default format for the display of WMI objetcs. If we look at the NetworkAdapter class

PS> Get-WmiObject -Class Win32_NetworkAdapter -Filter "DeviceId='11'"

ServiceName      : athr
MACAddress       : 00:00:00:00:00:00
AdapterType      : Ethernet 802.3
DeviceID         : 11
Name             : Atheros AR5007 802.11b/g WiFi Adapter
NetworkAddresses :
Speed            : 54000000

You didn’t think I’d really give you my MAC address did you?

If we want to see all of the properties we can do this

Get-WmiObject -Class Win32_NetworkAdapter -Filter "DeviceId='11'" | select -Property *

but we get a few ugly looking entries

__GENUS
__CLASS
__SUPERCLASS
__DYNASTY
__RELPATH
__PROPERTY_COUNT
__DERIVATION
__SERVER
__NAMESPACE
__PATH

that we may not actually want or need.

We can easily filter them out if desired

Get-WmiObject -Class Win32_NetworkAdapter -Filter "DeviceId='11'" | select -Property * -ExcludeProperty "__*"

The ExcludeProperty takes a wild card to identify the properties with two underscore characters at the front.

Simple but effective.

Technorati Tags: PowerShell,WMI,Property Selection

Patch Tuesday

RichardSiddaway — Sat, 14 Nov 2009 20:15:23 GMT

Patch Tuesday – the second Tuesday in the month – is the day Microsoft (and other vendors) release their patches. If you want to be able to plan ahead for these dates ( like arranging holiday or sick leave ) these two functions will supply the dates to look out for.

            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               
          function get-secondTuesday {               
param([datetime]$date)               
    switch ($date.DayOfWeek){               
        "Sunday"    {$patchTuesday = $date.AddDays(9); break}                
        "Monday"    {$patchTuesday = $date.AddDays(8); break}                
        "Tuesday"   {$patchTuesday = $date.AddDays(7); break}                
        "Wednesday" {$patchTuesday = $date.AddDays(13); break}                
        "Thursday"  {$patchTuesday = $date.AddDays(12); break}                
        "Friday"    {$patchTuesday = $date.AddDays(11); break}                
        "Saturday"  {$patchTuesday = $date.AddDays(10); break}                
     }               
     $patchTuesday.ToLongDateString()               
}               
              
function Get-PatchTuesday {               
param (               
    [parameter(ValueFromPipeline=$true)]               
    [int]$year = (Get-Date).Year,               
                  
    [switch]$nextmonth               
    )               
                   
    if ($nextmonth){               
        $now = Get-Date               
        $d = Get-Date -Day 1 -Month $($now.Month + 1) -Year $now.Year               
        get-secondTuesday $d               
    }               
    else {               
        1..12 | foreach {               
            $d = [datetime]"$_/1/$year"               
            get-secondTuesday $d               
        }                  
    }               
} 
       
 

Get-PatchTuesday takes a year (default is current year) and works out the first of each month as a date. This is passed to get-secondtuesday to calculate the date the patches will be released.

If the –nextmonth parameter is used then the first of next month is calculated and the patch date for that month retrieved from getsecondtuesday.

Dates for the next few months are

08 December 2009
12 January 2010
09 February 2010
09 March 2010
13 April 2010
11 May 2010
08 June 2010
13 July 2010
10 August 2010
14 September 2010
12 October 2010
09 November 2010
14 December 2010

Time to start planning.

These functions can be adapted to provide dates for any date that follows the pattern of nth weekday.

Technorati Tags: PowerShell,dates,patch Tuesday