Blank vba sql update to Excel 2013 field stopping subsequent inserts

Using vba and Microsoft.ACE.OLEDB.12.0 connection to Excel 2013 workbook. Inserting records into worksheet work fine. Problem occurs if I update a field in a record with a blank (""). The record updates OK, but from then on, no subsequent inserts (or updates) will work until I manually open the workbook and re-save the sheet (other sheets in the workbook are not affected and can be inserted to as normal). If a text string is included in the (field) update (rather than an empty string) everything works as expected. How can I 'update' a text field with a blank? I have tried all sorts of options: passing variant rather than string, SQLNull, nullstring, vbnullstring '' & '''' and Empty- all with no success. Could it be something to do with pooling? To get over the problem I update it with "bl"- and then have to ignore it and blank it elsewhere in the code- not very elegant. I would be obliged if any one can help.

3 answers