Setting Table Design Options

Now that you understand the basic mechanics of defining tables in your desktop database, it’s useful to take a look at a few options you can set to customize how you work with tables in Design view. Close any open tables so that all you see is the Navigation Pane. Click the Microsoft Office Button and then click Access Options to see all the custom settings offered.

You can find the first options that affect table design in the Advanced category as shown in Figure 27–32. One option that we highly recommend you use is Use Four-Digit Year Formatting, found in the General section. When you enable four-digit year formatting, Access 2007 displays all year values in date/time formats with four digits instead of two. This is important because when you see a value (in two-digit medium date format) such as 15 MAR 12, you won’t be able to easily tell whether this is March 15, 1912 or March 15, 2012. Although you can affect the display of some formats in your regional settings in Control Panel, you won’t affect them all unless you set four-digit formatting in Access.

Figure 27–32: You can find settings that affect table design in the General section in the Advanced category of the Access Options dialog box.

As you can see in Figure 27–32, you have two options under Use Four-Digit Year Formatting in the General section. If you select the This Database check box, the setting creates a property in the database you currently have open and affects only that database. If you select the All Databases check box, the setting creates an entry in your Windows registry that affects all databases that you open on your computer.

In the Current Database category of the Access Options dialog box, you can configure an option that was introduced in Access 2000 called Name AutoCorrect that asks Access to track and correct field name references in queries, forms, and reports. If you select the Track Name AutoCorrect Info check box in the Name AutoCorrrect Options section, Access maintains a unique internal ID number for all field names. This allows you to use the Object Dependencies feature. It also allows you to select the next check box, Perform Name AutoCorrect, as shown in Figure 27–33.

Figure 27–33: You can set Name AutoCorrect options in the Current Database category of the Access Options dialog box.

If you select the Perform Name AutoCorrect check box, when you change a field name in a table, Access 2007 automatically attempts to propagate the name change to other objects (queries, forms, and reports) that use the field. However, Track Name AutoCorrect Info requires some additional overhead in all your objects, so it’s a good idea to carefully choose names as you design your tables so that you won’t need to change them later. Finally, if you select the Log Name AutoCorrect Changes check box, Access 2007 logs all changes it makes in a table called AutoCorrect Log. You can open this table to verify the changes made by this feature. (Access doesn’t create the table until it makes some changes.)

The next category that contains useful settings that affect table design is Object Designers. Click that category to see the settings shown in Figure 27–34.

Figure 27–34: You can find settings that affect table design in the Object Designers category of the Access Options dialog box.

In the Table Design section, you can set the default field type and the default field size for Text and Number fields. The Default Field Type setting allows you to choose the default data type that Access 2007 selects when you type a new field name in table design and then tab to the Data Type column. When you select a data type of Text (either because it is the default data type or you select the Text data type in a new field), Access will automatically set the length you select in the Default Text Field Size box. When you select a data type of Number, Access sets the number size to your choice in the Default Number Field Size box of Byte, Integer, Long Integer, Single, Double, Decimal, or Replication ID. Use the AutoIndex On Import/Create box to define a list of field name prefixes or suffixes for which Access automatically sets the Index property to Yes (Duplicates OK). In the default list, for example, any field that you define with a name that begins or ends with ID will automatically have an index.

If you select the Show Property Update Options Buttons check box, a smart tag appears that offers to automatically update related properties in queries, forms, and reports when you change certain field properties in a table design.

You can find the last option that affects how your tables are stored (and, in fact, all objects in your database) in the Popular category, as shown in Figure 27–35. When you create a new database in Access 2007, you actually have a choice of three different file formats. These options also appear in the File New Database dialog box, but this setting in the Access Options dialog box controls which file format appears as the default. You should use the Access 2000 format if others with whom you might share this database are still using Access version 9 (2000), or you should use the 2002–2003 format if others sharing this database are still using Access version 10 (2002) or Access version 11 (2003). Selecting the Access 2007 format ensures maximum compatibility of what you build in Access with future versions of the product.

Figure 27–35: You can select your default database file format in the Creating Databases section of the Popular category in the Access Options dialog box.