我想从VBA我目前的Access数据库，其中 OTP = txtOTP.value 和可变= {NomAdminContrats，TelAdminContrats删除每个记录，TelecAdminContrats，[...]}

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

下面是我的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')")

我用OpenRecordset试图或预期的功能的与执行尝试时，当得到一个错误3219无效操作。我已经尝试了很多东西，但我没还没有得到这个查询工作。我也有充分的表中的记录，将是更容易/更快地与myRecordset.Delete办呢？如果是这样，我怎么能做到这一点？

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?

修改

现在试图与CurrentDb.Execute而不是CurrentDb.OpenRecordset。该错误是现在的的功能预期，而不是​​的无效操作的。

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

推荐答案

您不应该使用括号的 recordset.execute 功能，除非你要发送的执行函数的选项的，还有您的查询。

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)

选项的是一个可选常量，可以在函数，如 dbDenyWrite 或 dbFailOnError 。当你不包含的选项的在你的执行功能，微软将使用默认的 dbInconsistent ，然后你不应该使用括号。

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..."

您code，当你使用的变量工作的原因 SQL 是因为你没有用括号混合，当你使用的变量。

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