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

Answer 1

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

Answer 2

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

Answer 3

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,

Answer 4

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

Answer 5

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

Answer 6

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

Answer 7

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

Answer 8

The pairs of apostrophe is necessary for extended property and that finally fixed my issue! Thanks to Calvin_Gao for pointing that ou

Answer 9

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

Answer 10

You need to save the Excel file as an .xls format (Excel 97) before attempting to import it.

---

Kevin Humfreville