How can I get Excel to release its lock on another Excel file I've accessed using ADO?

I'm using a VBA macro in an Excel 2003 document to query another Excel 2003 document via ADO. The code looks something like this:

  Dim vRecordSet As ADODB.Recordset

  vWorkbookConnectionString = _
    "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\SomeExcelFile.xls;Extended Properties=Excel 8.0;"

  Set vRecordSet = New ADODB.Recordset

  Call vRecordSet.Open(Source:=strSQl, ActiveConnection:=vWorkbookConnectionString, _
    CursorType:=adOpenForwardOnly, LockType:=adLockReadOnly, Options:=adCmdTex开发者_Python百科t)

vRecordSet is later used to create a PivotCache.

My goal is to delete the file that is the source of this RecordSet (C:\SomeExcelFile.xls in the example above) when the user closes the workbook from which this code is run.

The file gets locked when this ADO query is run, and it doesn't seem to get released until after the Workbook is closed. Calling vRecordSet.Close does not release it.

Is it possible to force Excel to release its lock on this file so that I can delete it programmatically?

Did you try adding the following after your vRecordSet.Close call to see if it makes a difference:

Set vRecordSet = Nothing

Untested Voodoo: use a variable to hold the ADODB.Connection, pass this to open, close both the recordset and the connection.