What about the "Maintain database connections" setting for the DSN under ColdFusion Administrator? This should take off the lock place on the XLS file.
I am not sure if this will work for your problem, but you might want to give it a shot. +--------------------------+---------------------------+ | SCOTT VAN VLIET | SBC SERVICES, INC. | | Senior Analyst | ITO Enterprise Tools | | Tel: 858.886.3878 | 7337 Trade St. Room 4000 | | Pgr: 858.536.0070 | San Diego, CA 92121 | | Email: [EMAIL PROTECTED] | http://www.sbc.com | +--------------------------+---------------------------+ -----Original Message----- From: Dave Wilson [mailto:[EMAIL PROTECTED]] Sent: Thursday, April 18, 2002 12: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
