Finally found a way to solve this, thanks to all for your suggestions. Billy Craven's suggestion led me to my final solution which was to first enter the values to the spreadsheet using COM, then use a DTS package to export the results to SQL 7 and a consequent simple select query to return the result to my output. I had tried this previously in a different order i.e. enter the values using a simple update query, then use COM to attempt to close the worksheet, which didnt work.
On a side note. It is worth rememberng that the problem occurs when two consecutive queries are run on the same excel datasource and that the CFUSION_DISABLE_DBCONNECTIONS command wont fix this, Nor will Jamie Jacksons suggestion of running a deliberately bad query to force the connection closed, despite the fact that they do both in fact close the connection in the ODBC driver which CF is using, but the MS Jet database engine still keeps them open, hence the resulting cached resultset. Dave -----Original Message----- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]] Sent: 18 April 2002 21:47 To: CF-Talk Subject: RE: How to close Excel ODBC connection at end of query Perhaps you could build a COM object to access the data? (Since ADO gives you more control over when the connection is opened/closed) Or if you're good with COM in C++, you could build a CFX (gives you the advantage of being able to build a "cf" recordset) --- Billy Cravens -----Original Message----- From: Dave Wilson [mailto:[EMAIL PROTECTED]] Sent: Thursday, April 18, 2002 2:45 PM To: CF-Talk Subject: How to close Excel ODBC connection at end of query Hi, Further to my problem of aparently retrieving cached query results when using an excel datasource (see earlier email titled Strange behaviour with excel datasource), I've discovered that CF seems to be keeping the ODBC connection alive across requests. Does anybody know how to force a shutdown of excel? I've tried a few different methods to date. Namely: <CFSET CFUSION_DISABLE_DBCONNECTIONS("exceldsn", 1)> <CFSET CFUSION_DISABLE_DBCONNECTIONS("exceldsn", 0)> to disable and enable write access to the datasource OR using CFOBJECT <!--- Try to connect to the Excel application object ---> <CFTRY> <!--- If it exists, connect to it ---> <CFOBJECT ACTION="CONNECT" CLASS="Excel.Application" NAME="objExcel" TYPE="COM"> <CFCATCH> <!--- The object doesn't exist, so create it ---> <CFOBJECT ACTION="CREATE" CLASS="Excel.Application" NAME="objExcel" TYPE="COM"> </CFCATCH> </CFTRY> <CFSCRIPT> // Quit Excel objExcel.Quit(); // Release the object objExcel = "Nothing"; </CFSCRIPT> to try to shut down the connection. This is really frustrating because all I am trying to do is a simple update query (to insert variables to a formula), followed by a select statement to retrieve the result of a formula contained in the excel file. This is analogous to doing an insert, followed by a select to retrieve a new ID numnber using any other RDBMS ODBC drivers. I've even created and triggered (using cfexecute)a DTS package to import the data into SQL 7 after having carried out the update query, but this gives me an error message indicating that the excel file is already in use, despite my various attempts at forcing closure of the file. Any help would be much appreciated, Dave ______________________________________________________________________ This list and all House of Fusion resources hosted by CFHosting.com. The place for dependable ColdFusion Hosting. FAQ: http://www.thenetprofits.co.uk/coldfusion/faq Archives: http://www.mail-archive.com/[email protected]/ Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
