Import Excel 2007 into SQL 2000 using Classic ASP and ADO

Question

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. 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 anymore? Can someone confirm this? What do I need to do to get this to work? Please help!

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

Answer 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;""" 

Answer 2

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

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

Here's a "Closer to Excel" article

Answer 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.