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.