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.

Shouldn't it be `CurrentDb.Execute`, for a `DELETE` statement?... — Xophmeister, Oct 09 '12 at 14:38
It doesn't work either, with the parenthesis (`CurrentDb.Execute()`) I get an error "Function expected" and without the parenthesis I get a syntax error. — dan, Oct 09 '12 at 14:41
Finally managed to get it working, thank you. I will post the final code below. — dan, Oct 09 '12 at 14:46

score 3 · Answer 1 · answered Oct 09 '12 at 14:58

You already solved the problem. Congratulations. See whether a different approach for your WHERE clause is easier to work with.

The WHERE clause checks whether VARIABLE matches a list of values. Instead of using multiple OR conditions to compare VARIABLE with each member of the list, you can simply ask the db engine whether VARIABLE is present IN the list of values.

AND
    (
        [VARIABLE] IN
            (
                'NomAdminContrats',
                'TitreAdminContrats',
                'UnitAdminContrats',
                'AdrAdminContrats',
                'VilleAdminContrats',
                'TelAdminContrats',
                'TelecAdminContrats',
                'CourrielAdminContrats'
            )
    )

score 3 · Accepted Answer · answered Nov 19 '15 at 14:04

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.

Since this answer is more accurate than my workaround, I will set this as the accepted answer. However, at this point, the title is a little bit misleading since it's a not a problem with a specific function in VBA but more of a syntax problem on my part. Anyone can feel free to edit. Thank you, even though it's more than 3 years old lol. — dan, Nov 26 '15 at 20:23

score 0 · Answer 3 · answered Oct 09 '12 at 14:47

Finally managed to get it working. Had to declare another var for the query for some reason.

Dim sql as String
sql = "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')"
CurrentDb.Execute sql

Deleting data from Access with VBA

3 Answers3

Linked