Microsoft.Jet.OLEDB.4.0 vs Microsoft.ACE.OLEDB.12.0, Which should I use?

Question

I'm developing a plugin for AutoCAD and need to read data from Microsoft Access. But I always got an Exception:

the 'microsoft.xxx.oledb.x.0' provider is not registered on the local machine

Windows 10 1803 + Office 365 + Microsoft Access Database Engine 2010 + Autodesk AutoCAD 2015

UnitTest code:

var path = "path_to_mdb_file");
var connectionString = $"Provider=Microsoft.Jet.OLEDB.4.0;Data source={path}";
using (var connection = new OleDbConnection(connectionString))
{
    connection.Open();
...

test passed

var path = "path_to_mdb_file");
var connectionString = $"Provider=Microsoft.ACE.OLEDB.12.0;Data source={path}";
using (var connection = new OleDbConnection(connectionString))
{
    connection.Open();
...

exception: the 'microsoft.ace.oledb.12.0' provider is not registered on the local machine

CAD plugin code:

var path = "path_to_mdb_file");
var connectionString = $"Provider=Microsoft.Jet.OLEDB.4.0;Data source={path}";
using (var connection = new OleDbConnection(connectionString))
{
    connection.Open();
...

exception: the 'Microsoft.Jet.OLEDB.4.0' provider is not registered on the local machine

var path = "path_to_mdb_file");
var connectionString = $"Provider=Microsoft.ACE.OLEDB.12.0;Data source={path}";
using (var connection = new OleDbConnection(connectionString))
{
    connection.Open();
...

everything is ok.

---

Same code, different result, Why?

Answer 1

Preface:

• This answer is concerned only with OLE-DB and not ODBC.
• Please make sure you understand the relationship (and differences) between JET Red (abbreviated to just "JET" in this answer) and the MS Access program installed as part of Office.

---

Part 1:

1. First, verify the exact JET version of your *.mdb version:
   • Do this by opening the *.mdb file in a hex editor and verifying the first 32 bytes of file contains the ASCII string "Standard Jet DB".
     • If it contains "Standard ACE DB" then you have an ACE database (*.accdb), not a JET Red (*.mdb) file that's just been renamed.
   • Look at the byte at offset 0x14:
     • If it's 0x00 then you have a JET 3.0 database (Access 95, Access 97).
     • If it's 0x01 then you have a JET 4.0 database (Access 2000, 2002, 2003).
     • If it's any other value then something is wrong.
2. If it's a JET 3.0 database, then you need to update it to JET 4.0 because JET 3.0 is not supported anymore and trying to make it work is a world of pain.
   • In my opinion, converting a JET 3.0 database to a JET 4.0 database is best done with Access 2003. You can use Access 2007 or Access 2010, but Access will try to fight you and convert it to an ACE database instead.
   • Note that Access 2013 and later does not support JET 3.0 at all.
3. If it's a JET 4.0 database, then read-on to Part 2:

---

Part 2 (for JET 4.0 databases):

• To work with JET 4.0 databases (not JET 3.0 databases!) with OLE-DB (this answer is not concerned with ODBC!) you can use either Microsoft.JET.OLEDB.4.0 or Microsoft.ACE.OLEDB.12.0 (or later).

  • At the time of writing (June 2020), the following versions of the Microsoft.ACE.OLEDB.* provider support JET 4.0 databases:
    • Microsoft.ACE.OLEDB.12.0 (Access 2007)
      • This version also supports JET 3.0, but only if it's the "real" version 12 and not the "fake" version 12. You will have the "fake" version if you have Office 2013 or later installed, or the Access Database Engine 2013 or later installed.
    • Microsoft.ACE.OLEDB.14.0 (Access 2010)
      • This version also supports JET 3.0.
    • Microsoft.ACE.OLEDB.15.0 (Access 2013)
      • This version does not support JET 3.0 anymore.
    • Microsoft.ACE.OLEDB.16.0 (Access 2016, Access 2019)
      • This version also does not support JET 3.0.

• Important note: The Microsoft.Jet.OLEDB.4.0 provider is only available for 32-bit (x86) programs.

• All versions of the Microsoft.ACE.OLEDB.* provider are available in both 32-bit and 64-bit versions and can be installed side-by-side.

  • Side-note: Office 2013 and later include the Microsoft.ACE.OLEDB.15.0 provider - but note that it also registers version 15 as an alias for Microsoft.ACE.OLEDB.12.0. This is the "fake" version I mentioned above.
    • This is because lots of software hard-coded Microsoft.ACE.OLEDB.12.0 in embedded connection-strings, which meant that they'd be broken if the software was run on a computer that had v14, v15, or v16 and didn't have v12 installed.
      • This is why you should always let your users edit your connection-strings directly, to avoid this problem.

---

Part 3 (for your specific question):

• Check if your Visual Studio Unit Test Process is 32-bit or 64-bit
  • See instructions here: https://learn.microsoft.com/en-us/visualstudio/test/run-a-unit-test-as-a-64-bit-process?view=vs-2019
• You should configure your Unit Test Process to run with the same ISA as your AutoCAD host-process:
  • So if AutoCAD is 32-bit, then your unit-tests should be 32-bit and not AnyCPU.
  • So if AutoCAD is 64-bit, then your unit-tests should be 64-bit and not AnyCPU.
• If you're running as 32-bit, then you can use either Microsoft.JET.OLEDB.4.0 or Microsoft.ACE.OLEDB.12.0.
  • The 32-bit Microsoft.JET.OLEDB.4.0 provider will be installed by-default on most Windows installations without needing Office or Access installed.
  • I don't believe that Microsoft.ACE.OLEDB.12.0 is installed by default in Windows.
• If you're running as 64-bit, then you can only use Microsoft.ACE.OLEDB.12.0 or later, provided you installed the 64-bit provider from the Access Database Engine installer.
  • Though you should consider installing the latest 2016 version instead of 2010 (and so using Microsoft.ACE.OLEDB.16.0).