BUG: DAO dbFailOnError causes RecordsAffected to return 0

This article was previously published under Q195226
This article has been archived. It is offered "as is" and will no longer be updated.
When you run an action query by using Data Access Objects (DAO) and theMicrosoft Jet database engine against an ODBC table, if you specify thedbFailOnError argument, the RecordsAffected property always returns 0,regardless of the number of records affected by the statement.
This is a confirmed problem in Microsoft Jet database engine versions 3.5and 3.51.
To resolve this problem, do one of the following:
  • Remove the dbFailOnError flag.

  • Use ODBCDirect or another data access method, such as Remote Data Objects (RDO) or ActiveX Data Objects (ADO).
Microsoft has confirmed this to be a problem in the Microsoft productslisted at the beginning of this article.
When you run an action query, you can find out how many records wereaffected by examining the RecordsAffected property of the Database orQueryDef object. If you specify the dbFailOnError argument so that you cantrap for errors, the Microsoft Jet database engine incorrectly returns 0for the RecordsAffected property.

Microsoft provides programming examples for illustration only, without warranty either expressed or implied. This includes, but is not limited to, the implied warranties of merchantability or fitness for a particular purpose. This article assumes that you are familiar with the programming language that is being demonstrated and with the tools that are used to create and to debug procedures. Microsoft support engineers can help explain the functionality of a particular procedure, but they will not modify these examples to provide added functionality or construct procedures to meet your specific requirements.

Steps to Reproduce Problem

  1. In Microsoft Access or Microsoft Visual Basic, create a new project with a form (Form1) and a command button (Command1).
  2. Add the following code to the form's module:
          Private Sub Command1_Click()      Dim db As Database        Set db = DBEngine(0).OpenDatabase("", False, False, _      "ODBC;driver={SQL Server};server=xxx;uid=;pwd=;database=pubs")        db.Execute "Update authors set city = 'Paris' " _         & "where city = 'Oakland';", dbFailOnError        Debug.Print "To Paris:", db.RecordsAffected        db.Execute "Update authors set city = 'Oakland' " _         & "where city = 'Paris';"        Debug.Print "To Oakland:", db.RecordsAffected        db.Close      End Sub   NOTE: You must change the connect string in the OpenDatabase method to   point to a valid data source.					
  3. Use the Projects|References menu (or Tools|References menu) to add a reference to the following object library:
    Microsoft DAO 3.5 Object Library


    Microsoft DAO 3.51 Object Library
    NOTE: Microsoft Access has this reference by default.
  4. Run the project, open the form, and click the command button. The Immediate/Debug window will display 0 (incorrect value) for the first result and the correct number for the second result.
kbDSupport kbdse

Article ID: 195226 - Last Review: 02/21/2014 00:30:40 - Revision: 4.3

Microsoft Access 97 Standard Edition, Microsoft Visual Basic 5.0 Professional Edition, Microsoft Visual Basic 6.0 Professional Edition, Microsoft Visual Basic 5.0 Enterprise Edition, Microsoft Visual Basic 6.0 Enterprise Edition

  • kbnosurvey kbarchive kbbug kbpending KB195226