Using newest ACE driver to write XLSX file, exception on CREATE TA

[Scott S.]

Exception occurs when a sheet already exists, as it should.
But the problem occurs in my app even though a DROP TABLE before hand.

But it works fine for an XLS file using the old driver, and for an XLSB file
using the new.

In trying to recreate the problem in a simple example I discovered that it
will work fine if the DROP TABLE and CREATE TABLE are done during the same
connection, but not if the connection is closed and reopened in between.

I'll post a reply with the source code of my example application.

In the code, as posted, demonstrates the problem with XLSX files and how it
does work for XLS and XLSB.
But if you comment out the line "con.Close(); con.Open();" it will run
through for all 3.

Is this a bug in the driver, the file format, or something else?

[Scott S.]

using System;
using System.Data;
using System.Data.OleDb;
using System.Windows.Forms; // for MessageBox

namespace TestExcel
{
public static class MainEntry
{
[STAThread]
static void Main()
{
try
{
string[] connectStrings = new string[] {
"Provider=Microsoft.Jet.OLEDB.4.0;Data
Source=\"C:\\TEMP\\TestExcel2003Output.xls\";Extended Properties=\"Excel
8.0;HDR=Yes;\";",
"Provider=Microsoft.ACE.OLEDB.12.0;Data
Source=\"C:\\TEMP\\TestExcel2007Output.xlsb\";Extended Properties=\"Excel
12.0;HDR=Yes;\";",
"Provider=Microsoft.ACE.OLEDB.12.0;Data
Source=\"C:\\TEMP\\TestExcel2007Output.xlsx\";Extended Properties=\"Excel
12.0 Xml;HDR=Yes;\";"
};
string dropTableStmt = "DROP TABLE [test]";
string createTableStmt = "CREATE TABLE [test] ( [Integer] int, [String]
varchar(40), [Double] float, [Date] datetime, [Boolean] bit )";

foreach( string connect in connectStrings )
{
OleDbConnection con = new OleDbConnection( connect );
con.Open();
if( con.State == ConnectionState.Open )
{
OleDbCommand cmd = con.CreateCommand();
cmd.CommandTimeout = 0;

try
{
// Only need this on runs subsequent to first time
cmd.CommandText = dropTableStmt;
cmd.ExecuteNonQuery();
}
catch
{
// First run will cause exception because table (worksheet) doesn't
exist
}

// COMMENT THIS OUT to run without error for XLSX
con.Close(); con.Open();

cmd.CommandText = createTableStmt;
cmd.ExecuteNonQuery();

cmd.Dispose();
if( con.State == ConnectionState.Open )
con.Close();
con.Dispose();
}
}
}
catch( Exception ex )
{
MessageBox.Show( ex.Message );
}
}
}
}

[Paul Clement]

On Wed, 4 Jun 2008 12:51:02 -0700, Scott S. <Sco...@community.nospam> wrote:

¤ Exception occurs when a sheet already exists, as it should.

DROP TABLE will only delete the contents of an Excel Worksheet. It will not delete the Worksheet. If
you want to delete the Worksheet you will have to use Excel automation.

Paul
~~~~
Microsoft MVP (Visual Basic)

[Scott S.]

I realize that. But it always had the effect of eliminating the "table"
because it deleted the headers too, and then subsequent calls to CREATE TABLE
would work without error, even if the file was closed in between.

And that "effect" still works in the newest OLEDB provider when the file is
closed for the XLSB file format, but it doesn't for XLSX. This inconsistent
behavior is my real gripe/problem.

My app uses a customer selected provider, so I have to test with over 200
ODBC, OLEDB, and ADO.NET providers. All this code was fully tested and
working for everything before the new MS provider came out and I added
support for Access 2007 and Excel 2007. Excel 2007 with an XLSX file format
is the only thing I've every hit that hasn't worked since this code was
stable.

I have managed to work around it by make some serious changes to this core
area my application's code just to maintain a connection through the
determination of table existance, drop and recreate with the new column list.

So even though I have it working now, I have a LOT of testing ahead of me to
verify it didn't break something else ... all because, in my opinion,
Microsoft has a design defect in their provider.

Scott