Deleting data from Access with VBA

Question

I'm trying to delete every record from my current Access database in VBA where OTP = txtOTP.value and VARIABLE = {NomAdminContrats,TelAdminContrats,TelecAdminContrats, [...]}

Here is my code:

Dim query As Recordset 
Set query = CurrentDb.Execute("DELETE * FROM tb_SOMMAIRE WHERE OTP = '" & txtOTP.value & "' AND (VARIABLE = 'NomAdminContrats' or VARIABLE = 'TitreAdminContrats' or VARIABLE = 'UnitAdminContrats' or VARIABLE = 'AdrAdminContrats' or  VARIABLE = 'VilleAdminContrats' or VARIABLE = 'TelAdminContrats' or VARIABLE = 'TelecAdminContrats' or VARIABLE = 'CourrielAdminContrats')")

I got an error 3219 Invalid Operation when trying with OpenRecordset or Expected function when trying with Execute. I've tried a lot of things but I didn't manage yet to get this query working. I also have the full table in a recordset, would it be easier/faster to do it with myRecordset.Delete? If so, how could I do it?

EDIT

Now trying with CurrentDb.Execute instead of CurrentDb.OpenRecordset. The error is now Function expected instead of Invalid Operation.

Answer 1

You were not supposed to use parentheses for the recordset.execute function, unless you want to send the execute function options, as well as your query.

currentDB.Execute ("SQL EXECUTABLE QUERY", Options)

options is an optional constant that you can include in the function, such as dbDenyWrite, or dbFailOnError. when you don't include options in your execute function, Microsoft uses the default dbInconsistent, and you should not use parentheses.

Set query = CurrentDb.Execute "DELETE * FROM tb_SOMMAIRE..."

The reason your code worked when you used the variable sql is because you did not use the parantheses when you used the variable.