MS Access 2002 / MS SQL 2008 R2 - 根据特定条件删除记录的问题

I'm trying to get my code to delete records from an access 2002 database table that is linked to an MS Sql 2008 database table through a form event. I just want it to delete all records where User is null and Date is null.

我试图让我的代码从通过表单事件链接到MS Sql 2008数据库表的Access 2002数据库表中删除记录。我只是想删除User为null且Date为null的所有记录。

Here's the snippet that's giving me trouble:

这是给我带来麻烦的片段:

Dim delSQL As String

DoCmd.SetWarnings False
delSQL = "DELETE * FROM Notifications WHERE User IsNull;"
Set rst = CurrentDb.OpenRecordset(delSQL, dbOpenSnapshot)

DoCmd.RunSQL delSQL
rst.Close
Set rst = Nothing
DoCmd.SetWarnings True

I honestly don't know why this isn't working. I ran the SQL statement above (not including the Date part as I wanted to get the User criteria working first) on the SQL server side and it works. But every time I try to get it to run in VBA for Access 2002, I get an error when it goes to execute the SQL.

老实说,我不知道为什么这不起作用。我在SQL服务器端运行上面的SQL语句(不包括Date部分,因为我想让用户标准先工作)并且它可以工作。但每次我尝试让它在VBA for Access 2002中运行时,我在执行SQL时都会收到错误。

1 个解决方案

#1

User is a reserved word. Either enclose it in square brackets, or qualify the field name with the table name or alias.

用户是保留字。将其括在方括号中,或使用表名或别名限定字段名称。

IsNull is a function. Either use Is Null as @AlexK. suggested or use the field name as the argument to IsNull().

IsNull是一个函数。使用Is Null作为@AlexK。建议或使用字段名称作为IsNull()的参数。

The * in DELETE * is not required. Access will not throw an error if you include it. And it can be useful when you wish to preview the affected rows in the Access query designer's Datasheet View.

DELETE *中的*不是必需的。如果包含Access,Access不会抛出错误。当您希望在Access查询设计器的数据表视图中预览受影响的行时,它会很有用。

delSQL = "DELETE * FROM Notifications AS n WHERE n.User Is Null"

I suggest you leave SetWarnings on, and use CurrentDb.Execute with dbFailOnError to execute the DELETE. Since the data source is a linked SQL Server table, also include dbSeeChanges:

我建议您启用SetWarnings,并使用CurrentDb.Execute和dbFailOnError来执行DELETE。由于数据源是链接的SQL Server表,因此还包括dbSeeChanges:

DoCmd.SetWarnings True
CurrentDb.Execute delSQL, dbSeeChanges + dbFailOnError

That approach will give you better information about any problems encountered.

该方法将为您提供有关遇到的任何问题的更好信息。

#1