Returning the Number of Records or Rows Affected by an Action Query in Microsoft Access with VB6/VBA

Provided by: Molly Pell, Technical Project Manager

Action queries in Microsoft Access are queries that add, modify, or delete records from tables using INSERT, UPDATE, and DELETE commands. When you run Action queries interactively, Microsoft Access shows you how many records will be affected before the query is run:

Did you know you can also get this information when you run the query programmatically in VBA or Visual Basic 6?

When you use the Execute method to run the Action query, the Database.RecordsAffected property stores the number of records affected by the most recent Execute method. The difference is that you get the row count after the query is run, rather than before.

Example:

Sub RunActions()
  Dim dbs As DAO.Database
  Dim lngRowsInserted As Long
  Dim lngRowsDeleted As Long

  Set dbs = CurrentDb

  dbs.Execute "INSERT INTO Table1(Field1) SELECT 'DeleteMe'"
  lngRowsInserted = dbs.RecordsAffected

  dbs.Execute "DELETE FROM Table1 WHERE Field1 = 'DeleteMe'"
  lngRowsDeleted = dbs.RecordsAffected

  If lngRowsDeleted = 0 Then
    MsgBox "0 rows deleted. Check the data for errors."
  End If
End Sub

You can display this for the user, store it in a log table or file, or use it to detect potential data errors. For example, if you know that your query should always append at least 1 row, check for RecordsAffected = 0, and inform the user of a potential problem.

Additional Resources

• Our related tip: “Use Execute Method to Suppress Warning Messages while Running Action Queries Programmatically” for a discussion about the benefits of using the Execute method (as opposed to RunSQL or OpenQuery)

• Our Microsoft Access Queries Technical Paper

Good luck!

Return to the tips page