3

When we run a update query we get prompt saying that 'these many records 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.

braX
  • 11,506
  • 5
  • 20
  • 33
tksy
  • 3,429
  • 17
  • 56
  • 61

3 Answers3

6

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.

Fionnuala
  • 90,370
  • 7
  • 114
  • 152
4

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.

GEOCHET
  • 21,119
  • 15
  • 74
  • 98
David-W-Fenton
  • 22,871
  • 4
  • 45
  • 58
  • I copied/pasted your code above into a new module in Access 2003. When I run it, I get "Error in SQLRun(), 424: Object required." for SQLRun = dbLocal.RecordsAffected. – Patrick Cuff Dec 10 '08 at 00:29
  • When I run Remou's solution, I get a result that 0 rows where affected, but the table had 1 row updated. When I run my solution I get the table updated and the correct number of rows updated returned. – Patrick Cuff Dec 10 '08 at 00:42
  • Sorry about that -- I copied out of an app where I use a cached database variable accessed via function called dbLocal that returns a database object. I've fixed the code -- just use CurrentDB for both the .Execute and the .RecordsAffected. Please undo the down vote now that I've fixed the error. ;) – David-W-Fenton Dec 12 '08 at 03:32
  • Just to be clear -- you can't use CurrentDB and get the RecordsAffected, since each call to CurrentDB returns a different pointer to the currently-opened database. I altered the code in January to fix that problem. – David-W-Fenton Mar 21 '09 at 03:46
3

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
  • 28,540
  • 12
  • 67
  • 94