Answered by:
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
Tuesday, July 16, 2013 12:22 PM
Answers
-
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.
- Proposed as answer by Allen Li - MSFT Wednesday, July 17, 2013 8:45 AM
- Marked as answer by Allen Li - MSFT Wednesday, July 24, 2013 8:10 AM
Wednesday, July 17, 2013 6:44 AM
All replies
-
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 XingTuesday, July 16, 2013 12:27 PM -
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
Tuesday, July 16, 2013 12:27 PMAnswerer -
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
Tuesday, July 16, 2013 12:53 PM -
Try the below link:
Please use Marked as Answer if my post solved your problem and use Vote As Helpful if a post was useful.
Tuesday, July 16, 2013 12:55 PM -
-
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
Wednesday, July 17, 2013 6:17 AM -
-
Following error is returned while installing 64 bit provider. 32 bit is installed successfully but Sql query is not working.
Ram MSBI Developer
Wednesday, July 17, 2013 6:42 AM -
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.
- Proposed as answer by Allen Li - MSFT Wednesday, July 17, 2013 8:45 AM
- Marked as answer by Allen Li - MSFT Wednesday, July 24, 2013 8:10 AM
Wednesday, July 17, 2013 6:44 AM -
Thanks Latheesh.
I will try that.
Ram MSBI Developer
Wednesday, July 17, 2013 6:56 AM -
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?Wednesday, January 29, 2014 3:19 PM
-
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?
Friday, April 24, 2015 5:40 PM -
From an elevated command prompt:
AccessDatabaseEngine_x64.exe /passive
It will install in quitet mode. Check Add/Remove programs and it should be there
- Edited by Frank - SSPC Tuesday, July 14, 2015 7:12 PM
- Proposed as answer by Hopk1ns Monday, October 12, 2015 2:01 PM
Tuesday, July 14, 2015 7:10 PM -
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"?)
Monday, October 12, 2015 2:01 PM -
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"?)
- Edited by Hopk1ns Monday, October 12, 2015 3:05 PM
Monday, October 12, 2015 2:41 PM -
YES!! this works :)Tuesday, November 24, 2015 6:48 PM