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