54

It has been a good number of years since I did some programming with Classic ASP and Microsoft Access where we used "Microsoft.Jet.Oledb" driver to access and display the data.

I have been asked to do some work with accessing MS Excel data using "Microsoft.Ace.Oledb". I have found this to be part of the "Microsoft Access 2010 Engine Redistributable" download.

I would like to know if "Microsoft.Jet.OleDb" has replaced "Microsoft.Ace.Oledb" driver and are essentially the same or are they completely different things?

Also, do you normally get the "Microsoft.Ace.Oledb" driver when you buy MS Access 2010?

Kara
  • 5,650
  • 15
  • 48
  • 55
RT88
  • 639
  • 2
  • 6
  • 9

2 Answers2

76

It's mainly a matter of history, effectively ACE has superceded JET:

Wikipedia answers your question in great detail.

The most relevant sections are:

With version 2007 onwards, Access includes an Office-specific version of Jet, initially called the Office Access Connectivity Engine (ACE), but which is now called the Access Database Engine. This engine is fully backward-compatible with previous versions of the Jet engine, so it reads and writes (.mdb) files from earlier Access versions. It introduces a new default file format, (.accdb), that brings several improvements to Access, including complex data types such as multivalue fields, the attachment data type and history tracking in memo fields. It also brings security and encryption improvements and enables integration with Microsoft Windows SharePoint Services 3.0 and Microsoft Office Outlook 2007

In addition, ACE provides a 64-bit driver, so can be used on 64-bit machines, whereas JET cannot.

The driver is not part of the Windows operating system, but is available as a redistributable.[11] Previously the Jet Database Engine was only 32-bit and did not run natively under 64-bit versions of Windows.

As for the second part of your question, I recently installed Office 2010, and I had to download the ACE components separately. I got them from the link Microsoft Access Database Engine 2010 Redistributable. This is likely because I had installed a 32-bit version of Office under 64-bit Windows; in any case, the necessary files are easy to obtain from Microsoft.

dash
  • 84,786
  • 4
  • 48
  • 69
  • Been a very long time since I did any Office work? You're a lifesaver thanks! – RT88 Jan 18 '13 at 15:16
  • Do note that Microsoft states a lot of (surprising) disclaimers on the [ADE Download page](https://www.microsoft.com/en-us/download/details.aspx?id=54920), for example, that (1) ADE is *not* a general replacement for ACE, (2) ADE is *not* a replacement for Jet [sic!] in server-side applications and (3) that using it in web applications or service components is not supported. – Heinzi Aug 09 '17 at 14:11
  • @Heinzi So what's the difference between ADE and ACE? – Dai Jun 17 '20 at 02:57
7

The drivers are essentially the same when used for basic operations, and show notable difference with more complex stuff (unions, nested queries, etc).

Based on personal experience, ACE does not provide fully backward compatible results. It may open and read/write the previous .mdb format but there are changes in data type casting of exactly the same queries.

For example, when using UNION on TEXT fields, where JET used to return TEXT(255) result, ACE returns MEMO ?!

This can produce a lot of trouble in combination with BI or reporting tools like Crystal Reports.

Milan Oparnica
  • 150
  • 1
  • 6
  • 3
    It looks like ACE 2016 resolved this problem. Can't find any incompatibilities with Jet for now. – Milan Oparnica Dec 28 '17 at 13:35
  • 2
    I'd like to say that I ran into an issue using ACE that seemed to be resolved by JET. When doing a join with two tables, the first 10 rows of a particular column were blank. ACE decided to ignore the rest of the data in the column and not bring over anything. This is quite vexing and confusing. Oddly, switching to JET brought all the data over. I would prefer to use ACE as I've been using it, but JET seems to have returned the correct results and done so more quickly than ACE. I'm a bit frustrated and confused – user1274820 Jul 23 '18 at 23:36