• Create BookmarkCreate Bookmark
  • Create Note or TagCreate Note or Tag
  • PrintPrint
Share this Page URL

Chapter 4. Creating Your Database and Tables > Setting Table Design Options

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 Database window. From the Tools menu, choose Options to take a look at all the custom settings offered.

You can find the first options that affect table design on the General tab, as shown in Figure 4-36. One option that I highly recommend you use is four-digit year formatting. When you set four-digit year formatting, Access 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 Windows Control Panel, you won’t affect them all unless you set four-digit formatting in Access.

Figure 4-36. Some settings that affect table design are on the General tab of the Options dialog box.

As you can see in Figure 4-36, you have two options under Use four-digit year formatting on the General tab. If you choose This database, the setting creates a property in the database you currently have open and affects only that database. If you choose All databases, the setting creates an entry in your Windows registry that affects all databases that you open on your machine.

On this tab, you can also choose a feature 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 choose Track name AutoCorrect info, Access maintains a unique internal ID number for all field names. This allows you to use the new Object Dependencies feature explained in the next chapter. It also allows you to select the next option, Perform name AutoCorrect.

If you choose Perform name AutoCorrect, when you change a field name in a table, Access automatically propagates the name change to other objects (queries, forms, reports, and pages) that use the field. However, Track name AutoCorrect 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 choose Log name AutoCorrect changes, Access 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 tab that contains useful settings that affect table design is the Tables/Queries tab. Click that tab to see the settings shown in Figure 4-37.

Figure 4-37. Several settings that affect table design are located on the Tables/Queries tab of the Options dialog box.

In the upper left corner of this tab, you can set the default field sizes for Text and Number fields. When you choose a data type of Text, Access will automatically fill in the length you choose. When you choose a data type of Number, Access sets the number size to your choice of Byte, Integer, Long Integer, Single, Double, Decimal, or Replication ID. In the upper right corner, you can choose the default data type that Access selects when you type in a new field name in table design and then tab to the Data Type column. Use the AutoIndex on Import/ Create field 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.

The last item on this tab that affects how you work in table Design view is Show Property Update Options buttons. If you choose this option, 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 see more details about this option in the next chapter.

You can find the last option that affects how your tables are stored (and, in fact, all objects in your database) on the Advanced tab shown in Figure 4-38. When you create a new database in Microsoft Access 11, you actually have a choice of two different file formats. You would think that you would see this option in the File New Database dialog box, but it’s actually buried away in the Options dialog box. You should use the Access 2000 format if others with whom you might share this database are still using Microsoft Access version 9 (2000). Choosing the Access 2002 format ensures maximum compatibility of what you build in Access with future versions of the product.

Figure 4-38. Choosing your default database file format.

  • Creative Edge
  • Create BookmarkCreate Bookmark
  • Create Note or TagCreate Note or Tag
  • PrintPrint