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.
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.
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")
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.