Hey, Scripting Guy! I have looked everywhere, and I cannot find a script that will create a new Microsoft Office Access database, and automatically add a new table and columns to it. If you had such a thing in Windows PowerShell it would be even better. I know it is probably too much to ask, but could you, would you write such a thing for me? I mean after all, you are the Microsoft Scripting Guy, so how about writing me a script? If I had such a script, I would be so happy. I could then easily run scripts and create Office Access databases all the time. As it currently stands, it takes about two or three minutes for Office Access to launch. Then I have to go through the welcome screen, select the template, and wait for another 30 seconds. Next I have to go through the wizard to name everything. Then when I am done, guess what? I have no idea where the stupid database resides. I do not put everything in the default folders because by default everything gets lost. I put stuff in a data folder so I can do a nightly backup easily. It takes me a minimum of 15 minutes to go through this rigmarole.

– MW

Hi MW,

Now let me think about this. If you do four databases a week, that is 50 hours of labor a year. It seems to me the return on investment (ROI) of such a script would be worth it, so I will write said script.

To create an Office Access database using a Windows PowerShell script, you can use the CreateAccessDataBase.ps1 script.

To create an Office Access database using a Windows PowerShell script, you can use the CreateAccessDataBase.ps1 script.

CreateAccessDataBase.ps1

Function Check-Path($Db)
{
 If(!(Test-Path -path (Split-Path -path $Db -parent)))
   {
     Throw "$(Split-Path -path $Db -parent) Does not Exist"
   }
  ELSE
  {
   If(Test-Path -Path $Db)
     {
      Throw "$db already exists"
     }
  }
} #End Check-Path
Function Create-DataBase($Db)
{
 $application = New-Object -ComObject Access.Application
 $application.NewCurrentDataBase($Db,10)
 $application.CloseCurrentDataBase()
 $application.Quit()
} #End Create-DataBase
Function Invoke-ADOCommand($Db, $Command)
{
 $connection = New-Object -ComObject ADODB.Connection
 $connection.Open("Provider= Microsoft.Jet.OLEDB.4.0;Data Source=$Db" )
 $connection.Execute($command)
 $connection.Close()
} #End Invoke-ADOCommand
# *** Entry Point to Script ***
$Db = "C:\FSO\Test.mdb"
$table = "MyTest"
$Fields = "F1 Counter, F2 Date, F3 Integer, F4 Text"
$command = "Create Table $table `($fields`)"
Check-Path -db $Db
Create-DataBase -db $Db
Invoke-ADOCommand -db $Db -command $command

The first function we create is the Check-Path function. To create the function, we use the Function keyword, and give it the name. The Check-Path function will be used to verify the path to the database file, so it accepts an input parameter named $Db. This is seen here:

Function Check-Path($Db)
{

We then use the Test-Path cmdlet to determine if the parent path exists. The parent portion of the path is the drive letter and the folder. To break the parent away from the entire path to the database file, we use the Split-Path function. We give it the path contained in the $Db variable and tell it to retrieve the parent of the path by using the -parent parameter. Because we are only interested if the folder does not exist, we use the not  operator:

If(!(Test-Path -path (Split-Path -path $Db -parent)))
   {

If the path does not exist, we use the Throw keyword to generate an error. Throw accepts a string for the error message to generate. We once again use the Split-Path cmdlet to retrieve the parent path from the $Db variable. We use a sub expression to cause the evaluation of the Split-Path cmdlet to occur first and to return the data from the cmdlet to the string that is printed to the screen. This is seen here:

Throw "$(Split-Path -path $Db -parent) Does not Exist"
   }

Suppose the parent path exists. What happens if the database also exists? If we did not do something, the script would fail. Well, this is where we use the Else clause in our If statement:

ELSE
  {

Else what, you might ask? Well, what if the database does exist? To check this out, we once again use the Test-Path cmdlet to determine if the database exists:

If(Test-Path -Path $Db)

If the database already exists, we will once again use Throw and inform the user that the database already exists. Logic could be implemented here that would allow us to open the existing database and append information to it, but that is beyond the scope of this particular script. Here is the simple Throw command:

{
      Throw "$db already exists"
     }
  }
} #End Check-Path

After all those curly brackets, I am tired. So let’s jump into another function. We are now ready to create the database. To do this, we will create a function called Create-DataBase. It only needs the path and name of the database to create. This information is supplied via the $Db variable, as seen here:

Function Create-DataBase($Db){

The first thing we want to do is to create an instance of the Access.Application object. The Access.Application object is documented on MSDN, and it is the main object we use when working with Office Access. To create the Access.Application object, we use the New-Object cmdlet and specify that we are creating a ComObject. We store the returned object in the $application variable:

$application = New-Object -ComObject Access.Application

Now we need to create a new database. To do so, we use the NewCurrentDataBase method from the Access.Application object. We give it the path and the magic number 10. Normally, I dislike using magic numbers, and as a best practice you should avoid them like the plague. Why did I use one here? Uh, I’m feeling lazy. So what does the number 10 mean? It is an instance of the AcNewDatabaseFormat Enumeration. What I should have done was create this enumeration and then use it directly as I have done in other “Hey, Scripting Guy!” articles, but I didn’t. I will leave that as an exercise for you. What I will do is paste the enumeration names and their associated values in Table 1. The reason we used the Access 2002-2003 type of database is because we are planning on using ADO to create the table and columns. ADO does not work with the Access 2007 file format.

$application.NewCurrentDataBase($Db,10)

After the database has been created, it is time to close the database. And after you call the CloseCurrentDataBase method, we also need to call the Quit method from the Access.Application object (if we forget this important line of code, we can easily end up with 25 or 30 instances of the Access.exe executable running in memory):

$application.CloseCurrentDataBase()
 $application.Quit()
} #End Create-DataBase

Next we want to create the table and the fields in the Office Access table. To do this, we are going to use ADO. There is a VBScript version of this section of the script here.

We decide to create another function. This function is called Invoke-ADOCommand, and it receives two input parameters. The first parameter is the path to the database and the second is the ADO command we wish to run. As a best practice, I like to arrange the parameters in the order in which they will be used in the code, but as a practical matter it does not really matter. This command begins the Invoke-ADOCommand function:

Function Invoke-ADOCommand($Db, $Command){

The first thing we need to do is create an instance of the ADODB.Connection object. We use the New-Object cmdlet and store the returned connection object in the $connection variable, as seen here;

$connection = New-Object -ComObject ADODB.Connection

After the connection has been created, we need to open the connection. The hardest thing about using ADO is finding the provider strings. Here is the provider string for an Access Database:

$connection.Open("Provider= Microsoft.Jet.OLEDB.4.0;Data Source=$Db" )

After the connection has been opened, we can then execute the command:

$connection.Execute($command)

After we have executed the command, we need to close the connection. It is only polite to do so, and it will help to prevent file-locking issues as well:

$connection.Close()
} #End Invoke-ADOCommand

We are now at the entry point to the script. This is where we assign values to variables:

$Db = "C:\FSO\Test.mdb"
$table = "MyTest"

When creating the fields in the database, we need to specify both the field name and the type of field it will be. As an example, we are creating four different types of fields. In a real application, one would expect to see similar items:

$Fields = "F1 Counter, F2 Date, F3 Integer, F4 Text"

Next we build up the command text. By using variables to create the command, we make it easier to modify the command text. All you need to do is pay attention to commas and spacing when you assign different values for the field names and table names:

$command = "Create Table $table `($fields`)"

We now have everything in place, and it is a simple matter of calling the functions. First we check the path where the database will be created. We do so by calling the Check-Path function and supply the path we stored in the $db variable. Next we create the database by calling the Create-Database function while passing the path to the database. Next we call the Invoke-ADOCommand function to create our table. This also requires the path to the database file, as well as the command we wish to execute. This is all seen here:

Check-Path -db $db
Create-DataBase -db $Db
Invoke-ADOCommand -db $Db -command $command

When we are finished, we are rewarded with the database seen here:

Well, MW, that is all there is to creating an Office Access database and table by using Windows PowerShell. I hope you figure out a good ROI calculation and use it for your review at the end of the year. It makes a good bullet item. We will see you tomorrow as Office Access week continues. Until then, stay warm.

Ed Wilson and Craig Liebendorfer, Scripting Guys