ACC: RecordsAffected Property Incorrect for SQL Pass-Through

Last reviewed: August 29, 1997
Article ID: Q163703
The information in this article applies to:
  • Microsoft Access versions 7.0, 97

SYMPTOMS

Moderate: Requires basic macro, coding, and interoperability skills.

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 with SQL pass-through queries.

This article assumes that you are familiar with Visual Basic for Applications and with creating Microsoft Access applications using the programming tools provided with Microsoft Access. For more information about Visual Basic for Applications, please refer to your version of the "Building Applications with Microsoft Access" manual.

CAUSE

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

STATUS

This behavior is by design.

MORE INFORMATION

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 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 Debug 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 will return the correct number of records. Quit and restart Microsoft Access to ensure that you reproduce the desired results from this example.

  1. Start Microsoft Access and open the sample database Northwind.mdb.

  2. Create a module and type the following procedure:

          Sub WrongNum()
             Dim db As DATABASE
             Dim SPTErr As Error
             On Error GoTo WrongNum_err
             ' Substitute your own ODBC connection parameters.
             Set db = OpenDatabase("", False, False, _
                "ODBC;DSN=Pubs1;DATABASE=pubs;UID=sa;PWD=")
             ' 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 Debug 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.

REFERENCES

For more information about the RecordsAffected property, search the Help Index for "RecordsAffected property."

Keywords          : kbusage ODBCSPT QryPass MdlQry
Version           : 7.0 97
Platform          : WINDOWS
Hardware          : x86
Issue type        : kbprb
Solution Type     : Info_Provided


================================================================================


THE INFORMATION PROVIDED IN THE MICROSOFT KNOWLEDGE BASE IS PROVIDED "AS IS" WITHOUT WARRANTY OF ANY KIND. MICROSOFT DISCLAIMS ALL WARRANTIES, EITHER EXPRESS OR IMPLIED, INCLUDING THE WARRANTIES OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE. IN NO EVENT SHALL MICROSOFT CORPORATION OR ITS SUPPLIERS BE LIABLE FOR ANY DAMAGES WHATSOEVER INCLUDING DIRECT, INDIRECT, INCIDENTAL, CONSEQUENTIAL, LOSS OF BUSINESS PROFITS OR SPECIAL DAMAGES, EVEN IF MICROSOFT CORPORATION OR ITS SUPPLIERS HAVE BEEN ADVISED OF THE POSSIBILITY OF SUCH DAMAGES. SOME STATES DO NOT ALLOW THE EXCLUSION OR LIMITATION OF LIABILITY FOR CONSEQUENTIAL OR INCIDENTAL DAMAGES SO THE FOREGOING LIMITATION MAY NOT APPLY.

Last reviewed: August 29, 1997
© 1998 Microsoft Corporation. All rights reserved. Terms of Use.