Returns the number of records affected by the most recently invoked Execute method.
Syntax
expression.RecordsAffected
expression A variable that represents a QueryDef object.
Remarks
When you use the Execute method to run an action query from a QueryDef object, the RecordsAffected property will contain the number of records deleted, updated, or inserted.
Example
This example uses the RecordsAffected property with action queries executed from a Database object and from a QueryDef object. The RecordsAffectedOutput function is required for this procedure to run.
Visual Basic for Applications
Sub RecordsAffectedX()
Dim dbsNorthwind As Database
Dim qdfTemp As QueryDef
Dim strSQLChange As String
Dim strSQLRestore As String
Set dbsNorthwind = OpenDatabase("Northwind.mdb")
With dbsNorthwind
' Print report of contents of the Employees
' table.
Debug.Print _
"Number of records in Employees table: " & _
.TableDefs!Employees.RecordCount
RecordsAffectedOutput dbsNorthwind
' Define and execute an action query.
strSQLChange = "UPDATE Employees " & _
"SET Country = 'United States' " & _
"WHERE Country = 'USA'"
.Execute strSQLChange
' Print report of contents of the Employees
' table.
Debug.Print _
"RecordsAffected after executing query " & _
"from Database: " & .<strong class="bterm">RecordsAffected</strong>
RecordsAffectedOutput dbsNorthwind
' Define and run another action query.
strSQLRestore = "UPDATE Employees " & _
"SET Country = 'USA' " & _
"WHERE Country = 'United States'"
Set qdfTemp = .CreateQueryDef("", strSQLRestore)
qdfTemp.Execute
' Print report of contents of the Employees
' table.
Debug.Print _
"RecordsAffected after executing query " & _
"from QueryDef: " & qdfTemp.<strong class="bterm">RecordsAffected</strong>
RecordsAffectedOutput dbsNorthwind
.Close
End With
End Sub
Function RecordsAffectedOutput(dbsNorthwind As Database)
Dim rstEmployees As Recordset
' Open a Recordset object from the Employees table.
Set rstEmployees = _
dbsNorthwind.OpenRecordset("Employees")
With rstEmployees
' Enumerate Recordset.
.MoveFirst
Do While Not .EOF
Debug.Print " " & !LastName & ", " & !Country
.MoveNext
Loop
.Close
End With