0

I have an excel file that is being read with using ODBC driver with the following code:

 filePath = "folder\myExcelFile.xlsx"

 odbcPath = "DRIVER={Microsoft Excel Driver (*.xls)}; IMEX=1; HDR=YES; "&_
"Excel 8.0; DBQ=" & Server.MapPath(filePath) & ";"    

'xlsx driver 2007+
if InStr( lcase(filePath),".xlsx" ) > 0  then
    odbcPath= "Driver={Microsoft Excel Driver (*.xls, *.xlsx, *.xlsm, *.xlsb)}; "&_
    "DBQ=" & Server.MapPath(filePath) & ";"        
end if  

'Excel processing
Dim objConn
Dim objRs  

set objConn = Server.CreateObject("ADODB.Connection")
set objRs = Server.CreateObject("ADODB.Recordset")

objConn.Open odbcPath 

strSQL = "SELECT * FROM A1:Q50000"

objRs.LockType = 1
set objRs = objConn.Execute(strSQL)    

Do Until objRs.EOF
    ''Do stuff
     myfield = objRs.Fields("fieldName")
     ''...other code...
Loop

objRs.Close
objRs.ActiveConnection = Nothing
set objRs = Nothing

objConn.Close     
set objConn = Nothing

Inmediately after this, when I attempt to delete the file with the following:

Dim objFSO_Del
Set objFSO_Del = CreateObject("Scripting.FileSystemObject")

objFSO_Del.DeleteFile Server.MapPath(filePath), True

I get a permission denied error, which shouldn't happen because my IUSR account has all the privileges (total control) to the folder where this action takes place. In fact when I comment the code posted above and attempt to delete the file, the operation is successful

I was thinking on fetching the excel records using:

set objExcel = CreateObject("Excel.Application")
set objWorkbook = objExcel.Workbooks.Open(Server.MapPath(filePath))

As this would allow me to have precise control to close the file and quit the Excel process.

Is there a way I can release and delete the excel file efficiently using the ODBC technique?. I've done a fair amount of research and haven't found a solution for now.

Thank you very much for the insight.

****Update****

I tested my original code again and it actually works on xls but not on xlsx files.

When I use ACE.OLEDB engine as suggested by oracle certified professional, I get no permission denied errors:

odbcPath = "Provider=Microsoft.ACE.OLEDB.12.0;" &_
"Data Source=""" & Server.MapPath(filePath) & """;" &_
"Extended Properties=Excel 8.0;"

However I think should be a way to do this using the original engine to make this work on xlsx files.

Community
  • 1
  • 1
CoderRoller
  • 1,239
  • 3
  • 22
  • 39
  • 1
    would it be worth trying the [ACE](http://www.connectionstrings.com/ace-oledb-12-0/) engine in case that doesn't have the same issue? –  Sep 06 '13 at 17:06
  • I don't think it's possible to delete Excel files via ADO. – Ansgar Wiechers Sep 06 '13 at 18:01
  • The attempt to delete the file uses FSO.DeleteFile. – Ekkehard.Horner Sep 06 '13 at 18:12
  • Could someone else have the same file open while you're trying to delete it? It seems a distinct possibility with the code you posted, and would account for that error. – AnonJr Sep 06 '13 at 20:51
  • Hi @AnonJr, the file should be upload (with a unique name) read and discarded right afterwards. No one else are gonna be using it. – CoderRoller Sep 09 '13 at 13:54
  • Thanks @oracle certified professional, your suggestion worked and I will give you some points up if you put it as an answer. However is not exactly the answer I am looking for since I think should be a way to get around the problem using ODBC. – CoderRoller Sep 09 '13 at 14:02
  • does this happen with both *.xls _and_ *.xlsx workbooks? –  Sep 09 '13 at 15:09
  • ACE is the next gen data access driver for office products, why not use them if they work? – AnonJr Sep 09 '13 at 15:46
  • AnonJr, I am not sure if it is registered on this particular production server, if I can't find an answer I would need to ask for it. Also @oracle certified professional if you would like post your suggestion as an answer so I can give you the credit for it. Thanks guys. – CoderRoller Sep 10 '13 at 13:57

1 Answers1

0

Just Voodoo, but remove the 

objRs.ActiveConnection = Nothing

line - including the missing Set! If that doesn't help, remove all "Set whatever = Nothing" lines.

Ekkehard.Horner
  • 38,498
  • 2
  • 45
  • 96
  • All the `Set objRS.ActiveConnection = Nothing` bits explicitly close the objects and free the memory. These items would generally be added if they weren't there as a possible solution to this particular problem. – AnonJr Sep 06 '13 at 20:50
  • 4
    @AnonJr - A Set x = Nothing does not close or free anything; all it does is decrement a counter. Mostly they are a chance to mess things up (like forgetting the Set). In 99% of all cases VBScript knows best when to deal with out-of-scope objects and in which order. – Ekkehard.Horner Sep 06 '13 at 22:09
  • Hi @Ekkehard.Horner, I removed all the = Nothing references but the problem persists. – CoderRoller Sep 09 '13 at 13:55
  • Please enlighten me with a reference. I am aware that the scripting engine will do it's own garbage collection, and that in *most cases* it manages it just fine without us - however, I also know that there are cases where proactively deallocating resources will resolve problems (and those conditions are more lengthy to describe than I can cover in the remaining ~300 characters). Regardless, none of what we are discussing would produce the permission error described in the original question. – AnonJr Sep 09 '13 at 14:36
  • Hi @AnonJr Here you have a reference from Eric Lippert on the subject, which is/was from the VBScript compiler developing team: http://stackoverflow.com/questions/8144434/if-i-set-a-variable-using-createobject-do-i-need-to-clean-it-up-by-setting, so there should be some truth on it. – CoderRoller Sep 09 '13 at 15:32
  • 1
    @CoderRoller: All of which I am familiar with. Note though that the context of the question was in straight up VBScript - where the engine's resource usage is limited to the person running the script. On an ASP-Classic page, everyone hitting that page at that time is using those resources - increasing the expense of a resource, leading to point 2 of the cases where you would want to clean up yourself. – AnonJr Sep 09 '13 at 15:40
  • I agree @AnonJr, is possible that with this page being hit often the clean up should happen since the excel file would be a "long-lived resource", Is an interesting observation so I'll keep the Nothing references for that matter. Thank you!. – CoderRoller Sep 09 '13 at 15:57