Excel With ADO.NET:Cannot modify the design of table ‘xxx′. It is in a read-only database

If you are trying to execute some Jet SQL DDL like CREATE TABLE or DROP TABLE against an Excel workbook and you get this message:

Executing the query “DROP TABLE [Sheet1];” failed with the following error: “Cannot modify the design of table ‘Sheet1′. It is in a read-only database.”.

Check your connectionstring properties in your excel connection manager. If you have the extended property “IMEX=1″, then you will be opening your workbook in import mode and therefore you will be unable to modify the design. Remove the setting and you may find that your Execute SQL task now works.

"Provider=Microsoft.Jet.OLEDB.4.0;Data Source={0};Extended Properties=\"Excel 8.0;HDR=Yes;IMEX=1\";"

==>

"Provider=Microsoft.Jet.OLEDB.4.0;Data Source={0};Extended Properties=\"Excel 8.0;HDR=Yes;\";"