Microsoft Access 2010 Bible

Chapter 3: Designing Bulletproof Databases

127

Most often, these rules are added to a table at design time. Enforcing such rules goes a long way

toward preserving the value of the data managed by the database. For example, in Figure 3.19, the

ValidationRule property of the Quantity field (>=0) ensures that the quantity can’t be a

negative number. If the inventory clerk tries to put a negative number into the Quantity field, an

error message box pops up containing the validation text: Must not be a negative number.

FIGURE 3.19

A simple validation rule goes a long way toward preserving the database’s integrity.

You can also establish a tablewide validation rule that provides some protection for the data in the

table. Unfortunately, only one rule can be created for the entire table, making it difficult to provide

specific validation text for all possible violations.

Cross-Reference
You can read examples of using VBA to enforce business rules in many different chapters in this book.

Summary

This chapter examines the relationships between tables in an Access database, and how you nor-

malize the data for the best performance. I also covered the important topic of ensuring data secu-

rity through the built-in integrity rules that are enforced by the Jet database engine. Most Access

databases are built without adequate safeguards; make sure your database applications provide

adequate protection for your user’s data.

You’re now ready to begin exploring using all that data. The next chapter takes on the challenging

topic of constructing powerful, useful queries that return the data in a variety of ways. As you’ll