Referential Integrity Enforced for DBs Created in Access

- Microsoft Visual Basic programming system for Windows, version 3.0

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.

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