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.
Does that column allow NULL values in the Access database?
Open the database in Access and set Required to No on that column, meaning it will accept NULL values.
Not sure if this is the actual case here but it's worth a try!
Thanks Albert - unfortunately I am writing to an Excel table and therefore do not have that level of control.
Ah, my bad. Anyways: did you try the IMEX=1 option in the connection string?