Categories

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

Leave a Reply

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>