[HOWTO] Access Microsoft Access Database with PowerShell

In one of our current projects we had to access a Microsoft Access Database (.mdb) with PowerShell. I first tried to access the .mdb using the Invoke-SqlCmd Cmdlet from our PowerShell module biz.dfch.PS.System.Data. Unfortunately connecting to the Access database with the Invoke-SqlCmd Cmdlet does not work as it uses SqlClient from the C#/.NET namespace System.Data under the hood. After a short internet research I figured out, that I have to use System.Data.Odbc or System.Data.OleDb from the C#/.NET namespace System.Data. I decided to use System.Data.Odbc and extended our current PowerShell module biz.dfch.PS.System.Data accordingly. From version 2.0.0 the module biz.dfch.PS.System.Data has a new Cmdlet called Invoke-OdbcCmd. The Invoke-OdbcCmd Cmdlet can be used to invoke commands against different databases that support ODBC.

Install PowerShell Module biz.dfch.PS.System.Data

To install the PowerShell module biz.dfch.PS.System.Data the following steps have to be executed.

  1. Open PowerShell console
  2. Download NuGet package by executing the following command
    nuget install biz.dfch.PS.System.Data

  3. Install NuGet package by executing the following command
    .\biz.dfch.PS.System.Data.2.0.0\Install.ps1

Usage of Invoke-OdbcCmd Cmdlet

Get identity from access database (.mdb or .accdb)

Invoke-OdbcCmd -Dbq 'C:\arbitrary-database.mdb' -Driver '{Microsoft Access Driver (*.mdb, *.accdb)}' "Select @@Identity";

Login with credentials and get identity from access database (.mdb or .accdb)

Invoke-OdbcCmd -Dbq 'C:\arbitrary-database.mdb' -Driver '{Microsoft Access Driver (*.mdb, *.accdb)}' "Select @@Identity" -Username 'Arbitrary' -Password 'P@ssw0rd';

Get identity from access database (.mdb or .accdb) by providing ConnectionString

$connectionString = 'Driver={Microsoft Access Driver (*.mdb, *.accdb)};Dbq=C:\arbitrary-database.mdb;Uid=Admin;Pwd=;';
Invoke-OdbcCmd -ConnectionString $connectionString;

For more information concerning ConnectionStrings see here

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: