Showing how many records were updated by an update

问题:

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

Ex:

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?

回答1:

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

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.

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

回答2:

I know its an old question, but there was no answer that directly answered the OP question (I got here via Google Top 3 results while trying to remember the syntax) If you use the Execute method, you can get the number of records affected directly:

sqltext = "update table employees set bonus = 0 where salary > 50000"
CurrentDb.Execute sqltext
AffectedRows = CurrentDb.RecordsAffected
'Optional Notification
   MsgBox CStr(AffectedRows) & " records were affected by this SQL statement."

回答3:

I don't see any way at this time to get the information after the update query has run unless you have another distinguishing field (maybe an updated date field). Why not run a select query for it and run a report off of that data, THEN run the update query to change the values for 'bonus'.

Let me know if this helps! JFV