显示更新查询更新了多少条记录 [英] Showing how many records were updated by an update query

问题描述

是否可以在不使用记录集的情况下获得使用更新查询更新的记录的报告?

Is it possible to get a report of the records that are updated using a update query, without using a recordset?

例如:

sqltext = update table employees set bonus = 0 where salary > 50000
DoCmd.RunSQL sqltext

sqltext = update table employees set bonus = 0 where salary > 50000
DoCmd.RunSQL sqltext

此查询运行后，是否可以获取对其执行此更新查询的员工的姓名?

After this query runs, is it possible to get the name of the employees for whom this update query was performed?

推荐答案

使用DoCmd.RunSQL绝不是一个好主意，因为它会生成一个提示(如果您不想要，则必须关闭它)，并且即使发生错误也可以完成更新，并且不会报告错误.更好的方法是将其替换为执行相同SQL的函数:

It's never a good idea to use DoCmd.RunSQL as it generates a prompt (which you have to turn off if you don't want it), and it completes the updates even if errors occur, and doesn't report the errors. Much better is to replace it with a function that executes the same SQL:

Public Function SQLRun(strSQL As String) As Boolean
On Error GoTo errHandler

  CurrentDB.Execute strSQL, dbFailOnError
  SQLRun= True

exitRoutine:
  Exit Function

errHandler:
  MsgBox err.Number & ": " & err.Description, vbExclamation, "Error in SQLRun()"
  Resume exitRoutine
End Function

将其放置在公共模块中后，您可以轻松地进行全局搜索并替换为DoCmd.RunSQL，以将其替换为SQLRun.

Once you've placed this in a public module, you can easily do a global search and replace for DoCmd.RunSQL to replace it with SQLRun.

此函数的另一个版本(返回受影响的记录数)是

Another version of this function that returns the number of records affected is here.

这篇关于显示更新查询更新了多少条记录的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！