Table Design   «Prev  Next»
Lesson 7

Enhancing Database table design in MS Access Conclusion

This module discussed various ways of making sure that the data that gets entered into your database comes in a bit cleaner by using field properties such as the Validation Rule property and lookup fields. You also saw how to:
  1. Use the Validation Rule property to test data
  2. Set the Validation Text property to display a custom message
  3. Use the Input Mask Wizard to specify data input formats
  4. Set up fields that display Combo Box and List Box controls for data input
  5. Use the correct data type for optimal performance

Access Macros

One area with major changes in the new version of Access is macros. Not only did the macro interface get a facelift, but it was revamped and upgraded to include table-level triggers and a rich functionality previously available only through VBA coding. Access 2010 offers two types of macros:
  1. UI macros and
  2. Data macros.
We discuss them briefly here:

UI Macros

UI macros are what we used to call macros in previous versions of Access. However, they not only have a new name, but they also feature a number of notable improvements. The UI macro Editor, provides an interface that resembles a code editor complete with complex logic, error handling, and looping through records. In addition, it provides the added convenience of collapsible segments. The redesigned UI macros allow you to create a rich and productive UI for your users to interact with the data. UI macros can be used in combination with Data macros to report back validation rule status and errors that may have occurred.

Data Macros

Data macros can serve as a convenient vehicle to help implement business rules in your application.
Similar to using Triggers in SQL Server, you can use Data macros to attach logic to record events (also referred to as table events), centralizing the logic in one place, the table, where forms that are bound to the table inherit that logic. Data macros also enable you to manage calculated fields, ensuring that the current data stored is always the most accurate. The table events that can be used to set the macros are
  1. BeforeChange,
  2. BeforeDelete,
  3. AfterInsert,
  4. AfterUpdate, and
  5. AfterDelete.
You can also set your macro as a Named macro and associate it with a table. The Named [Data] Macro can be called from other Data macros or UI macros and from VBA code so it can easily be leveraged and reused through the application.

Field Level Properties Validation Purposes - Quiz

Click the Quiz link below to answer a few questions about the entire module.
Field Level Properties Validation Purposes - Quiz