ACC2000: RecordsAffected Property Incorrect for SQL Pass-Through

When you use the RecordsAffected property of a Database or QueryDef object to determine the number of records affected by the Execute method, the value is incorrect for SQL pass-through queries.

The RecordsAffected property is not set when you execute a pass-through query.

When you view the RecordsAffected property, it reflects the number of records affected by the most recent Execute method on an object that uses the Microsoft Jet database engine. Pass-through queries bypass the Jet database engine and interact directly with the back-end database through the ODBC driver.

The following example shows a correct RecordsAffected value because the Execute method operates on a table in the sample database Northwind.mdb:

1. Start Microsoft Access and open the sample database Northwind.mdb.
2. Create a module and type the following procedure:

   Sub ViewRecs()
      Dim db As DAO.DATABASE
      ' Substitute the correct path to Northwind.mdb on your computer.
      Set db = DBEngine.Workspaces(0).OpenDatabase _
      ("C:\Program Files\Microsoft Office\Office\Samples\Northwind.mdb")
      db.Execute "Update employees set country = 'United States' " _
       & "where country = 'USA';"
      Msgbox db.RecordsAffected
   End Sub
   					

3. To test this procedure, type the following line in the Immediate window, and then press ENTER.
   ViewRecs
   Note that a message box opens and displays the number of records affected by the update. The number is 5 in an unaltered copy of Northwind.mdb.

Steps to Reproduce Behavior

The following example uses an ODBC connection to the Pubs database in Microsoft SQL Server. Substitute the correct parameters for your ODBC database in the OpenDatabase method.

NOTE: If the number of records affected by the most recent Execute method in your current instance of Microsoft Access happens to be 2, the RecordsAffected property returns the correct number of records. Quit and restart Microsoft Access to ensure that you reproduce the intended results from this example.

1. Start Microsoft Access and open the sample database Northwind.mdb.
2. Create a module and type the following procedure:
   
   Note In the following sample code, you must change UID=<username> and PWD=<strong password> to the correct values. Make sure that the user ID has the appropriate permissions to perform this operation on the database.

   Sub WrongNum()
      Dim db As DAO.DATABASE
      Dim SPTErr As Error
      On Error GoTo WrongNum_err
      ' Substitute your own ODBC connection parameters.
      Set db = OpenDatabase("", False, False, _
         "ODBC;DSN=Pubs Database;DATABASE=pubs;UID=<username>;PWD=<strong password>")
      ' Create a table in SQL Server and create a unique index.
      db.Execute "create table testrecs (f1 int)", dbSQLPassThrough
      db.Execute "create unique index idx on testrecs (f1)", _
         dbSQLPassThrough
   
      ' Insert two records.
      db.Execute "Insert into testrecs values(1)", dbSQLPassThrough
      db.Execute "Insert into testrecs values(2)", dbSQLPassThrough
   
      ' This message box returns 0 records.
      Msgbox db.RecordsAffected & " Records Affected."
   
      ' Delete the testrecs table.
      db.Execute "drop table testrecs", dbSQLPassThrough
   
      ' This message box returns 0 records.
      Msgbox db.RecordsAffected & " Records Affected."
    Exit Sub
      WrongNum_err:
      For Each SPTErr In DBEngine.Errors
         With SPTErr
            Msgbox .Number & vbcr & .Description & vbcr & .Source
         End With
      Next SPTErr
   End Sub
   					

3. To test this procedure, type the following line in the Immediate window, and then press ENTER.
   WrongNum
   Note that a message box opens twice and displays the number 0 instead of the actual number of records affected, which is 2.

For more information about the RecordsAffected property, click Microsoft Visual Basic Help on the Help menu, type recordsaffected property in the Office Assistant or the Answer Wizard, and then click Search to view the topic.

For more information about SQL pass-through queries, click Microsoft Access Help on the Help menu, type what is an sql query and when would you use one? in the Office Assistant or the Answer Wizard, and then click Search to view the topic.