Excel file won't delete after SQL use ... thinks it's still being

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have a function that imports an Excel file into an Access table using SQL.
I then close the OleDataReader and the OleDbConnection, then dispose the
OleDbCommand, then OleDbConnection. The calling function then attempts a
File.Delete (also tried the FileInfo delete) on the Excel file, but gets the
message "The process cannot access the file ... because it is being used by
another process".

I've stepped through the code and it steps through correctly. I've put the
delete before the import function call and it works correctly. But it doesn't
work if it follows the import call.

Here's the important part of the import function code:

OleDbConnection cnSQL;
OleDbCommand cmSQL;

....

cnSQL = new OleDbConnection(ConnectionString);
cnSQL.Open();

strSQL = "INSERT INTO " + FileType + " SELECT * FROM [Excel 8.0;Database=" +
ExcelFile + "].[" + FileType + "Log]";
cmSQL = new OleDbCommand(strSQL, cnSQL);
cmSQL.ExecuteNonQuery();

cnSQL.Close();
cmSQL.Dispose();
cnSQL.Dispose();
 
Smitty said:
I have a function that imports an Excel file into an Access table using
SQL. I then close the OleDataReader and the OleDbConnection, then dispose
the OleDbCommand, then OleDbConnection. The calling function then attempts
a File.Delete (also tried the FileInfo delete) on the Excel file, but gets
the message "The process cannot access the file ... because it is being
used by another process".
Click to expand...

Just a dumb thought -- but do you have the Excel spreadsheet open in Excel
on your desktop somewhere?
I've stepped through the code and it steps through correctly. I've put the
delete before the import function call and it works correctly. But it
doesn't work if it follows the import call.

Here's the important part of the import function code:

OleDbConnection cnSQL;
OleDbCommand cmSQL;

...

cnSQL = new OleDbConnection(ConnectionString);
cnSQL.Open();

strSQL = "INSERT INTO " + FileType + " SELECT * FROM [Excel 8.0;Database="
+ ExcelFile + "].[" + FileType + "Log]";
cmSQL = new OleDbCommand(strSQL, cnSQL);
cmSQL.ExecuteNonQuery();

cnSQL.Close();
cmSQL.Dispose();
cnSQL.Dispose();
Click to expand...
 
No. But good question :). No other applications are running.

BTW, same problem happens on development PC and deployment PC.
 
Smitty said:
strSQL = "INSERT INTO " + FileType + " SELECT * FROM [Excel 8.0;Database="
Click to expand...

I'm wondering if you don't have two open connections here.

The open connection of the INSERT and the open connection of the SOURCE.

Can you do it in two steps?

Get a DataReader from Excel, then add the DataReader contents to the
FileType access table.

Alternatively, you could use the Access object model and run the Import
method.
+ ExcelFile + "].[" + FileType + "Log]";
cmSQL = new OleDbCommand(strSQL, cnSQL);
cmSQL.ExecuteNonQuery();

cnSQL.Close();
cmSQL.Dispose();
cnSQL.Dispose();
Click to expand...
 
Oops! In the original message, instead of close OleDataReader that should say
close OleDbCommand.
 
I've narrowed the problem down to not the INSERT statement, but a prior
SELECT statement.

Here is the code:

OleDbConnection cnSQL;
OleDbCommand cmSQL;
OleDbDataReader drSQL;
string strSQL;

// check that Excel sheet exists and contains records
strSQL = "SELECT Count(*) FROM [Excel 8.0;Database=" +
ExcelFile + "].[" + FileType + "Log]";

cnSQL = new OleDbConnection(ConnectionString);
cnSQL.Open();
cmSQL = new OleDbCommand(strSQL, cnSQL);
drSQL = cmSQL.ExecuteReader();

....

drSQL.Close();


If I comment out the ExecuteReader (and Close) statement the File.Delete
works on the Excel file that is used in the SELECT statement.
 
You close the reader, but do you close the connection?

( The connection is not consumed until you run the command. )
I've narrowed the problem down to not the INSERT statement, but a prior
SELECT statement.

Here is the code:

OleDbConnection cnSQL;
OleDbCommand cmSQL;
OleDbDataReader drSQL;
string strSQL;

// check that Excel sheet exists and contains records
strSQL = "SELECT Count(*) FROM [Excel 8.0;Database=" +
ExcelFile + "].[" + FileType + "Log]";

cnSQL = new OleDbConnection(ConnectionString);
cnSQL.Open();
cmSQL = new OleDbCommand(strSQL, cnSQL);
drSQL = cmSQL.ExecuteReader();

...

drSQL.Close();


If I comment out the ExecuteReader (and Close) statement the File.Delete
works on the Excel file that is used in the SELECT statement.
Click to expand...

--
"The Bush administration aims in its 2005 budget to cut by $1 billion the
$18 billion fund that helps about 2 million Americans--generally the poor,
elderly, and disabled--pay their rent."
-Mother Jones
http://www.motherjones.com/news/dailymojo/2004/05/05_520.html
 
OK, I've got it figured out. I was using the OleDbDataReader variable (drSQL)
for two different ExecuteDataReader statements within the function. The
second ExecuteDataReader had nothing to do with the Excel file (except to
check whether the Access table that the Excel sheet would be imported into
had any records that would be overlaid). I was closing drSQL at the end of
the function, but not between the two ExecuteDataReaders. Once I did a
drSQL.Close() after the first ExecuteDataReader statement everything worked.
Still seems kind of strange since drSQL is out of scope when I have the
problem attempting to delete the Excel file. But obviously something's not
being released.

Thanks for your help everyone.




Section 8 said:
You close the reader, but do you close the connection?

( The connection is not consumed until you run the command. )
I've narrowed the problem down to not the INSERT statement, but a prior
SELECT statement.

Here is the code:

OleDbConnection cnSQL;
OleDbCommand cmSQL;
OleDbDataReader drSQL;
string strSQL;

// check that Excel sheet exists and contains records
strSQL = "SELECT Count(*) FROM [Excel 8.0;Database=" +
ExcelFile + "].[" + FileType + "Log]";

cnSQL = new OleDbConnection(ConnectionString);
cnSQL.Open();
cmSQL = new OleDbCommand(strSQL, cnSQL);
drSQL = cmSQL.ExecuteReader();

...

drSQL.Close();


If I comment out the ExecuteReader (and Close) statement the File.Delete
works on the Excel file that is used in the SELECT statement.
Click to expand...

--
"The Bush administration aims in its 2005 budget to cut by $1 billion the
$18 billion fund that helps about 2 million Americans--generally the poor,
elderly, and disabled--pay their rent."
-Mother Jones
http://www.motherjones.com/news/dailymojo/2004/05/05_520.html
Click to expand...
 
Back
Top