locked
Query 32 bit Access Database from 64 bit Sql Server RRS feed

  • 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.

    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 Xing

    Tuesday, 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 PM
    Answerer

  • 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:

    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.

    Tuesday, July 16, 2013 12:55 PM
  • "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


    Tuesday, July 16, 2013 1:06 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
  • "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

    Wednesday, July 17, 2013 6:34 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.

    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"?)

    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!
    • 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