Just A Programmer We're just programmers

25Aug/111

Creating an Access Database In Powershell without Access installed

Recently I stumbled across a Hey, Scripting Guy! blog post titled “How Can I Use Windows PowerShell to Create an Office Access Database?” that demonstrated how to create a Microsoft Access database in powershell. The only problem with this script was it required Microsoft Access to be installed on the machine that it ran on. I knew this dependency on Access was unnecessary, because a few years ago I wrote an open source project for manipulating Microsoft Access and SQLite databases called PlaneDisaster.NET. That program did not require Microsoft Access to be installed and it allowed you to create, compact and repair databases. (I’ve discussed PlaneDisaster.NET previously on this blog.)

So, as a matter of personal and professional pride I determined that I must write a PowerShell script that could create an Access Database without requiring Microsoft Access to be installed. After all, If you can do something in C#, you can do it in PowerShell. I did have one concern. I was making unmanaged Win32 API calls via PInvoke. However, I quickly learned that’s not a problem at all. My particular PInvoke calls involve enums, but you can create enums in PowerShell. However, I ended up replacing the enums with ints in the PInvoke declarations and no one complained since PowerShell is good at figuring that sort of thing out. The code I ended up with is:

$signature = @'
[DllImport("ODBCCP32.DLL",CharSet=CharSet.Unicode, SetLastError=true)]
public static extern int SQLConfigDataSource
    (int hwndParent, int fRequest, string lpszDriver, string lpszAttributes);
 
[DllImport("odbccp32", CharSet=CharSet.Auto)]
public static extern int SQLInstallerError(int iError, ref int pfErrorCode, StringBuilder lpszErrorMsg, int cbErrorMsgMax, ref int pcbErrorMsg);
'@;
 
Add-Type -MemberDefinition $signature -Name Win32Utils -Namespace PInvoke -Using PInvoke,System.Text;

This created two static functions I could call, [PInvoke.WIn32Utils]::SQLConfigDataSource() and [PInvoke.WIn32Utils]::SQLInstallerError().

However, a new problem emerged. I could not get the script to run in a 64 bit PowerShell process. A quick google search informed me that the only way to get a 64 bit Access driver is through the Microsoft Access Database Engine 2010 Redistributable. However, even after installing the 64 bit version of that executable, my script did not work unless I ran it through a 32 bit instance of PowerShell.

After a lot of searching and frustration I eventually had an epiphany. The provider name I was using to create the database was Microsoft Access Driver (*.mdb). This provider name refered to ODBCJT32.DLL, which is only available as a 32 bit version. However, the driver that ships with the Access 2010 redistributable is called ACEODBC.DLL. This dll has a provider name of Microsoft Access Driver (*.mdb, *.accdb).  The code for this is simple:

[string] $driver = 'Microsoft Access Driver (*.mdb)';
if ([IntPtr]::Size -eq 8) {
    $driver = 'Microsoft Access Driver (*.mdb, *.accdb)';
}

Yes I’m using the size of a pointer to determine if I’m running in 32 or 64 bits.

The full script is below:

That version is hosted on poshcode.org to be more searchable. However, the authoritative one will remain as a github gist.

  • Pingback: Justin Dearing

  • Pingback: Sean Kearney

  • Pingback: Jim Poshible

  • Pingback: Jim Poshible

  • Pingback: Justin Dearing

  • Kurt

    I tried to tweak your code to compact a database instead of creating one:
    Function Compact-MDB([string] $filename) {
    removed file removal code
    [string] $attrs = [string]::Format(“COMPACT_DB=`”{0}`” `”{0}`” General`0, $filename);

    I’m getting Error: Driver’s ConfigDSN, ConfigDriver, or ConfigTranslator failed. When I run the unchanged Create-MDB code it works fine. Am I missing something in my changes?