Query 32 bit Access Database from 64 bit Sql Server

Question

Hi All,

I have a task to query the Accessdb from Sql Server and below are the configuration details:

OS: Windows Server 2008 R2 Enterprise( 64 bit)

Sql Server: Sql Server 2012 enterprise (64 Bit)

Access : MS Office 2010 (32 bit)

I used the below queries to reap the data from access db.

SELECT * FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0','D:\Product.accdb', Product) AS Product

(or)

SELECT * FROM OPENROWSET('MICROSOFT.ACE.OLEDB.12.0','D:\PRODUCT.ACCDB', PRODUCT) AS PRODUCT

following error returnd from both queries.

Error: "Msg 7438, Level 16, State 1, Line 2.    The 32-bit OLE DB provider "Microsoft.Jet.OLEDB.4.0" cannot be loaded in-process on a 64-bit SQL Server."

Please help.

---

Ram MSBI Developer

Answer 1

Hello,

The old JET data provider is not available on 64 bit systems, you have to use the 64 Bit ACE data provider (as in your 2nd query).

You can get ACE here: Microsoft Access Database Engine 2010 Redistributable

---

Olaf Helper

Blog Xing

Answer 2

Hmm, Is  that possible to create a SSIS package. There is an option to run the package as under 32 bit...

---

Best Regards,Uri Dimant SQL Server MVP, http://sqlblog.com/blogs/uri_dimant/

MS SQL optimization: MS SQL Development and Optimization
MS SQL Blog: Large scale of database and data cleansing
Remote DBA Services: Improves MS SQL Database Performance

Answer 3

Thanks for the update.

But still i am getting the below error.

"Cannot create an instance of OLE DB provider "Microsoft.jet.oledb.4.0" for linked server "(null)".'

---

Ram MSBI Developer

Answer 4

Try the below link:

http://blogs.msdn.com/b/spike/archive/2008/07/23/ole-db-provider-microsoft-jet-oledb-4-0-for-linked-server-null-returned-message-unspecified-error.aspx

---

Please use Marked as Answer if my post solved your problem and use Vote As Helpful if a post was useful.

Answer 5

"Cannot create an instance of OLE DB provider "Microsoft.jet.oledb.4.0" for linked server "(null)".'

Again, you have to use ACE, not JET

SELECT * 
FROM OPENROWSET('MICROSOFT.ACE.OLEDB.12.0','D:\PRODUCT.ACCDB', PRODUCT) AS PRODUCT

---

Olaf Helper

Blog Xing

Answer 6

With ACE, I'm still getting the below error after installing the Providers.

"The 32-bit OLE DB provider "Microsoft.ace.OLEDB.12.0" cannot be loaded in-process on a 64-bit SQL Server."

With JET, I'm getting below error.

Cannot create an instance of OLE DB provider "Microsoft.jet.oledb.4.0" for linked server "(null)"

---

Ram MSBI Developer

Answer 7

"The 32-bit OLE DB provider "Microsoft.ace.OLEDB.12.0" cannot be loaded in-process on a 64-bit SQL Server."

You have to install the 64 bit provider, of course, not then 32 bit.

---

Olaf Helper

Blog Xing

Answer 8

Following error is returned while installing 64 bit provider. 32 bit is installed successfully but Sql query is not working.



---

Ram MSBI Developer

Answer 9

ITs self explanatory that you need to remove the 32 bit Office product and try installing 64 bit. Did you try it?

---

Please use Marked as Answer if my post solved your problem and use Vote As Helpful if a post was useful.

Answer 10

Thanks Latheesh.

I will try that.

---

Ram MSBI Developer

Answer 11

I have the same situation as Latheesh, however installing the 64bit version of Office in our company is not an option at this time. Is there any other option?

Answer 12

not sure if anyone is watching this thread anymore but i have this scenario. i am using the excel connection manager in SSIS to create an excel spreadsheet with VS 2012.  To do this from what I have read I am required to use the 32-bit driver for "Microsoft Access database engine 2007", if I use the 64-bit driver I cannot connect to the Excel connection manager.   i get 0xc020801c message.

i can install the 32-bit "Microsoft Access database engine 2007" and resolve this problem.  but i also have a stored procedure that creates a spreadsheet (i use this because i have to leave some cells intact), it appears to require the 64-bit "Microsoft Access database engine 2007".  in 32-bit mode i get the following message

Msg 7438, Level 16, State 1, Procedure sp_CreateGRfile, Line 26
The 32-bit OLE DB provider "Microsoft.ACE.OLEDB.12.0" cannot be loaded in-process on a 64-bit SQL Server.

so i need both the 32-bit  "Microsoft Access database engine 2007" and 64-bit  "Microsoft Access database engine 2007".

any thoughts on getting around this issue?

Answer 13

From an elevated command prompt:

AccessDatabaseEngine_x64.exe /passive

It will install in quitet mode. Check Add/Remove programs and it should be there

Answer 14

From an elevated command prompt:

AccessDatabaseEngine_x64.exe /passive

It will install in quitet mode. Check Add/Remove programs and it should be there

Fabulous, thanks!

Opinion: I cannot believe that such an ugly problem exists, requiring an equally ugly hack to work around.  Surely there are tens of thousands of 64-bit SQL Server users who must also work with 32-bit office?  (Note: I wonder if this will cause issues when applying updates because the name and version appear to be identical to the existing 32 bit version in "Add or remove programs"?)

Answer 15

Fabulous, thanks!

Opinion: I cannot believe that such an ugly problem exists, requiring an equally ugly hack to work around.  Surely there are tens of thousands of 64-bit SQL Server users who must also work with 32-bit office?  (Note: I wonder if this will cause issues when applying updates because the name and version appear to be identical to the existing 32 bit version in "Add or remove programs"?)

I spoke to soon.  SQL Server can now read the spreadsheet, but Access 2010 will now cease to load!  Uninstalling the 64 bit version, identifiable by the date in "Add or remove programs", restores things to how they were, except for perhaps new updates appearing in Windows Update which will need to be ignored if you chose to remove the driver.  Inexperienced users: tread cautiously!

Answer 16

YES!! this works :)