How can I get a value from the update query prompt in Access VBA?

Question

When we run a update query we get prompt saying that 'these many recordr are going to be updated. do you want to continue' is it possible to capture the value in the prompt message to a variable i.e the number of records going to be updated.

Answer 1

If you run the query from code, you can use the records affected property:

Dim db As Database
Set db=CurrentDB
db.Execute "Some SQL here"
db.RecordsAffected

If you use a transaction, you can rollback.

Answer 2

Yes, you can get the number of records updated via the RecordsAffected property:

Function RowsChanged(updateQuery As String) As Long
    Dim qry As QueryDef

    Set qry = CurrentDb.QueryDefs(updateQuery)
    qry.Execute

    RowsChanged = qry.RecordsAffected
End Function

You can call this function with the name of your update query to get the number of rows updated:

Dim numRows as long
numRows = RowsChanged("UpdateQuery")

Answer 3

Patrick Cuff proposed this function:

  Function RowsChanged(updateQuery As String) As Long
    Dim qry As QueryDef

    Set qry = CurrentDb.QueryDefs(updateQuery)
    qry.Execute

    RowsChanged = qry.RecordsAffected
  End Function

I don't understand why one would go to the trouble of assigning a QueryDef variable to execute a query when it can be done directly CurrentDB.Execute without initializing (or cleaning up) any object variables.

Obviously, a parameter query is going need to use the QueryDef approach, since you have to assign the values to the parameters before executing it. But without parameters, there's no reason to make it more complicated than necessary. With a generic function like this that isn't set up to handle parameter queries, it seems wrongly designed.

And, of course, it ought also to use dbFailOnError, so that you don't get unexpected results (dbFailOnError works with QueryDef.Execute, just as it does with CurrentDB.Execute). In that case, there really needs to be an error handler.

Rather than write an error handler every time you execute SQL, you can do this, instead. The following function returns the RecordsAffected and will recover properly from errors:

  Public Function SQLRun(strSQL As String) As Long
  On Error GoTo errHandler
    Static db As DAO.Database

    If db Is Nothing Then 
       Set db = CurrentDB
    End If
    db.Execute strSQL, dbFailOnError
    SQLRun = db.RecordsAffected

  exitRoutine:
    Exit Function

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

It can also be used to replace DoCmd.RunSQL (you just call it and ignore the return value). In fact, this function was entirely designed for use as a global replacement for DoCmd.RunSQL.