Richard Siddaway's Blog

Working with Access dates

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

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

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

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

Updating Access data

The last of of our data manipulation tasks is to update the data – we have already seen how to create, read and delete.

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

Testing Connection to Access database

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.

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.

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.

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

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:\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

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

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.

Add Access Record Pt III – parameter sets

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.

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.

Export Access data to csv file

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.

Reading Access records

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

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.

Add Access Record PtII

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.

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.

Set Background colour of Excel cell

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.

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.

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.

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.

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.

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.

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

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.

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.

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.

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.

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.

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.

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.

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.

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.

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.

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.

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.

WMI CookBook: WMI Presentation

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.

Office 2010 beta

The Office 2010 beta is available on TechNet and MSDN. The installation seems very smooth. Need to uninstall Technical Preview version first – thats always boring and a pain.

Slight hiccup over activation as the license key wasn’t asked for by Office when doing a customised install!!  Soon resolved that.  Visio and Project have separate installs but the look and feel matches the rest of Office.

Now I’ve got that I’ll try a few more PowerShell and Office projects.  Be good to dig out the One Note PowerShell functions as well.

I remembered to export my autocorrect entries before uninstalling the previous version and managed to import them back in – functions to do this can be found in earlier posts.

I’m expecting this to be boring & just work.  I’ll let you know how I get on.