Using Microsoft Access in .NET Core

Microsoft Access and .NET Core

Microsoft Access is a relational database application that is part of the Microsoft Office family. While there are a lot of other database system that is more popular with .NET such as MySQL, SQL Server, or PostgreSQL, there are some older application that still uses Microsoft Access as its database system.

I have recently been tasked to create an integration to a legacy system that is still using Microsoft Access (.mdb) for its database. We’re using ASP.NET Core for the new system. And seeing that ASP.NET Core is a cross platform framework, we need to figure out how to connect to a Microsoft Access database from ASP.NET Core.

While we can use OLE DB if we’re using the .NET Framework, Microsoft has stated that OLE DB won’t be supported for .NET Core.

And so we can use the ODBC for connecting to Microsoft Access. With recent effort for increasing the APIs to help developers move to .NET Core, Microsoft provided a System.Data.Odbc which support .NET Standard 2.0.

Next is you will need the Microsoft Access Database Engine in Windows. For Linux database engine, there is one that I know of but it is not free and can be fairly expensive.

The Microsoft Access Database Engine is available through Microsoft website. But there is also exist a chocolatey package here.

Chocolatey branded themselves as “The package manager for Windows”. So you might think of it as DPKG for Debian/Ubuntu or RPM for Red Hat.

Getting started with chocolatey is quite easy, you can just install it using command prompt and run below command

@"%SystemRoot%\System32\WindowsPowerShell\v1.0\powershell.exe" -NoProfile -InputFormat None -ExecutionPolicy Bypass -Command "iex ((New-Object System.Net.WebClient).DownloadString('https://chocolatey.org/install.ps1'))" && SET "PATH=%PATH%;%ALLUSERSPROFILE%\chocolatey\bin"

After that you can just install the Microsoft Access Database Engine using below command

choco install msaccess2010-redist-x64

With that done, we can start to code for accessing Microsoft Access from .NET Core

Connecting to Microsoft Access

Before we can connect to Microsoft Access, we must create a new .NET Core console application

mkdir newapp
cd newapp
dotnet new console

We can then add the System.Data.Odbc to our application

dotnet add package System.Data.Odbc

And I might add Dapper to this application as it simplifies our code greatly.

dotnet add package Dapper

We will need a connection string to connect to Microsoft Access. The common format for the connection string is like below

//If the Access file does not have a password
"Driver={Microsoft Access Driver (*.mdb, *.accdb)};Dbq=C:/location/to/mdb/file"
//If the Access file has a password
"Driver={Microsoft Access Driver (*.mdb, *.accdb)};Dbq=C:/location/to/mdb/file;Uid=Admin;Pwd=Password;"

See below for sample code of connecting to Microsoft Access

Summary

In this short post, we have learn that you can still connect to Microsoft Access from .NET Core even though there is no support for OLE DB. Keep in mind that the Access Database Engine primarily distributed by Microsoft for Windows machine. It doesn’t mean that Linux doesn’t have, it’s only that it’s pricey.

Happy Coding !