Error when reading data from Excel

[fdarkness]

I have an Excel sheet stored on my server and an ASP page that queries the data on the sheet. It works great, except there seems to be a "time limit"... ie, when I query the sheet and display the data the first time, it displays fine. If I back up a menu and display the page with the query again (doesn't matter if it's the same set of data or different data), it throws an error. If I wait a few minutes and reload, it displays the data.

I assumed that the Excel page wasn't closing after the first query, but I am definitely closing the recordset information.

Here's my code:

Set objConn = Server.CreateObject("ADODB.Connection")
objConn.Open "ADOExcel"

Set objRS = Server.CreateObject("ADODB.RecordSet")
objRS.ActiveConnection = objConn
objRS.CursorType = 3
objRS.LockType = 2
objRS.Source = "SELECT * FROM Range1 WHERE SurveyID='" & SurveyID & "'"
objRS.Open

*** display data ***

objRS.Close
Set objRS = Nothing
objConn.Close
set objConn = Nothing %>
[/close]

and sadly, this is the error code (I've tried turning off "Show friendly HTTP messages", but she just ain't going):
[b]Provider error '80004005' 
Unspecified error 
/reports/functions_clientsurvey.asp, line 57 [/b]

Line 57 corresponds to [b]objConn.Open "ADOExcel"[/b]

Any guidance would be appreciated!

 

[Tarwn]

When you created your DSN did you try the "Test Connection" button to make sure it worked? I haven't used a DSN with excel files before, I usually use an ODBC string like the examples here

-T