ACE Oledb 12.0 and XLSX problems

Question

I'm using the following code to set a connection string on my local PC that has Office 2007, SQL Server 2008:

string connString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + MapPath(Request.ApplicationPath) + "\\" + excelFolderName + fileName + ";Extended Properties=\"Excel 12.0 Xml;HDR=YES;IMEX=1\"";

My OS is Windows 7 (64-bit) and I'm able to read XLS and XLSX files without any problem.

I've released my project into a MS Server 2003 R2 Standard Edition X64 and after bumping into some problems, because I didn't have the ACE OLEDB 12.0 installed, I installed "Microsoft Access Database Engine 2010 Redistributable" and I am no able to read XLS files.

The problem comes when I try to open XLSX (that were created using Office 2007) because I get this error:

External table is not in the expected format

What am I doing wrong?

I don't want to install the previous "Microsoft Access Database Engine 2007 Redistributable" because it only has a 32 bits version that forces me to build my project as 32 bits...

Thanks in advance

Answer 1

Maybe you need different connection strings for .xlx and .xlxs files? Look at this, it could be helpful: http://www.arboundy.com/2010/11/the-microsoft.ace.oledb.12.0-provider-is-not-registered-on-the-local-machine/

Answer 2

I am having a similar issue (see my post IIS and OLE DB External table is not in the expected format). I am beginning to think that ACE has a serious issue since my connection strings are ACE 12.0.

Since you are using Excel 2007, you can still use Jet and that may solve your issue. If you use Excel 2010 and beyond, then you must use ACE.