Add Access record
Published November 23, 2009We’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.
Adding a Table to an Access database
Published November 23, 2009
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"} $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") function Close-AccessDatabase { |
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.
Creating an Access database
Published November 22, 2009I’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"} $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.
Windows 7 – Pet Hate #1
Published November 21, 2009I’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
Published November 20, 2009When 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 $data = @() if ($_.InterfaceIndex -eq 1) {$route.Interface = "127.0.0.1"} |
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
Published November 19, 2009
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
Published November 19, 2009Jonathan 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
Published November 18, 2009A 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
Published November 18, 2009If 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 |
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 ## display window |
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.
Reminders
Published November 18, 2009If 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 |
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 ## display window |
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.