Monthly Archive

Categories

Monthly Archives: November 2009

Add Access record

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

 

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

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

Windows 7 – Pet Hate #1

I’ve been using Windows 7 since January and as I’ve reported several times I have been really happy with it.  One source of irritation has arisen – the Action Center.

This is a reporting system for system issues such as security (firewall turned off) or maintenance (backups not configured).  This is fine but the thing takes over. Once it has popped its window up to say there is a problem it won’t go away. Not good.

I don’t mind been reminded of things but not to the point where I am then effectively stopped working because these windows are configured to always remain on top.

Technorati Tags: Windows 7,Action center

WMICookbook: Read Routing Table

When we need to troubleshoot networking problems we will sometimes need to read the routing table on a machine. The routing table contains the information on the routes known to the network interfaces. This can be created automatically or manually . On the local machine we can use the route command to find this information – but how do we find it on a remote machine. WMI has a class that enables us to read the routing table.

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
function Get-RouteTable {
param (
    [parameter(ValueFromPipeline=$true)]
    [string]$computer="."
)

## create class for object
$source=@"
public class WmiIPRoute
{
    private string _destination;
    private string _mask;
    private string _nexthop;
    private string _interface;
    private int _metric;
   
     public string Destination {
        get {return _destination;}
        set {_destination = value;}
    }
   
    public string Mask {
        get {return _mask;}
        set {_mask = value;}
    }
   
    public string NextHop {
        get {return _nexthop;}
        set {_nexthop = value;}
    }
   
    public string Interface {
        get {return _interface;}
        set {_interface = value;}
    }
   
    public int Metric {
        get {return _metric;}
        set {_metric = value;}
    }
}
"@

Add-Type -TypeDefinition $source

    $data = @()
    Get-WmiObject -Class Win32_IP4RouteTable -ComputerName $computer| foreach {
        $route = New-Object -TypeName WmiIPRoute
        $route.Destination = $_.Destination
        $route.Mask        = $_.Mask
        $route.NextHop     = $_.NextHop
        $route.Metric      = $_.Metric1
       
        $filt = "InterfaceIndex='" + $_.InterfaceIndex + "'" 
        $ip = (Get-WmiObject -Class Win32_NetworkAdapterConfiguration -Filter $filt -ComputerName $computer).IPAddress

        if ($_.InterfaceIndex -eq 1) {$route.Interface = "127.0.0.1"}
        elseif ($ip.length -eq 2){$route.Interface = $ip[0]}
        else {$route.Interface = $ip}
       
        $data += $route
    }
    $data | Format-Table -AutoSize 
}

 

Our function takes a single parameter – a computer name (or IP address) I’ve used the advanced function parameters to this function operates on the pipeline.  We then create a .NET class to hold our data – we will be accessing a couple of WMI classes so we’ll make the presentation neat.  The class is added using Add-Type.

As an aside I really like this technique for collecting data together into a single object.  Its neater and easier to use than Add-Member.

We can then use Get-WmiObject -Class Win32_IP4RouteTable -ComputerName $computer to retrieve the routing information. We create an instance of our object and populate the properties.  One thing we need to know is the Interface ie which address on our machine is using this route,  We can find this from the Win32_NetworkAdapterConfiguration  class.  There isn’t an association but we can find the address by using the InterfaceIndex as a filter – its the same value in both classes.  if the InterfaceIndex = 1 its the Loopback Adapter on 127.0.0.1

We can then add our route to the data. When all the routes are collected we can display the data.  The data could be output onto the pipeline but at the moment I can’t think what else to do with it so we’ll leave it like this for now.

Note: Win32_IP4RouteTable is only available on Windows 2003 and later

Excel 2010 beta

 

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)

 

PowerShell v2 cmdlets

Jonathan has started a series of posts at http://www.jonathanmedd.net/ on all of the new cmdlets in PowerShell v2.  If you are just starting with v2, or want more information on whats new, this is a good place to start.

Technorati Tags: PowerShell v2,cmdlets

Office 2010 – fixed in beta

A couple of issues that I noticed with the Office 2010 TP have been fixed in the beta:

  • Word documents will now download from the Internet and open
  • One undocks back to normal view correctly

I did notice that in the Visio 2010 beta the Window title still says Technical Preview.  All the other components just have the application name.

Technorati Tags: Office 2010

Reminders via WPF

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

Reminders

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