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

Provided by Molly Pell, Senior Systems Analyst

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:

Affected records

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.

Running a Microsoft Access Action Query with DAO

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

Good luck!

Return to Query Help Center Page