RecordsAffected Property

Applies To

Connection object, Database object, QueryDef object.

Description

Returns the number of records affected by the most recently invoked Execute method.

Return Values

The return value is a Long from 0 to the number of records affected by the most recently invoked Execute method on either a Database or 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.

When you use RecordsAffected in an ODBCDirect workspace, it will not return a useful value from an SQL DROP TABLE action query.

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.

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: " & .RecordsAffected
        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.RecordsAffected
        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

End Function
Example (Microsoft Access)

The following example prints the number of records updated by an action query:

Sub RecordsUpdated()
    Dim dbs As Database, qdf As QueryDef
    Dim strSQL As String

    ' Return reference to current database.
    Set dbs = CurrentDb
    strSQL = "UPDATE Employees SET Title = " _
        & "'Senior Sales Representative' " & "WHERE Title = 'Sales Representative';"
    ' Create new QueryDef object.
    Set qdf = dbs.CreateQueryDef("UpdateTitles", strSQL)
    ' Execute QueryDef object.
    qdf.Execute
    Debug.Print qdf.RecordsAffected
    Set dbs = Nothing
End Sub