vb.net - Inconsistent behavior when using OleDbDataAdapter with MS Excel -

i using ms excel 2010 sort of database store data records , manipulate them vb.net forms.

i observing strange , inconsistent behavior:

• the data record populated insert command//insertcommand.executenonquery() api of vb.net
• the data record sometimes not modified update command//updatecommand.executenonquery() api of vb.net. however, api status success , returns number of affected rows 1

at other times, same piece of code able modify record

• during time of failure, if execute couple of sql commands manually (at microsoft query dialog in excel), , re-run vb.net code, records start getting updated!

here's simple piece of code:

    '''''''''''''''''''''''     dim obj new cdatabaseconnection     obj.dbconnect()     dim strsql string     strsql = "update [mytable$] set status = 'cancelled' id = 39"     obj.dbrunupdatequery(strsql)     obj.dbdisconnect()     '''''''''''''''''''''''      public class cdatabaseconnection         private oledbconn oledbconnection         private dataaptr oledbdataadapter         private dset dataset         public sub dbconnect()           dataaptr = new oledbdataadapter           dset = new dataset        end sub         public sub dbdisconnect()           dataaptr.dispose()           dset.dispose()        end sub         public sub dbrunupdatequery(byval sqlq string)              oledbconn = dbsingletonopen()              dataaptr.updatecommand = new oledbcommand(sqlq, oledbconn)             dim rowsaffected = dataaptr.updatecommand.executenonquery()              if rowsaffected = 0                 writelog("warning: 0 rows affected dbrunupdatequery")             end if              dbsingletonclose()         end sub    end class     public function dbsingletonopen()          dim taskdbname string = application.startuppath & "\datafile.xlsx"          dim connstring string = "provider=microsoft.ace.oledb.12.0;" &         "data source=" &        taskdbname &        ";" &         "extended properties=""excel 12.0 xml;hdr=yes"""          oledbconn = new oledbconnection(connstring)         oledbconn.open()      return oledbconn    end function    public sub dbsingletonclose()       oledbconn.close()  end sub 

its been on month since struggling behavior of msexcel/vb.net , have decided take expert help. (i not professional windows developer).

one may argue not using excel etc, lets stick it.

appreciate help.