Object doesn't support this property or method: 'OpenSchema' via A

[jswidorski]

I have 2 sets of code that are basically the same code one is ASP the other
is VBS. Both Access that same DSN, but the ASP code gives me an error

Microsoft VBScript runtime error '800a01b6'
Object doesn't support this property or method: 'OpenSchema'

I don't understand why the ASP does not work.

Thanks.

'======================
' ASP that DOES NOT Work
'======================
set conExcel = Server.CreateObject("ADODB.Connection")
strConnExcel = "DSN=Excel_AC2;"
conExcel.Open strConnExcel

set xlSheets = conExcel.OpenSchema(20)
while NOT xlSheets.EOF
response.Write xlSheets.Fields("TABLE_NAME").Value & "<BR>"
xlSheets.MOVENEXT
wend
xlSheets.Close


'==============
' VBS that Works
'==============
set conExcel = CreateObject("ADODB.Connection")
strConnExcel = "DSN=Excel_AC2;"
conExcel.Open strConnExcel

set xlSheets = conExcel.OpenSchema(20)
while NOT xlSheets.EOF
wscript.echo xlSheets.Fields("TABLE_NAME").Value
xlSheets.MoveNext
wend
xlSheets.Close

 

[Bob Barrows]

I have 2 sets of code that are basically the same code one is ASP the
other is VBS. Both Access that same DSN, but the ASP code gives me
an error

Microsoft VBScript runtime error '800a01b6'
Object doesn't support this property or method: 'OpenSchema'

I don't understand why the ASP does not work.

Thanks.

'======================
' ASP that DOES NOT Work
'======================
set conExcel = Server.CreateObject("ADODB.Connection")

The first thing i would do is remove the "Server." from this statement.
It's unlikely to have anything to do with your problem, but removing it
can't hurt and might help


Where are you testing these scripts? Are you testing them on the same
machine? Or are you testing the vbs on your machine and the asp code on
a different machine?

 

[jswidorski]

I copied my script to the web server and it failed with same error.

My local box ran the script and asp fine.

 

[Bob Barrows]

So it's an MDAC-version issue. The server needs an MDAC upgrade - go to
MSDN and get the latest version of MDAC and install it. You might also
need the Jet components but I'm not sure about that.

 

[jswidorski]

I thought that I had. I will have to dig into this further.

 

[jswidorski]

I have done this I am still getting this error. I run the Component Checker
on 2 different servers and they are both "MDAC 2.8 SP 2 ON WINDWS 2003 SP2"
and one works and the other pops my OpenSchema error.

I switched my connection string to:
strCE = "Driver={Microsoft Excel Driver (*.xls, *.xlsx, *.xlsm,
*.xlsb)};DBQ=\\server\share\databases\AC.xls"

Also I used MBSA to verify all my updates...

Any other thoughts?

I thought that I had. I will have to dig into this further.

 

[Bob Barrows]

Did you verify that the Jet components are installed on both boxes?
Are there any differences between the two servers? Different OS or
versions?

 

[jswidorski]

Same versions. Jet should be the same

12.00.6423.1000 - 3/6/2009 on production server that does not work.

12.00.4518.1014 - 10/26/2006 on test server that works.

 

[Bob Barrows]

Are those version numbers referring to Access, or the Jet components?
They are two different things. See
http://support.microsoft.com/kb/239114

I'm thinking this is a red herring anyways - OpenSchema is part of ADO,
not Jet. But it cannot hurt to make sure the components are properly
installed.
Frankly, I'm grasping at straws. I've never used OpenSchema since there
are other ways to get the information it provides (ADOX for one).

Same versions. Jet should be the same

12.00.6423.1000 - 3/6/2009 on production server that does not work.

12.00.4518.1014 - 10/26/2006 on test server that works.

 

[jswidorski]

Here is my code snippet

set conExcel = Server.CreateObject("ADODB.Connection")
strConnExcel = "Driver={Microsoft Excel Driver (*.xls, *.xlsx, *.xlsm,
*.xlsb)};DBQ=\\servername\sharename\databases\A_C.xls"

conExcel.Open strConnExcel

set xlSheets = conExcel.OpenSchema(20)
if NOT xlSheets.EOF then
response.Write xlSheets.Fields("TABLE_NAME").Value & "<BR>"
strSheetName = xlSheets.Fields("TABLE_NAME").Value
xlSheets.MOVENEXT
end if
xlSheets.Close

set rsE = Server.CreateObject("ADODB.Recordset")
rsE.Open ("SELECT * FROM [" & strSheetName & "$]"), strConnExcel

Same versions. Jet should be the same

12.00.6423.1000 - 3/6/2009 on production server that does not work.

12.00.4518.1014 - 10/26/2006 on test server that works.

 

[jswidorski]

Inside of the ODBC Data Sources.

As for ADOX, when I tried a month or so ago with Extented Properties for
something similiar to below, I would get Updateable ISAM errors. Maybe there
is something wrong with my server configuration. I don't know.
---------
Dim oConn As New ADODB.Connection
oConn.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=C:\Book1.xls;" & _
"Extended Properties=""Excel 8.0;"""
oConn.Close

---------

To step back, what should be driving this? Jet? MDAC? OLEDB?

I see this working on one server but not another (also, my local box works.)