Since I was using ODBC to read/write Excel documents recently I was surprised not to have too much problems with it. Today a problem occured - funny thing because I was nearly through with changing the application to finally use SqlCE as persistency layer...
The problem was that we needed some large texts placed in some columns but excel just truncated it down to 255 chars (256 if you count the ' thats visible in excel). I didn't had much time to write some workaround at this point so I tested a little bit hoping to find a shorter workaround.
Of Course todays excel can handle more than 256 chars in a cell but when you save the document you'll get a warning for some incompatibility issues. Nice - file saved in "new" format - opened my program again - inserted a large text and ye......no - again truncated to 256 chars. So the ODBC driver seemed to switch back to old format. Since the driver seemed capable of handling xls files of office 12.0 (2007) according to the descriptions in the ODBC-Administration dialog I was looking for a simple way to make him use the new format. Finally I found something on the web saying that the driver determines the format by scanning some rows and while reading this I rememberd something I red earlier; there is an option "Rows to scan" in the ODBC-config and/or in the connection-string which was always set to 8. Well putting a large text in one cell in the first 8 rows and SUCCESS now even my application can write texts with more than 256 chars...
At this point I stopped testing so maybe it is possible to increase the RowsToScan variable to a max value or something - for me my quick and dirty workaround was done here

I was using ODBC to Excel for a while and it turned out to work well (if you take care of integrity by yourself...) but for some reasons until now I hadn't used any DELETE FROM-Statement. So I was a little bit disappointed when my first DELETE returned this:
[HY000] [Microsoft][ODBC Excel Driver] Deleting data in a linked table is not supported by this ISAM.
(well the message was "ISAM unterstützt das Löschen von Daten in einer verknüpften Tabelle nicht." since my system is german)
Since the Message itself was somewhat confusing I found out that it is simply not possible to drop Excels lines via ODBC - you can clear lines but you cannot delete them. There are some workarounds in the net which may use other components to do the delete job but the ODBC wont let you.
I prefered simply to add a "Deleted"-Column to my Excel table and rejected these lines already in the DataAccess layer so the real data always remains. Maybe I'll write a Defrag Job later if the deleted data starts to cause performance problems.