BUG: DAO dbFailOnError causes RecordsAffected to return 0

• Microsoft Access 97
• Microsoft Visual Basic Professional and Enterprise Editions for Windows, versions 5.0, 6.0

SYMPTOMS

When you run an action query by using Data Access Objects (DAO) and the Microsoft Jet database engine against an ODBC table, if you specify the dbFailOnError argument, the RecordsAffected property always returns 0, regardless of the number of records affected by the statement.

CAUSE

This is a confirmed problem in Microsoft Jet database engine versions 3.5 and 3.51.

RESOLUTION

To resolve this problem, do one of the following:

• Remove the dbFailOnError flag.
  
  -or-



• Use ODBCDirect or another data access method, such as Remote Data Objects (RDO) or ActiveX Data Objects (ADO).

STATUS

Microsoft has confirmed this to be a problem in the Microsoft products listed at the beginning of this article.

MORE INFORMATION

When you run an action query, you can find out how many records were affected by examining the RecordsAffected property of the Database or QueryDef object. If you specify the dbFailOnError argument so that you can trap for errors, the Microsoft Jet database engine incorrectly returns 0 for the RecordsAffected property.

Microsoft provides programming examples for illustration only, without warranty either expressed or implied, including, but not limited to, the implied warranties of merchantability and/or fitness for a particular purpose. This article assumes that you are familiar with the programming language being demonstrated and the tools used to create and debug procedures. Microsoft support professionals 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 needs. If you have limited programming experience, you may want to contact a Microsoft Certified Solution Provider or the Microsoft fee-based consulting line at (800) 936-5200. For more information about Microsoft Certified Solution Providers, please see the following page on the World Wide Web:

http://www.microsoft.com/mcsp/

http://www.microsoft.com/support/supportnet/overview/overview.asp

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
   
   -or-
   
   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.

Additional query words: kbDSupport kbdse

Keywords : kbdta AccCon KbVBA
Version : WINDOWS:5.0,6.0,97
Platform : WINDOWS
Issue type : kbbug