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.