Referential Integrity Enforced for DBs Created in Access (104983)


The information in this article applies to:

  • Microsoft Visual Basic Standard Edition for Windows 3.0
This article was previously published under Q104983

SUMMARY

Microsoft Visual Basic version 3.0 for Windows has no built-in features or properties that provide Referential Integrity. To make Visual Basic version 3.0 for Windows enforce referential integrity rules on a Microsoft Access database, build the database in Microsoft Access. To do this, open the Database window in Microsoft Access, and choose Relationships... from the Edit menu. Then in the Relationships window, select the Enforce Referential Integrity option.

MORE INFORMATION

For more information on the way Visual Basic version 3.0 handles and enforces referential integrity, please refer to page 85 of "Professional Features Book 2."

Visual Basic version 3.0 can enforce referential integrity between tables as long as the Enforce Referential Integrity option was selected in Microsoft Access. Visual Basic enforces these rules by providing certain error codes when a database built in Microsoft Access has violated the referential integrity rules in Visual Basic code. These are trappable errors in Visual Basic, so you as the programmer have the option to handle these violations as you wish.

Below are the possible errors you could get that refer to referential integrity:

  • Couldn't initialize data access because file 'SYSTEM.MDA' couldn't be opened.
    Error 3028
    In order to ensure referential integrity in databases created by Microsoft Access, Visual Basic must read the Access SYSTEM.MDA file. Make sure the file is in the location specified in the SystemDB entry in the [Options] section in the .INI file.
  • Can't delete or change record. Because related records exist in table 'Item', referential integrity rules would be violated.
    Error 3200
    You tried to perform an operation that would have violated referential integrity rules for related tables. For example, this error occurs if you try to delete or change a record in the "one" table in a one-to-many relationship when there are related records in the "many" table. If you want to delete or change the record, first delete the related records from the "many" table.
  • Can't add or change record. Referential integrity rules require a related record in table 'Item'.
    Error 3201
    You tried to perform an operation that would have violated referential integrity rules for related tables. For example, this error occurs if you try to change or insert a record in the "many" table in a one-to-many relationship, and that record doesn't have a related record in the table on the "one" side. If you want to add or change the record, first add a record to the "one" table that contains the same value for the matching field.
There is more information in the Visual Basic version 3.0 "Professional Features Book 2" manual. Referential integrity implementation differences between Microsoft Access and Visual Basic are described on page 119. System table differences are explained on page 21, and using multiple tables is described on page 85.

Dynaset objects can create an inconsistent dynaset with the DB_INCONSISTENT flag. But it may be harder to keep referential integrity when this flag is specified. See pages 58, 59, and 85 of "Professional Features Book 2."
Modification Type:MinorLast Reviewed:1/8/2003
Keywords:KB104983
©2002 Microsoft Corporation. All rights reserved.