SQL Server 2014 - Cannot create an instance of OLE DB provider "Microsoft.ACE.OLEDB.12.0"

I'm having the rather common problem in SQL Server trying to import data using OPENROWSET:

Msg 7302, Level 16, State 1, Line 24
Cannot create an instance of OLE DB provider "Microsoft.ACE.OLEDB.12.0" for linked server "(null)".

Open in new window


I'm running SQL Server 2014 Standard, Office 365 - 2016 64bit. I've also installed the AccessDatabaseEngine_X64. Both versions 12 and 16 of the OLEDB provider are showing in the providers list in SQL Management Studio.

I'm trying to run the following:

EXEC sp_configure 'show advanced options', 1; RECONFIGURE;
EXEC sp_configure 'Ad Hoc Distributed Queries', 1; RECONFIGURE;

EXEC master.dbo.sp_MSset_oledb_prop N'Microsoft.ACE.OLEDB.12.0', N'AllowInProcess', 1 
EXEC master.dbo.sp_MSset_oledb_prop N'Microsoft.ACE.OLEDB.12.0', N'DynamicParameters', 1 

SELECT * FROM OPENROWSET('Microsoft.ACE.OLEDB.12.0', 'C:\Import2263.mdb';  'admin';'',IMPORT);

Open in new window


I've added Everyone with full control over all the temp folders I can find and the source mdb file. I'm running the script as sa. I've tried running the MSSQLSERVER account as local system and as the local administrator. No luck.

The only thing I haven't tried is a clean install. The server has had 32bit office running which I uninstalled before installing 64bit. Just rather a nuclear option.

I can't see what I'm missing - any help much appreciated.
charlesdeanAsked:
Who is Participating?
 
Eugene ZConnect With a Mentor Commented:
"clean install" sounds promising
try to reboot first and try again
and try to Import-Export 64-bit Excel -- > 
"SSIS"  to see if driver there
if reboot did not help and you do not see Excell 2010 diver in sql export-import 64 bit

make sure you have installed
Microsoft.ACE.OLEDB.12.0 for Windows, 64 bit  or up
https://www.microsoft.com/en-us/download/details.aspx?id=13255
---tryy
0
 
pcelbaCommented:
If OPENROWSET does not work for you then try to create Linked Server instance (in SSMS) and if you succeed then use it in OPENQUERY.

The Linked Server creation could provide better error messaging.
0
 
charlesdeanAuthor Commented:
Pcelba, many thanks for your comment. I should have said earlier - this is an automatic process driven by a web page in order to import one or more tables from MDB files that have been uploaded. This means it needs to be a solution that works from inside a stored procedure - hence OPENROWSET. I've done this before for Excel files so there must be a solution somewhere!
0
Cloud Class® Course: CompTIA Cloud+

The CompTIA Cloud+ Basic training course will teach you about cloud concepts and models, data storage, networking, and network infrastructure.

 
pcelbaCommented:
OK, it would be just a try to know whether you are able to read some data over the Microsoft.ACE.OLEDB.12.0 driver...

You may also try following command:
SELECT * FROM OPENDATASOURCE('Microsoft.ACE.OLEDB.12.0','Data Source=C:\Import2263.mdb')...[Table]
0
 
pcelbaCommented:
Some sources also recommend to test  SELECT * FROM OPENROWSET ...  from SSMS running as Administrator.
0
 
charlesdeanAuthor Commented:
I've tried the OPENDATASOURCE - exactly the same error.

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

Open in new window


I'm running all these scripts as sa in SSMS.

I've also tried using the Import Wizard and again same error about cannon create instance etc.
0
 
pcelbaConnect With a Mentor Commented:
To execute the scripts as sa in SSMS is something different than to run SSMS as Administrator.
OTOH, I don't know what should SSMS do differently in such case because all actions are running under SQL Server Service user.

Are you sure the installed SQL Server is 64 bit?
0
 
pcelbaCommented:
The Process Monitor should show what action fails in conjunction with Microsoft.ACE.OLEDB.12.0 driver. But to find the true reason of the problem will be difficult as the Process Monitor also shows plenty of other messages.
0
 
Mike in ITIT System AdministratorCommented:
You should check the error log to see if there is another error that comes before this one. Usually, something like this is paired with another. You can find the error logs at:
%Program-Files%\Microsoft SQL Server\MSSQL.1MSSQL\LOG\ERRORLOG
or you should be able to get to it by going to the Server Node in SSMS and expanding Management and clicking on SQL Server Logs
0
 
pcelbaCommented:
If the error logs investigation or clean install does not help then I would propose a different solution:

Create a short program in C# (or in VB.NET or even in Visual FoxPro) which will read Access data and export them to SQL Server db. Then you may do whatever you need on SQL Server.
The C# import/export is easier to debug/manage because it can use ODBC/OLE DB drivers which work reliably in this environment.

Of course, you may also try to create SSIS package for Access data import but it could bring the same problems as you have now.
0
 
charlesdeanAuthor Commented:
I finally worked out what I'd done wrong. I had installed the 32bit version of the Access Database Engine. This was because the 64 bit one had failed. This article sorted that problem.

https://stackoverflow.com/questions/33265663/api-ms-win-crt-runtime-l1-1-0-dll-is-missing-when-opening-microsoft-office-file

Then I could get the 64 bit version of ADE installed and working.

That fixed things.

Eugene many thanks, and pcelba for your comments.
0
 
charlesdeanAuthor Commented:
Many Thanks!
0
 
pcelbaCommented:
LOL, the question presumption was "I've also installed the AccessDatabaseEngine_X64" :-)

Anyway, good to read it is working for you now!

Thanks.
0
 
charlesdeanAuthor Commented:
Yes indeed! Attention to detail - it's usually the answer :)
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.