使用经典ASP和ADO将Excel 2007导入SQL 2000

I have the following code from a legacy app which currently reads from an Excel 2003 spreadsheet on a server, but I need this to run from my machine which uses Excel 2007. When I debug on my machine ADO does not seem to be reading the spreadsheet.

我从遗留应用程序中获得以下代码，该应用程序当前从服务器上的Excel 2003电子表格中读取，但我需要从我的使用Excel 2007的计算机上运行。当我在我的机器上调试时，ADO似乎没有阅读电子表格。

I have checked all file paths etc. and location of spreadsheet that is all fine. I've heard that you cannot use the jet db engine for Excel 2007 any more? Can someone confirm this? What do I need to do to get this to work?

我检查了所有文件路径等和电子表格的位置都很好。我听说你不能再使用jet db引擎用于Excel 2007了吗？有人可以证实吗？我需要做些什么才能让它发挥作用？

set obj_conn = Server.CreateObject("ADODB.Connection")
obj_conn.Open   "Provider=Microsoft.Jet.OLEDB.4.0;" & _
            "Data Source=" & Application("str_folder") & "CNS43.xls;" & _
            "Extended Properties=""Excel 8.0;"""
set obj_rs_cns43 = Server.CreateObject("ADODB.RecordSet")
obj_rs_cns43.ActiveConnection = obj_conn
obj_rs_cns43.CursorType = 3
obj_rs_cns43.LockType = 2
obj_rs_cns43.Source = "SELECT * FROM [CNS43$]"
obj_rs_cns43.Open

3 个解决方案

#1

Try changing your connection string like this (source article):

尝试像这样更改连接字符串（源文章）：

obj_conn.Open   "Provider=Microsoft.ACE.OLEDB.12.0;" & _ 
            "Data Source=" & Application("str_folder") & "CNS43.xls;" & _ 
            "Extended Properties=""Excel 12.0;"""

#2

You can always use Excel Automation to read from Excel. But I think that's the Plan B.

您始终可以使用Excel Automation从Excel中读取。但我认为这是B计划。

The best solution for this problem would be Openrowset. No ASP required, just pure SQL.

这个问题的最佳解决方案是Openrowset。不需要ASP，只需要纯SQL。

Here's a "Closer to Excel" article

这是一篇“更接近Excel”的文章

#3

I resolved this issue, the problem I was having was related to 64 bit OS compatibility with the microsoft Ace drivers. To get this to debug you have to force Visual Studio to debug with 32 bit i.e. debug config manager.

我解决了这个问题，我遇到的问题与64位操作系统与微软Ace驱动程序的兼容性有关。要使其调试，您必须强制Visual Studio使用32位调试，即调试配置管理器。

#1