'Microsoft.ACE.OLEDB.12.0' 64x Sql Server and 86x Office?

64-bit oledb 32-bit openrowset
Chris Hayes picture Chris Hayes · May 24, 2010 · Viewed 54.2k times · Source

The error:

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.

And the answers I'm seeing is a conflict between 64 bit Sql Server and 32 bit Office. Is there a way to run an openrowset on Excel into Sql Server?

insert into dbo.FiscalCalendar 
select * from 
openrowset('Microsoft.ACE.OLEDB.12.0',
'Excel 12.0 Xml;Database=C:\Users\uname\Desktop\fy11.xlsx;',
'Select * from [Sheet1]')

Answer

user1390375 picture user1390375 · Feb 28, 2014

...the key is to install the 64-bit Access engine using the /passive flag:

c:>AccessDatabaseEngine_64 /passive

Related questions

Microsoft.ACE.OLEDB.12.0 Provider can no longer open .mdb under Windows 8

I have an x64 application which uses Microsoft.ACE.OLEDB.12.0 provider to read an MS Access Database on Windows 7 (x64) and it works fine (Office 2010 installed). The same application running under Windows 8 with Office 2013 installed, fails with a message saying …

Read More
OleDB not supported in 64bit mode?

I've been using Microsoft.Jet.OLEDB.4.0 and Microsoft.ACE.OLEDB.12.0 to read in .csv, .xls, and .xlsx files. I just found out that neither of these technologies are supported in native 64bit mode! I have 2 questions: What is the supported …

Read More
'Microsoft.ACE.OLEDB.12.0' provider is not registered -but it is

Short and sweet: app works on x86 machine but not on x64 (tried on a 2008 Server and a Windows 8) Microsoft Access Database Engine 2010 already installed (also have Microsoft Office 2007 Access Database Engine installed) MS Office not installed confirmed the existence …

Read More