MSAccess in DotNetCore

I have been struggling with using msaccess databases from dotnetcore applications.

There are several approaches, but I think the easiest is to use ODBC.

So lets try with a simple program like the following:

using System;
using System.Data.Odbc;
internal class Program
{
    private static void Main(string[] args)
    {
        string connetionString = null;
        OdbcConnection cnn;
        connetionString = @"Driver={Microsoft Access Driver (*.mdb, *.accdb)};DBQ=F:\SKS_Web\Upgraded\Orders.mdb";
        cnn = new OdbcConnection(connetionString);
        try
        {
            cnn.Open();
            Console.WriteLine("Connection Open ! ");
            cnn.Close();
        }
        catch (Exception ex)
        {
            Console.WriteLine("Can not open connection ! " + ex.Message);
        }
    }
}

You don't need to have msaccess installed but you might need to use the [MS Access Database Engine Redistributable 2010](https://www.microsoft.com/en-us/download/details.aspx?id=13255)

Another alternative is mdbtools. I used this approach on my Windows 10 Bash Tools and it is valid for Mac and other Linux variants.

I followed these steps:

  1. cd /mtn/f  (because I use f as my drive)
  2. git clone https://github.com/brianb/mdbtools.git
  3. cd mdbtools
  4. sudo apt-get update
  5. sudo apt install autoconf
  6. sudo apt install unixodbc
  7. sudo apt install unixodbc-dev
  8. sudo apt install glib2.0
  9. sudo apt install flex
  10. sudo apt install bison
  11. ./configure --with-unixodbc=/usr/local --disable-man
  12. make
  13. sudo ldconfig

After that install dotnet core following: https://www.microsoft.com/net/download/linux-package-manager/ubuntu18-04/sdk-current

In my case: (after running lsb_release -a to determine my version)

wget -q https://packages.microsoft.com/config/ubuntu/16.04/packages-microsoft-prod.deb
sudo dpkg -i packages-microsoft-prod.deb

And then

sudo add-apt-repository universe
sudo apt-get install apt-transport-https
sudo apt-get update
sudo apt-get install dotnet-sdk-2.1

Now we need to configure the MDBTools driver

sudo vi /etc/odbcinst.ini

And edit it adding a section like:

[MDBTools]
Description = MDBTools Driver
Driver=/usr/local/lib/libmdbodbc.so
Setup=/usr/local/lib/libmdbodbc.so
FileUsage=1
UsageCount=1

And edit odbc.ini with a section like:

[testdb] 
Description = test 
Driver = MDBTools 
Database = /mnt/f/mdbtools/Orders.mdb

Because my file is in f:/mdbtools/Orders.mdb

And now to make the same program on .NET core for *nix.

Create the dotnet project
    dotnet new console -name program1

Edit the program to something like:

using System;
using System.Data.Odbc;
internal class Program
{
    private static void Main(string[] args)
    {
        string connetionString = null;
        OdbcConnection cnn;
        connetionString = @"MDBTools;DSN=testdb";
        cnn = new OdbcConnection(connetionString);
        try
        {
            cnn.Open();
            Console.WriteLine("Connection Open ! ");
            cnn.Close();
        }
        catch (Exception ex)
        {
            Console.WriteLine("Can not open connection ! " + ex.Message);
        }
    }
}

Notice that the string conection format is not the same.

install the odbc nuget using dotnet add package System.Data.Odbc

And run the program with dotnet run

Ok. Thanks that's all. It's nice that my family was too excited with Pokemon Let's go to notice that I was on my computer :)