DELETE Query Functions in Access but not in VB.NET

[gponto18]

I have a very frustrating and confusing problem here; I have a SQL query that functions perfectly well in Access however when I code it in VB.NET it does not yield the desired results;here is the code:

        Dim oledbConnection As New OleDb.OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & InstallLocation & "SDE.mdb;User Id=admin;Password=;")
        oledbConnection.Open()
 
        Dim oledbCommand As New OleDb.OleDbCommand("DELETE Sheet1.F4 FROM Sheet1 WHERE (((Sheet1.F4) Is Null)) OR (Sheet1.F4) LIKE '*E*';", oledbConnection)
        oledbCommand.ExecuteNonQuery()
        oledbConnection.Close()

The query runs and all nulls are dropped from the table, however none of rows that contain "E" get dropped.

Any advice would be greatly appreciated.

[gponto18]

To narrow down the problem a bit, this line executes but does not delete anything from the table, even though when run in Access, it works fine:

DELETE Sheet1.F4 FROM Sheet1 WHERE (Sheet1.F4) LIKE '*E*';

[kenobewan]

I suggest removing the ";", not used to access in sql we use %. However does seem like a syntax problem.

[gponto18]

I suggest removing the ";", not used to access in sql we use %. However does seem like a syntax problem.

Thank you for your reply; I tried your suggestion, unfortunately removing the ";" has no appreciable effect.

What is particularly vexing about this problem is that the statement works perfectly in Access but not in VB.NET.

[gponto18]

Well I was able to solve this problem by using the Access Interop Libraries and running the SQL Query internally in Access.

Somehow though I think there must be a better way to do this.

[thaboloko]

if you want to execute your query by using the oledbcommand (that would improve your app performance, I guess...) you should replace the star sign ( * ) with a percent sign ( % ) on your LIKE Operator.

That should solve the problem, I figure it out that , after several days.

Check your code again here, I made the correction:

Dim oledbConnection As New OleDb.OleDbConnection("Provider=Microsoft.Jet.OLED B.4.0;Data Source=" & InstallLocation & "SDE.mdb;User Id=admin;Password=;")
oledbConnection.Open()

Dim oledbCommand As New OleDb.OleDbCommand("DELETE Sheet1.F4 FROM Sheet1 WHERE (((Sheet1.F4) Is Null)) OR (Sheet1.F4) LIKE '%E%';", oledbConnection)
oledbCommand.ExecuteNonQuery()
oledbConnection.Close()

PS. Remove the ";" anyway. =]