none
The 'Microsoft.ACE.OLEDB.12.0' provider is not registered in the local machine

    Question

  • I have asp.net/vb application on 2008 server with SQl 2008 backend. i get the the above error when I try to open an Excel file as followed:

     

    Dim oledbConn As OleDbConnection = New OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0;Data source=" & a_sFilepath & ";Extended Properties= Excel 12.0;HDR=YES;IMEX=1;")

    oledbConn.Open()

    I need to open the EXcel file to get the sheet names and then I use SQL to read data into tables.

    I have Office 2010(x64) installed on the server.

    When I use the same method to open Excel and transfer data in SQL there is no problem.  The following code in SQL works fine:

    set

     

    @sql = 'insert ' + @tableName +

    'SELECT * FROM OPENROWSET

    (''Microsoft.ACE.OLEDB.12.0'',''Excel 12.0 Xml;IMEX=1;Database='

     

    + @DATASOURCE1 + ' ;'',''Select * From [' + @SHEET +

    '$]'')'

    I read many posting on this error and tried some of the suggestions.  No luck yet.  can anyone help??
    • Moved by Cindy Meister MVPMVP Saturday, November 19, 2011 5:47 PM not using VSTO technology (From:Visual Studio Tools for Office)
    Friday, November 18, 2011 9:10 PM

Answers

  • Thank you very much for your reply.  I tried it and still got the same error. Now I am approaching it differently and reading the file as follow as vbscript on the asp page:

    objExcel = CreateObject("Excel.Application" )

     

    objWorkBook = objExcel.Workbooks.Open(Server.MapPath("temp//" & filename), , True)

    objExcel.Visible =

    True

    this one is working for now to open the file, but when i try to make it visible on the client side, it does not work and gives me unrelated errors of file not found.

    Is it possible to open an excel file, from the server, on the client side assuming client has Excel installed?

     

    thanks



    Tuesday, November 22, 2011 4:04 PM

All replies

  • Hi Smoosavi,

    You need the Access Database engine (ACE)

    See http://www.microsoft.com/download/en/details.aspx?id=13255


    Brian, ProcessIT- Hawke's Bay, New Zealand
    Sunday, November 20, 2011 11:26 PM
  • Thanks Brian for your information. However, since Smoosavi can utilize ACE engine in SQL command, and also he has Office (X64) installed, the issue should not caused by missing installing of ACE engine.

    Hi Smoosavi,

    Thanks for your post.

    Would you like to tell me whether you have specified your application edition to 32-bit or 64-bit? The most probably reason I can tell is that you complie the application as 32-bit one, however, the ACE engine 32-bit isn't installed in your machine. You might need to change the "Platform target" to "x64" manually so that the application will call the 64-bit engine, which you have installed.

    I hope this helps.


    Calvin Gao[MSFT]
    MSDN Community Support | Feedback to us
    Monday, November 21, 2011 9:31 AM
  • Thank you for your response.

    My target platform is "Any CPU".  I did change it to x64 and tried it again and it did not work.  I have been looking online and noticed that mainly this solution resolved the issu for others but did not work for me.  So I am puzzled about it.. any other thing that comes to mind??

    thank you,

    Monday, November 21, 2011 3:41 PM
  • I notice that there is a small mistake you need to modify in the connection string:

    Change:

    "Provider=Microsoft.ACE.OLEDB.12.0;Data source=" & a_sFilepath & ";Extended Properties= Excel 12.0;HDR=YES;IMEX=1;"
    


    TO:

    "Provider=Microsoft.ACE.OLEDB.12.0;Data source=" & a_sFilepath & ";Extended Properties= 'Excel 12.0;HDR=YES;IMEX=1';"
    


    The pairs of apostrophe is necessary for extended property.

    Please let me the result of the fix.

    Good day,


    Calvin Gao[MSFT]
    MSDN Community Support | Feedback to us
    Tuesday, November 22, 2011 9:04 AM
  • Thank you very much for your reply.  I tried it and still got the same error. Now I am approaching it differently and reading the file as follow as vbscript on the asp page:

    objExcel = CreateObject("Excel.Application" )

     

    objWorkBook = objExcel.Workbooks.Open(Server.MapPath("temp//" & filename), , True)

    objExcel.Visible =

    True

    this one is working for now to open the file, but when i try to make it visible on the client side, it does not work and gives me unrelated errors of file not found.

    Is it possible to open an excel file, from the server, on the client side assuming client has Excel installed?

     

    thanks



    Tuesday, November 22, 2011 4:04 PM
  • I've found another thread that seems to have it fixed. Seem some sort of issue with the 64-bit version and using either 32-bit or the 2007 engine works:

    http://social.msdn.microsoft.com/Forums/en-AU/vstsdb/thread/1d5c04c7-157f-4955-a14b-41d912d50a64

    Thursday, December 29, 2011 4:28 PM
  • try installing this

    http://www.microsoft.com/download/en/details.aspx?id=13255 .

    restart your IIS and it will work like charm.... atleast worked for me

    Friday, April 06, 2012 2:04 PM
  • The pairs of apostrophe is necessary for extended property and that finally fixed my issue! Thanks to Calvin_Gao for pointing that ou

    • Proposed as answer by Dinesh_T Monday, July 16, 2012 5:27 AM
    • Unproposed as answer by Dinesh_T Monday, July 16, 2012 5:27 AM
    Tuesday, June 26, 2012 4:41 PM
  • I faced same issue. It is fixed when you change 'Enable 32- bit applications' as True in the application pool of the IIS. Now it works fine
    Monday, July 16, 2012 5:29 AM
  • You need to save the Excel file as an .xls format (Excel 97) before attempting to import it.

    Kevin Humfreville

    Thursday, August 23, 2012 3:13 PM