Marc Rufer bio photo

Marc Rufer

Software engineer
@d-fens GmbH

Twitter Google+ LinkedIn Github Stackoverflow
RSS Feed

HOWTO Access Microsoft Access Database with PowerShell

Marc Rufer 19 May 2017 ODBC PowerShell

In one of my projects at work I 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 Microsoft Access 2016 Runtime

To make Invoke-OdbcCmd Cmdlet work, the necessary driver has to be installed.

  1. Download Microsoft Access 2016 Runtime
  2. Install Microsoft Access 2016 Runtime

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=Arbitrary;Pwd=P@ssw0rd;';
Invoke-OdbcCmd -ConnectionString $connectionString;

For more information concerning ConnectionStrings see here

comments powered by Disqus

Posts

HOWTO Access Azure File Share in PowerShell Script executed by a Scheduled Task BUG "Task Scheduler service is not available" Error, if specifying Network Connection Condition HOWTO Sync OneDrive on Server even if Windows User not logged in HOWTO Access Microsoft Access Database with PowerShell Get NetworkCredential from PSCredential splits Username into Username and Domain HOWTO Create LocalDB File (.mdf) manually in Visual Studio 2015 HOWTO Set Cookie Header on DefaultRequestHeaders of HttpClient HOWTO Set up PowerShell Remote Session Configuration HOWTO Analyze PowerShell Scripts with PSScriptAnalyzer HOWTO Sign PDF with SuisseID NoBrainer PUT/PATCH/MERGE with OData Service Client Apply Commit from one Repository to another GitHub Repository creation with PowerShell HOWTO Sign PowerShell Script with SuisseID Swissunihockey game schedule PDF generator HOWTO Maven Release on JetBrains TeamCity Limitations when running Activiti in H2 Embedded Mode HOWTO Pylint Integration in IntelliJ 14 HOWTO Install Windows 10 IoT Core on Raspberry PI 2 from a VM First steps with Dropwizard Allman code style for IntelliJ First steps with Flyway IntelliJ - Setup custom inspection profiles Configuration properties meta-data support in IntelliJ AMQP Integration with spring integration OCA Java SE 7 Programmer I Certification Guide Swissunihockey key matcher Swisscom SMS-API-Client Some hints concerning logging with Log4j IFTTT A brilliant service Run deltacloud on a VirtualBox-VM Project specific maven settings in IntelliJ AngularJS Javascript for beginners Liferay in Action