SQL Server - How to Install the Microsoft.ACE.OLEDB.12.0 and Microsoft.Jet.OLEDB.4.0 Drivers

How to check which OLEDB providers are installed

To check which providers you have installed, you can run this query:

Or by Management Studio:

Differences Between ACE OLEDB and Jet OLEDB

Very similar, but at the same time different. For basic operations such as INSERT, UPDATE, DELETE, and SELECT, you will hardly notice any difference between these two OLEDB drivers, as they have the same parameters and work exactly the same. But what changes between them? Why two drivers?

Launched on 1992, the JET driver has long met the need for many developers, providing easy and convenient integration between different data sources, abstracting technical issues. With the emergence of Windows on the x64 (64 bits) platform, JET began to no longer serve developers as the driver had native support only on the x86 (32 bits) platform and to be able to access MDB databases and other data sources. , it was necessary to use 32 bit software that acted as a proxy.

Aware of this scenario, Microsoft released Office 2007, and with it, a new version of JET, now called the Office Access Connectivity Engine (ACE), which enabled compatibility with JET 4.0 and its earlier versions and supported the new Access format. (.accdb), which brought several new features to Access, such as multivalued fields, security enhancements, and encryption. Nevertheless, ACE has not retained some important functions of the XET version of JET, such as replication features and user-level security.

With Access 2010, the ACE driver has been supported by the 64 bits platform, being considered in essence an 64 bits version of the JET driver.

Looking at 2 drivers, we see that they are very similar for simple operations, but when we involve union, join, nested queries, and others, chances are the results are not the same. The ACE driver does not have as much support for older files as JET, so if you open these files in older versions of Access, such as when you are UNIONing fields of type TEXT, where JET returns TEXT (255), ACE returns MEMO.

Therefore, if you are using an 32 bit operating system (which is not currently recommended), you can choose between ACE and JET. If you are using an 64 bit version, you can only use ACE. My recommendation? Use the ACE.

Installing the Microsoft Jet OLEDB Driver

As I had already mentioned, the JET OLEDB driver does not work in 64 bit environments. So I had to create another VM 32 bits to do the installation and show it to you.

As you can see, I'm using Windows Server 2008 R2 x86 and SQL Server 2012 and the JET provider is now available for use without installing anything. I just installed the operating system updates (newly installed) and then installed SQL Server.

Example file:

Usage examples:

Note that to use the JET driver, I had to convert my Office 2016 XLSX spreadsheet to Office 2003 XLS format, and change in my query the Excel version for 8.0.

If I try to import XLSX, we will see this error message:

Msg 7399, Level 16, State 1, Line 2
The OLE DB provider “Microsoft.Jet.OLEDB.4.0” for linked server “(null)” reported an error. The provider did not give any information about the error.
Msg 7303, Level 16, State 1, Line 2
Cannot initialize the data source object of OLE DB provider “Microsoft.Jet.OLEDB.4.0” for linked server “(null)”.

If I try to import XLS but do not change the Excel version to 8.0 in my query, we will see this error message:

OLE DB provider “Microsoft.Jet.OLEDB.4.0” for linked server “(null)” returned message “Could not find installable ISAM.”.
Msg 7303, Level 16, State 1, Line 2
Cannot initialize the data source object of OLE DB provider “Microsoft.Jet.OLEDB.4.0” for linked server “(null)”.

The Microsoft.Jet.OLEDB.4.0 driver is for use on 32 bit operating systems, and supports Excel files up to the 2003 version. Above that, you must use ACE DB.

Installing the Microsoft ACE OLEDB Driver

Widely used, especially for 64 bit operating system support, the ACE driver should be installed using one of the links below:
- 2007 Office System Driver: Data Connectivity Components (32 bits)
- Microsoft Access Database Engine 2010 Redistributable (32 and 64 bits)

After installation is complete, providers and drivers will be available for use on SQL Server (no restart required).

Example file:

Using OPENROWSET and OPENDATASOURCE with the ACE DB 12.0 driver:

The Microsoft.ACE.OLEDB.12.0 driver can be used on 32 bits operating systems to open Excel files up to the 2007 version and can be used on 64 bits operating systems and in this edition, can open Excel files of any version.

Note that you cannot install the ACE OLEDB 64 bits driver if Microsoft Office 2007-2016 x86 (32 bits) is installed. That is, if you are using SQL Server 64 bits and you have installed Microsoft Office 32 bits, you will not be able to use the OPENROWSET / OPENDATASOURCE functions to open Excel files, and you may encounter this error message:

Msg 7403, Level 16, State 1, Line 1
The OLE DB provider “Microsoft.ACE.OLEDB.12.0” has not been registered.

How to enable distributed transactions

One of the most common errors when using OLE DB providers is not enabling the Ad Hoc Distributed Queries feature. When this occurs, you will come across this error message:

Msg 15281, Level 16, State 1, Line 1
SQL Server blocked access to STATEMENT 'OpenRowset / OpenDatasource' of component
'Ad Hoc Distributed Queries' because this component is turned off as part of
the security configuration for this server.
A system administrator can enable the use of 'Ad Hoc Distributed Queries'
by using sp_configure.
For more information about enabling 'Ad Hoc Distributed Queries',
see “Surface Area Configuration” in SQL Server Books Online.

To solve this problem, it is very simple:

Setting ACE OLEDB Properties

Another issue that may occur when trying to use the Microsoft ACE OLEDB driver is not enabling the AllowInProcess and DynamicParameters features and encountering the error message below:

Msg 7399, Level 16, State 1, Line 1
The OLE DB provider “Microsoft.ACE.OLEDB.12.0” for linked server “(null)” reported an error. The provider did not give any information about the error.
Msg 7330, Level 16, State 2, Line 1
Cannot fetch a row from OLE DB provider “Microsoft.ACE.OLEDB.12.0” for linked server “(null)”.

If this happens to you, simply run the following commands to enable these features:

Granting permissions to the TEMP directory (32 bits)

This kind of problem only occurs with SQL Server x86 (32 bits) and so it should not disturb so many people. This is because SQL Server creates temporary files while executing queries using the provider, using the credentials of the user running the query. The error message generated looks something like this:

OLE DB provider “Microsoft.Jet.OLEDB.4.0” for linked server “(null)” returned message “Unspecified error”.
Msg 7303, Level 16, State 1, Line 1
Cannot initialize the data source object of OLE DB provider “Microsoft.Jet.OLEDB.4.0” for linked server “(null)”.

If SQL Server is running using the Network Service account, the temporary directory should be something like: C: \ Windows \ ServiceProfiles \NetworkService\ AppData \ Local \ Temp

If SQL Server is running using the Local Service account, the temporary directory should be something like: C: \ Windows \ ServiceProfiles \LocalService\ AppData \ Local \ Temp

In this case, we must grant read and write permission to all users in this directory or only to users who execute this type of query. This can be done with a command similar to this:

In the example above, I created the command for setting permissions in case SQL Server is running using the NetworkService account and the user used to run the queries is "vs".