Office 365 and Access Engine

If you are developing an application using an Office 365 Access database, you may come across this error.

System.InvalidOperationException HResult=0x80131509 Message=The 'Microsoft.ACE.OLEDB.12.0' provider is not registered on the local machine. Source=System.Data StackTrace: at System.Data.OleDb.OleDbServicesWrapper.GetDataSource(OleDbConnectionString constr, DataSourceWrapper& datasrcWrapper) at 

If you are like myself, the instant reaction is to look for an Office 365 runtime. Indeed there is one available, however, this does not only not solving your problem, but it also overtake your proper "full" Access when you are trying open an accdb file.

So, what should I use then?  The answer is an Access Engine.  You may ask WTF, an Access Engine?  I am afraid I don't have an answer for this.  I can only say the Access Engine works but the runtime doesn't.  You can find the engine at:

https://www.microsoft.com/en-us/download/details.aspx?id=54920

After the installation, your application should be able to connect to OLEDB database as usual.

Here is an example I wrote:

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Data.OleDb;

namespace ConsoleApp1
{
    class Program
    {
        static void Main(string[] args)
        {
            string connectionString =
                @"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=SampleDatabase.accdb;Persist Security Info=False;";

            Console.WriteLine($"Connecction String: {connectionString}");

            using (OleDbConnection conn = new OleDbConnection(connectionString))
            {
                conn.Open();

                OleDbCommand cmd = conn.CreateCommand();
                cmd.CommandText = "SELECT TOP 1 message FROM Sample";

                string message = (string)cmd.ExecuteScalar();

                Console.WriteLine($"Message from database: {message}");
            }
        }
    }
}

If you still encounter the "Microsoft.ACE.OLEDB.12.0" error after installing the engine, you may want to change the platform target from ANY to x86 for 32bit or x64 for 64bit engine.

Updated 2020-02-26

If you have stability issues, e.g. SSIS project keeps crashing when using OLEDB to connect to a data source, you may want to downgrade the engine to 2010 (https://www.microsoft.com/en-gb/download/details.aspx?id=13255)