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?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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
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
Protecting & Securing Your Critical Data

Considering 93 percent of companies file for bankruptcy within 12 months of a disaster that blocked access to their data for 10 days or more, planning for the worst is just smart business. Learn how Acronis Backup integrates security at every stage

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
pcelbaCommented:
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
EugeneZ ZhitomirskySQL SERVER EXPERTCommented:
"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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Office

From novice to tech pro — start learning today.