Categories

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

Leave a Reply