Part I: Access Building Blocks
126
“hide” a relationship by deleting the tables you see in the Relationships window. Click on a table,
press the Delete key, and Access removes the table from the Relationships window. Removing a
table from the Relationships window doesn’t delete any relationships between the table and other
tables in the database.When building database tables, make sure that the Required property of the foreign key field in the
related table (in the case of tblBookOrders5 and tblBookOrderDetails, the foreign key is
OrderID in tblBookOrderDetails) is set to Yes. This action forces the user to enter a value
in the foreign key field, providing the relationship path between the tables.The relationships formed in the Relationships window are permanent and are managed by Access.
When you form permanent relationships, they appear in the Query Design window by default as
you add the tables (queries are discussed in detail in Chapter 4). Even without permanent relation-
ships between tables, you form temporary relationships any time you include multiple tables in the
Query Design window.Cross-Reference
If you connect to a SQL Server back-end database or use the Microsoft Database Engine and create an Access
Data Project, the Relationships window is different. You can find more about this subject in Chapters 37 and 38.
Deleting relationships
From time to time, you might find it necessary to delete relationships between tables. The
Relationships window is simply a picture of the relationships between tables. If you open the
Relationships window, click on each of the tables in the relationship, and press the Delete key, you
delete the picture of the tables in the relationship, but not the relationship itself. You must first
click on the line connecting the tables and press Delete to delete the relationship, and then delete
each of the table pictures to completely remove the relationship.Application-specific integrity rules
In addition to the referential integrity rules enforced by the Jet database engine, you can establish a
number of business rules that are enforced by the applications you build in Access. In many cases,
your clients or users will tell you the business rules that must be enforced by the application. It’s
up to you as the developer to compose the Visual Basic code, table design, field properties, and so
on that implement the business rules expected by your users.Typical business rules include items such as the following:l The order-entry clerk must enter his ID number on the entry form.l (^) Quantities can never be less than zero.
l The unit selling price can never be less than the unit cost.
l (^) The order ship date must come after the order date.