Setting Table Design Options

Now that you understand the basic mechanics of defining tables in your desktop database, it's useful to look at a few options that 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 File tab on the Backstage view and then click Options to see all the custom settings offered.

You can find the first options that affect table design in the Client Settings category. 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 2010 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 tell easily 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.

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 AutoCorrect Options section, Access maintains a unique internal ID number for all field names. It also allows you to select the next check box, Perform Name AutoCorrect.

If you select the Perform Name AutoCorrect check box, when you change a field name in a table, Access 2010 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 choose names carefully as you design your tables so that you won't need to change them later. Note that Access does not attempt to propagate the name change to any Visual Basic code you created in your database. Finally, if you select the Log Name AutoCorrect Changes check box, Access 2010 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 affecting table design is Object Designers.

In the Table Design View 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 2010 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 have an index automatically.

If you select the Show Property Update Options Buttons check box, a smart tag appears that offers to update related properties automatically in queries, forms, and reports when you change certain field properties in a table design. You can see more details about this option in the next tutorial.

You can find the last option that affects how your tables are stored (and, in fact, all objects in your database) in the General category. When you create a new database in Access 2010, 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-used by both Access 2007 and Access 2010-ensures maximum compatibility of what you build in Access with future versions of the product. Note that if you choose to use an older file format, you won't be able to use some of the new features found only in the .accdb file format, such as Attachment, Multi-Value Field, and Calculated data types.