Microsoft.ACE.OLEDB.12.0 Issues and their solution

Few OLEDB issues that I would like to share;

1.       Msg 7415, Level 16, State 1, Line 1

Ad hoc access to OLE DB provider 'Microsoft.ACE.OLEDB.12.0' has been denied. You must access this provider through a linked server.

If you see this message with your OLEDB query, it means you do not have sufficient rights on the OLEDB source. Please check your user have enough rights to access the OLEDB provider. The provider must show in the list of providers as below;

Secondly, right click the provider and check “Disallow adhoc Access”. It should be unchecked.

               

2.       Msg 7302, Level 16, State 1, Line 1

Cannot create an instance of OLE DB provider "Microsoft.ACE.OLEDB.12.0" for linked server "(null)".

Check the above issues stated in point 1. If above settings are good, and still getting this error, then it your syntax. Please check the correct syntax to query using the Provider. For example, to query excel file, the correct syntax is;

SELECT *

FROM OPENROWSET('Microsoft.ACE.OLEDB.12.0',

'Excel 12.0;Database=F:\FleName.xlsx',

                           'select * from [sheet1$]')

For csv, the syntax is;

SELECT *

               FROM OPENROWSET ('Microsoft.ACE.OLEDB.12.0',

                      'Text;Database=E:\',

                      'SELECT * from FileName.csv')

Please note that in case of excel, File name is give in the “Database” property and sheet name is given in the “Select * from [sheet1$]” statement.

In case of csv, only path is given for “Database” property and file name is given in the “Select * from Filaname.csv” part.

3.  OLE DB provider 'Microsoft.ACE.OLEDB.12.0' cannot be used for distributed queries because the provider is configured to run in single-threaded apartment mode.

You need to install 64 bit or 32 bit Access driver depending upon the operating system that you have on the machine where you are running the query to access the excel or csv file.

4.       Few columns returning NULL in the dataset

You might see this issue where your values are turning into NULL. If you look deeply into your data, you will notice that the values in the column are numbers (not alpha numeric charachters) and the values that are alpha numeric in the parent file are turning into NULL. This is because the data type of the column is determined based up on the first few data values in the column. If these are numbers, the datatype will be set to “int” and all character values will be saved as NULL.

To solve this issue, you need to do some changes in the registry as shown in the screen below;

Open registry of the server in which you are processing the csv / Excel file. Go to the following path;

Computer\HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Office\15.0\Access Connectivity Engine\Engines\Text

We have selected “Text” as we faced this issue while processing CSV files.

Change “ImportMixedTypes” property value to “Text”. Save the registry. Execute the query again and you will now see all your data returning as Text. You can no move your data as per your table schema.