Difference between Microsoft.Jet.OleDb and Microsoft.Ace.OleDb

Question

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?

Answer 1

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.

Answer 2

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.