ACC: No Difference Between dbConsistent and dbInconsistent

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

SYMPTOMS

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

When you use the dbConsistent and dbInconsistent (or DB_CONSISTENT and DB_INCONSISTENT in version 2.0) constants with the OpenRecordset method in code to create a recordset, it appears not to have any effect on the editability of the recordset. That is, after creating a recordset using the dbConsistent constant, you can change fields from the "one" side of a one-to-many join.

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.

NOTE: Visual Basic for Applications is called Access Basic in Microsoft Access version 2.0. For more information about Access Basic, please refer to the "Building Applications" manual.

CAUSE

If you select Enforce Referential Integrity and Cascade Update Related Fields in the Relationships window for the one-to-many relationship in question, the difference between dbConsistent and dbInconsistent is nullified.

STATUS

This behavior is by design.

MORE INFORMATION

If you change the relationship so that referential integrity is not enforced, you can see the difference between the two constants. The following example demonstrates the difference using the one-to-many relationship between the Customers and Orders tables in the sample database Northwind.mdb (or NWIND.MDB in version 2.0). Those tables are joined on the CustomerID field.

NOTE: In version 2.0, there is a space in the Customer ID field.

The following steps demonstrate the difference between the constants by first opening a recordset using the dbInconsistent constant, changing the CustomerID, opening the recordset again using the dbConsistent constant, and then changing the CustomerID again:

  1. Start Microsoft Access and open the sample database Northwind.mdb (or NWIND.MDB in version 2.0).

  2. Create a new query and add the Customers and Orders tables.

  3. Drag the CustomerID field from both tables to the query grid. Save the query as ConsistentDemo.

  4. Create a new module and enter the following code.

    In Microsoft Access 7.0 and 97:

          Function ConsistentDemo ()
    
             Dim rs As Recordset, db As Database
             Set db = CurrentDB()
             Set rs = db.OpenRecordset("ConsistentDemo", dbOpenDynaset,_
             dbInconsistent)
             rs.FindFirst "[Customers].[CustomerID]='ALFKI'"
             rs.Edit
                rs![Customers.CustomerID] = "ALFKJ"
             rs.Update
             rs.FindFirst "[Customers].[CustomerID]='ALFKJ'"
             rs.Edit
                rs![Customers.CustomerID] = "ALFKI"
             rs.Update
             Set rs = db.OpenRecordset("ConsistentDemo", dbOpenDynaset,_
              dbConsistent)
             rs.FindFirst "[Customers].[CustomerID]='ALFKI'"
             rs.Edit
                rs![Customers.CustomerID] = "ALFKJ"
             rs.Update
             rs.FindFirst "[Customers].[CustomerID]='ALFKJ'"
             rs.Edit
                rs![Customers.CustomerID] = "ALFKI"
             rs.Update
             rs.Close
             db.Close
             MsgBox "Test complete."
          End Function
    
       In Microsoft Access 2.0:
    
       NOTE: In the following sample code, an underscore (_) is used as a line-
       continuation character. Remove the underscore from the end of the line
       when re-creating this code in Access Basic.
    
          Function ConsistentDemo ()
             Dim rs As Recordset, db As Database
             Set db = CurrentDB()
             Set rs = db.OpenRecordset("ConsistentDemo", DB_OPEN_DYNASET,_
             DB_INCONSISTENT)
             rs.FindFirst "[Customers].[Customer ID]='ALFKI'"
             rs.Edit
                rs![Customers.Customer ID] = "ALFKJ"
             rs.Update
             rs.FindFirst "[Customers].[Customer ID]='ALFKJ'"
             rs.Edit
                rs![Customers.Customer ID] = "ALFKI"
             rs.Update
             Set rs = db.OpenRecordset("ConsistentDemo", DB_OPEN_DYNASET,_
              DB_CONSISTENT)
             rs.FindFirst "[Customers].[Customer ID]='ALFKI'"
             rs.Edit
                rs![Customers.Customer ID] = "ALFKJ"
             rs.Update
             rs.FindFirst "[Customers].[Customer ID]='ALFKJ'"
             rs.Edit
                rs![Customers.CustomerID] = "ALFKI"
             rs.Update
             rs.Close
             db.Close
             MsgBox "Test complete."
          End Function
    
    

  5. Run the function by typing the following line in the Debug window (or Immediate window in version 2.0), and then pressing ENTER:

          ? ConsistentDemo()
    

    Note that the code runs without any errors. You are able to change data whether the recordset is opened consistently or inconsistently.

  6. Select the Database window, and then click Relationships on the Tools menu (or on the Edit menu in version 2.0).

  7. Double-click the join line between the CustomerID fields in the Customers and Orders tables.

  8. Click to clear the Enforce Referential Integrity check box, and then click OK.

  9. Run the ConsistentDemo() function again by typing the following line in the Debug window and then pressing ENTER:

          ? ConsistentDemo()
    

    Note that this time you receive the error message:

          Field can't be updated
    

    Because referential integrity is not being enforced for the relationship, the dbInconsistent constant has the expected effect.

NOTE: When you reset the relationship between the Customers and Orders tables, be sure to select the Cascade Update Related Fields check box in addition to the Enforce Referential Integrity check box.

REFERENCES

For more information about using constants with the OpenRecordset method, search the Help Index for "OpenRecordset method."


Additional query words: relationships programming
Keywords : kberrmsg kbprg SynFnc RltOthr
Version : 2.0 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.