Upload An Excel File in Classic ASP On Windows 2003 x64 Using Office 2010 Drivers_问答_开发者

So, we are migrating an old web app from a 32-bit server to a newer 64-bit server. The app is basically a Classic ASP app. The pool is set to run in 64-bit and cannot be set to 32-bit due to other components. However, this breaks the old usage of Jet drivers and subsequent parsing of Excel files.

After some research, I downloaded the 64-bit version of the new 2010 Office System Driver Beta and installed it. Presumably, this allows one to open and read Excel and CSV files.

Here's the snippet of code that errors out. Think I followed the lean guidelines on the download page:

Set con = Server.CreateObject("ADODB.Connection")
con.ConnectionString = "Provider=Microsoft.ACE.OLEDB.14.0;Data Source=" & strPath & ";Extended Properties=""Excel 14.0;"""
con.Open

Any ideas why?

UPDATE: My apologies. I did forget the important part, the error message:

ADODB开发者_运维技巧.Connection error '800a0e7a'
Provider cannot be found. It may not
be properly installed.
/vendor/importZipList2.asp, line 56

I have installed, and uninstalled/reinstalled twice.

I encountered a similar issue a little while ago and I found out that there's actually a bug in the installer package for the ACE 14 driver. The driver is being registered as "Microsoft.ACE.OLEDB.12.0". In my case I could get things to work just by changing my connection string to 12 as opposed to 14.

Evidence on the bug: Microsoft Connect

It could be the Excel version in the Extended Properties parameter. Excel 14 is for Office 2010. While the driver may be Office 2010 I would presume your Excel documents are not.
Excel 2007 - v12
Excel 2003 - v8
From http://www.connectionstrings.com for Excel 2007:

Provider=Microsoft.ACE.OLEDB.12.0;Data Source=c:\myFolder\myExcel2007file.xlsx;Extended Properties="Excel 12.0 Xml;HDR=YES";

You could try replacing the ACE.OLEDB.12.0 with ACE.OLEDB.14.0 to match the driver version.

Here http://forums.asp.net/p/1128266/1781961.aspx is a suggestion to split your app into 32 and 64 bit components and transfer data via COM. To quote:

What you need to do is split your application into a 32 bit part and a 64 bit part, use COM interop to cross the 64/32 bit boundary. For instance, drop the code (just a simple class library compiled as 32 bit) that retrieves the Excel data into a COM+ (System.EnterpriseServices) as a "server type" application, and call that server methods from your 64 bit Windows service. This is exactly why System.EnterpriseServices are made for.

Looks like it is "14" ("14.0") for Excel. But "12" ("12.0") for ACE.

From this download:

http://www.microsoft.com/downloads/en/details.aspx?familyid=C06B8369-60DD-4B64-A44B-84B371EDE16D&displaylang=en

I found this snipplet:

**2.If you are an application developer using OLEDB, set the Provider argument of the ConnectionString property to “Microsoft.ACE.OLEDB.12.0”

◦If you are connecting to Microsoft Office Excel data, add “Excel 14.0” to the Extended Properties of the OLEDB connection string.**

HOWEVER!!!

The instructions from the download page seem to be wrong....causing some of this confusion.

From here: http://social.msdn.microsoft.com/Forums/en/adodotnetdataproviders/thread/686d8ebb-0da3-4f0c-bf16-9c650f8dcb32

//Quote //Paul P C The instructions are incorrect. It should be Excel 12.0. I wish they would fix this mistake at the download site. //End Quote

I have verified that this connection string works for me: "Provider=Microsoft.ACE.OLEDB.12.0;Data Source='C:\MyFile.xls';Extended Properties='Excel 12.0;HDR=NO;IMEX=1;';"

I faced the same problem, finally, I realized that the error is that the connection string should be modified in 64 bit!

To get the right one, install visual basic 6 and drag the Microsoft data access control,through its wizard, open the file you want, and let it write the connection string to you.