none
The 'Microsoft.ACE.OLEDB.12.0' provider is not registered on the local machine. (System.Data)

Answers

  • Hi Rune,

    As you pointed, you may have to install these Office 2010 System Driver data connectivity components to connect office data sources (such as Excel, Access) from non-Microsoft Office applications. You can use the following statement to access the Excel data source:

    SELECT * FROM OPENROWSET('Microsoft.ACE.OLEDB.12.0','EXCEL 12.0;Database=D:\test.xls;HDR=YES;IMEX=1','SELECT * FROM [sheet1$]');
    
    

    >>I installed AccessDatabaseEngine_x64 without success.
    What was the error message you received? Is the Operating System supported by the Microsoft Access Database Engine 2010 Redistributable with System requirements on the link above? Could you please try to uninstall it and install again?

    For more information about access to Office 2010:
    http://www.layer2.de/en/community/FAQs/BDLC/Pages/How-to-access-office-2010-data-from-sharepoint.aspx
    http://www.arboundy.com/2010/11/the-microsoft.ace.oledb.12.0-provider-is-not-registered-on-the-local-machine


    Best Regards,
    Stephanie Lv

    Forum Support
    Please remember to mark the replies as answers if they help and unmark them if they provide no help. If you have feedback for TechNet Subscriber Support, contact tnmff@microsoft.com.
    • Marked as answer by MyRBC Saturday, December 31, 2011 3:32 PM
    Monday, September 26, 2011 2:12 AM
  • Try using ACE.14.0 instead of 12.0 using following. do change your excel filename/sheetname in following query

     

    SELECT * FROM OPENROWSET('Microsoft.ACE.OLEDB.14.0','EXCEL 14.0;Database=D:\test.xls;HDR=YES;IMEX=1','SELECT * FROM [sheet1$]');
    



    Mark as Answer If Reply Was Helpful
    Thanks
    Kuldeep Bisht
    Technical Lead @ Simplion Technologies
    Blog : www.dbsimplified.com
    • Marked as answer by MyRBC Saturday, December 31, 2011 3:32 PM
    Monday, September 26, 2011 8:41 AM
  • The reason why you can't see "Office 2010" in your SQL is most likely because of the unsucceeded installation of the ACE 64-bit driver / provider.

    What is the error when you install AccessDatabaseEngine_x64 without success. Are you getting the error about "64-bit ACE cannot be installed along with a 32-bit Office installation"? If so, please see the related thread at: http://support.microsoft.com/kb/2269468

     

    Thanks,
    Ming.
    WDAC Team, Microsoft.


    Pak-Ming Cheung - MSFT
    • Marked as answer by MyRBC Saturday, December 31, 2011 3:32 PM
    Friday, September 30, 2011 2:58 AM
    Answerer
  • So you are saying that ACE 14.0 is not native to an installation of Office 2010 professional? Basically my team will need to package AccessDatabaseEngine for 2007 with our current code or change code to handle ACE 14.0 + AccessDatabaseEngine for 2010.  Please Correct me if I am wrong.

     

    Thanks,

    Mike

    • Marked as answer by MyRBC Saturday, December 31, 2011 3:33 PM
    Thursday, October 06, 2011 4:05 PM
  • But you have mentioned " I installed AccessDatabaseEngine_x64 without success" in your first post.

    So, i am now confused. Do you get it installed successfully or not? Please clarify.

    ==============================

    btw, you said that there is an option about "Excel 2007" but not "Excel 2010" in SQL Server. So, i wonder whether you can use the option "Excel 2007" to import the Excel 2010 file - both of them are xlsX.

    Thanks,
    Ming.
    WDAC Team, Microsoft.


    Pak-Ming Cheung - MSFT
    • Marked as answer by MyRBC Saturday, December 31, 2011 3:33 PM
    Saturday, October 08, 2011 2:21 AM
    Answerer
  • Currently, I do not have a 64-bit SQL and 64-bit Office for testing handy.

    However, I tried the 32-bit environment and I also found that there is no "Excel 2010" but only "Excel 2007", when I use "Import Data" and select "Microsoft Excel" in the "Data Source" textfield in the dialog "SQL Server Import and Export Wizard". (I have installed Office 2010 in my machine, but not Office 2007).

     

    But I can successfully import the data into my SQL tables with the option "Excel 2007". So, I wonder whether you have tried the "Excel 2007" option or not. Actually, both Excel 2007 and Excel 2010 are using xlsx file, and 2010 may not have too much different than 2007. If you have tried and it still failed, then it may suggest that there may be some problem in the 64-bit environment.

     

    Thanks,
    Ming.
    WDAC Team, Microsoft.

     

     

     


    Pak-Ming Cheung - MSFT
    • Marked as answer by MyRBC Saturday, December 31, 2011 3:33 PM
    Saturday, October 08, 2011 11:26 AM
    Answerer

All replies

  • Hi Rune,

    As you pointed, you may have to install these Office 2010 System Driver data connectivity components to connect office data sources (such as Excel, Access) from non-Microsoft Office applications. You can use the following statement to access the Excel data source:

    SELECT * FROM OPENROWSET('Microsoft.ACE.OLEDB.12.0','EXCEL 12.0;Database=D:\test.xls;HDR=YES;IMEX=1','SELECT * FROM [sheet1$]');
    
    

    >>I installed AccessDatabaseEngine_x64 without success.
    What was the error message you received? Is the Operating System supported by the Microsoft Access Database Engine 2010 Redistributable with System requirements on the link above? Could you please try to uninstall it and install again?

    For more information about access to Office 2010:
    http://www.layer2.de/en/community/FAQs/BDLC/Pages/How-to-access-office-2010-data-from-sharepoint.aspx
    http://www.arboundy.com/2010/11/the-microsoft.ace.oledb.12.0-provider-is-not-registered-on-the-local-machine


    Best Regards,
    Stephanie Lv

    Forum Support
    Please remember to mark the replies as answers if they help and unmark them if they provide no help. If you have feedback for TechNet Subscriber Support, contact tnmff@microsoft.com.
    • Marked as answer by MyRBC Saturday, December 31, 2011 3:32 PM
    Monday, September 26, 2011 2:12 AM
  • I am using SQL Server 2008 R2 to import data from Excel sheet.

    I get the error when I have selected my excel sheet as my data source; and press next.

    I installed AccessDatabaseEngine_x64; but get still the same error when I try to import my excel sheet into my database. 


    Rune Brattas
    Monday, September 26, 2011 7:43 AM
  • Try using ACE.14.0 instead of 12.0 using following. do change your excel filename/sheetname in following query

     

    SELECT * FROM OPENROWSET('Microsoft.ACE.OLEDB.14.0','EXCEL 14.0;Database=D:\test.xls;HDR=YES;IMEX=1','SELECT * FROM [sheet1$]');
    



    Mark as Answer If Reply Was Helpful
    Thanks
    Kuldeep Bisht
    Technical Lead @ Simplion Technologies
    Blog : www.dbsimplified.com
    • Marked as answer by MyRBC Saturday, December 31, 2011 3:32 PM
    Monday, September 26, 2011 8:41 AM
  • I assume http://www.microsoft.com/download/en/details.aspx?displaylang=en&WT.mc_id=soc-n-%5bTR%5d-loc-%5bServices%5d-%5bfarukc%5d&id=13255 is ACE.14 (Office 2010).

    The problem is that I do not see Excel 2010 in SQL Server 2008 R2; when I try to import my Excel sheet. I see only Excel 2007; I assume I should see Excel 2010???

    I got around the problem by saving my excel sheet to Excel 97-2003; but I like to have this issue fixed.

    I like to import my Excel 2010 into SQL Server 2008 R2. I assume Office 2010 and SQL Server 2008 R2 are 64 bit by looking at the path (C:\Program Files).


    Rune Brattas
    Monday, September 26, 2011 3:23 PM
  • The reason why you can't see "Office 2010" in your SQL is most likely because of the unsucceeded installation of the ACE 64-bit driver / provider.

    What is the error when you install AccessDatabaseEngine_x64 without success. Are you getting the error about "64-bit ACE cannot be installed along with a 32-bit Office installation"? If so, please see the related thread at: http://support.microsoft.com/kb/2269468

     

    Thanks,
    Ming.
    WDAC Team, Microsoft.


    Pak-Ming Cheung - MSFT
    • Marked as answer by MyRBC Saturday, December 31, 2011 3:32 PM
    Friday, September 30, 2011 2:58 AM
    Answerer
  • So you are saying that ACE 14.0 is not native to an installation of Office 2010 professional? Basically my team will need to package AccessDatabaseEngine for 2007 with our current code or change code to handle ACE 14.0 + AccessDatabaseEngine for 2010.  Please Correct me if I am wrong.

     

    Thanks,

    Mike

    • Marked as answer by MyRBC Saturday, December 31, 2011 3:33 PM
    Thursday, October 06, 2011 4:05 PM
  • Please notice I got no installation error; but I will try to reinstall SQL to see if the Office 2010 get installed...which I doubt.
    Rune Brattas
    Thursday, October 06, 2011 8:39 PM
  • You are right as always, you have never been wrong in the past and I will never correct you in the future; so don't doubt yourself anymore... :)
    Rune Brattas
    Thursday, October 06, 2011 8:42 PM
  • But you have mentioned " I installed AccessDatabaseEngine_x64 without success" in your first post.

    So, i am now confused. Do you get it installed successfully or not? Please clarify.

    ==============================

    btw, you said that there is an option about "Excel 2007" but not "Excel 2010" in SQL Server. So, i wonder whether you can use the option "Excel 2007" to import the Excel 2010 file - both of them are xlsX.

    Thanks,
    Ming.
    WDAC Team, Microsoft.


    Pak-Ming Cheung - MSFT
    • Marked as answer by MyRBC Saturday, December 31, 2011 3:33 PM
    Saturday, October 08, 2011 2:21 AM
    Answerer
  • Ok, that could be confusing.

    I installed AccessDatabaseEngine_x64; but that did not solve my problem. I still cannot import data from my Office 2010 Excel sheet in SQL Server 2008 R2. But do you have a 64 bit machine, running 64 bit SQL Server 2008 R2 with 64 bit Office 2010? ...and you can import your Office 2010 Excel Sheet into your SQL Server 2008? Can you see Office 2010 Excel option when you import data?


    Rune Brattas
    Saturday, October 08, 2011 10:16 AM
  • Currently, I do not have a 64-bit SQL and 64-bit Office for testing handy.

    However, I tried the 32-bit environment and I also found that there is no "Excel 2010" but only "Excel 2007", when I use "Import Data" and select "Microsoft Excel" in the "Data Source" textfield in the dialog "SQL Server Import and Export Wizard". (I have installed Office 2010 in my machine, but not Office 2007).

     

    But I can successfully import the data into my SQL tables with the option "Excel 2007". So, I wonder whether you have tried the "Excel 2007" option or not. Actually, both Excel 2007 and Excel 2010 are using xlsx file, and 2010 may not have too much different than 2007. If you have tried and it still failed, then it may suggest that there may be some problem in the 64-bit environment.

     

    Thanks,
    Ming.
    WDAC Team, Microsoft.

     

     

     


    Pak-Ming Cheung - MSFT
    • Marked as answer by MyRBC Saturday, December 31, 2011 3:33 PM
    Saturday, October 08, 2011 11:26 AM
    Answerer
  • The work around is to save your office 2010 into a office 2007 excel file; it works.
    Rune Brattas
    Saturday, October 08, 2011 3:05 PM
  • This is strange. I don't need that step on 32-bit platform.

     

    btw, since you are obtaining the error message, are you writing some script / TSQL in your application?  Are you using the GUI (Import / Export Wizard) or some TSQL script to import your data from Excel.

     

    Could you share what you were doing:

    - Detail steps about your program

    - Enviornment (platform = x64 platform, windows = ?, SQL version = SQL2K8 R2 64-bit , Office version = 2010 64-bit)?

     

     

    Thanks,

    Ming.
    WDAC Team, Microsoft.

     


    Pak-Ming Cheung - MSFT
    Saturday, October 08, 2011 3:13 PM
    Answerer
  • I did not run as administrator...right click and run as admin on SSMS.
    Rune Brattas
    Saturday, December 31, 2011 3:34 PM