删除5条记录，但RecordsAffected属性为0 [英] Delete 5 Records but RecordsAffected Property is 0

问题描述

这是一个代码段:

Private Sub frmSearch_UnMarkAll()
Const sqlD As String = "DELETE * FROM PickList WHERE TableName = ""CARS"" AND KeyNo IN (" & _
"SELECT RecNo FROM Cars "
If frmSearch.WhereSql <> vbNullString Then
    CurrentDb.Execute sqlD & frmSearch.WhereSql & ")"
End If
Stop
End Sub

通过在f​​rmSearch表单上单击命令按钮来调用此函数.该按钮引发由上述代码处理的事件. WhereSql是查找屏幕上显示的列表框中的where子句.它的值类似于"WHERE Cars.RecNo> 1441" 当代码到达Stop语句时，我通过使用立即窗口并键入以下命令来检查RecordsAffected属性:

This function is called by clicking a command button on the frmSearch form. The button raises an event that is handled by the above code. WhereSql is the where clause from the listbox displayed on the find screen. It has a value like "WHERE Cars.RecNo > 1441" When the code reached the Stop statement, I checked the RecordsAffected property by using the immediate window and typing:

? Currentdb.RecordsAffected 
 0 is the result.

我检查了PickList表，并删除了相应的项目.

I checked the PickList table and the appropriate items were deleted.

我的问题:这是一个错误吗?或者，删除记录不影响记录是否正常?

My question: Is this a bug? Or, is it normal that deleting a record does not affect it?

我已经解决了这个问题.我从PickList表中删除了SequenceNo字段.我在PickList表中的TableName和KeyNo上创建索引，并将Unique指定为yes.现在我只是

I have gotten around the problem. I removed the SequenceNo field from the PickList table. I created and index on TableName and KeyNo in the PickList table and specified Unique as yes. Now I just

将INERT插入PickList(表名，LastChangedDate，键号)
值(表名，now()，从表名中删除)

INSERT INTO PickList (TableName,LastChangedDate,KeyNo)
Values(tablename,now(),recno from the tablenamed)

如果由于错误3022而导致INSERT失败，则是在重复输入的尝试，本例中我忽略了该尝试.其他所有功能都按照我最初想要的方式运行.当我在代码中测试或通过在调试器中打印视图时，RecordsAffected属性始终为零.这仍然令人困惑，因为如果您以交互方式从访问中运行操作查询，则会显示所有这些信息.如果运行其他关系DBMS(标准的关系DBMS)，则在使用查询引擎时始终会有反馈.我发现很难相信如果这是访问中的错误，其他人也不会失败.我倾向于认为我以某种方式搞砸了.希望我不是唯一的先驱.谢谢，提前查看此内容.

If the INSERT fails due to error 3022, it is an attempt at duplicate entry which I ignore in this example. Everything else functions the way I wanted to function in the first place. The RecordsAffected property is always zero when I test it in my code or view by printing in the debugger. That is still perplexing because if you run action queries from access interactively, all that information is presented. If you run other relational DBMS's (the ones that are standard) there is always feedback when you use the query engine. I find it hard to believe that others are not failing if this is a bug in access. I am inclined to think I screwed up somehow. Hopefully, I am not the lone pioneer. Thanks, in advance for looking at this.

推荐答案

有关什么是解释，请参见答案此处出问题了.实际上，当您获得RecordsAffected属性时，您并不是在引用同一对象.您需要设置显式引用并在两种情况下都使用. 像这样:

See the answer here for explanation of what is going wrong. In effect you are not referring to the same object when you obtain the RecordsAffected property. You need to set explicit reference and use in both cases. Something like:

dim db as dao.database
set db=currentdb
db.execute "some SQL statement", dbfailonerror
debug.print db.recordsaffected

这篇关于删除5条记录，但RecordsAffected属性为0的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！