Deleting data from Access with VBA

问题:

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.

回答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.

回答2:

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'
            )
    )

回答3:

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