Creating Tables with Wizards

Access wizards simplify many database tasks, such as building and maintaining tables. Access offers more than 20 database wizards, such as the Contact Management wizard, Time And Billing wizard, Service Call Management wizard, and Video Collection Management wizard. All of these create whole applications, including the table design. The Table wizard automatically builds individual tables and even adds primary keys to a table and links one table to another table for you. The Field Builder helps you maintain tables by simplifying the addition of fields.

Database Wizards

You can view the available database wizards when you start Access 2000 by selecting the Access Database Wizards, Pages, And Projects option in the Microsoft Access dialog box and then clicking OK. When the New dialog box appears, click on the Databases tab and then double-click the icon for the wizard that you want to start. (You can also open the New dialog box by choosing New from the File menu.)

The basic functionality of a wizard-generated database is perfect for novices because it contains forms, tables, reports, and even some sample data. You can examine the database design and use it as a model for your own custom applications. More advanced developers can edit the basic design by adding their own tables, queries, forms, reports, and modules.

The Table Wizard

The Table wizard focuses exclusively on table design. You can open it from the Tables item in the Objects collection in the Database window. Double-click the Create Table By Using Wizard icon to open the initial Table Wizard dialog box. This wizard offers a collection of prebuilt business and personal designs. The business table designs include Contacts, Customers, Employees, Products, Orders, Invoices, and Events tables. The personal designs are very complete and can also serve some small business functions. The designs include Recipes, Plants, Exercise Log, Books, Video Collection, and Recordings tables. The wizard does not populate a table with starter values, but it does suggest links to other tables that are already in the database design. It will even automatically construct these relationships.

After selecting a prebuilt table design, you can populate your custom version of it with fields from a list box. The initial Table Wizard dialog box offers a set of buttons for transferring field names between the Sample Fields list box and the Fields In My New Table list box. Figure 3-4 shows an Invoices table in the process of construction. Several sample fields have been transferred to the Fields In My New Table list box. At this point, you can click Next three times to construct an Invoices table. The defaults create a primary key for you, link your new table to any table already in the database that links to it naturally, and let you enter data directly into the table. When you click the final Finish button after accepting all the defaults, the table opens in Datasheet view so you can populate it with values. You can override any of the defaults before clicking Finish in the final dialog box.

Figure 3-4. Creating an Invoices table in the initial Table Wizard dialog box.

The automatic detection of links to other tables is particularly helpful if you are just starting to learn about relational databases. Figure 3-5 below shows the third Table Wizard dialog box for the creation of an Invoice Details table. This table can include an InvoiceID field that acts as a foreign key to the Invoices table. If the wizard detects the Invoices table, it automatically relates the two tables. While you can override the link or create other links not suggested by the wizard, the safe thing for new developers to do is to accept the default recommendations.

Figure 3-5. A Table Wizard dialog box showing the detection of an automatic link between the Invoice Details table and the previously created Invoices table.

The Field Builder

After you use a wizard to construct a table, you might want to add another field to the table. This apparently simple task can be more difficult than it initially appears, especially if the field needs to link with fields in other tables. This is because it is easier to create a new field with a different data type than it is to create a field in another table. When you try to relate similarly named fields from different tables whose data types are not identical, the attempt will fail without explanation. One solution to this problem is to use the Field Builder when you add new fields to tables.

You open the Field Builder by right-clicking on a blank row in a table's Design view and choosing Build from the shortcut menu. The Field Builder dialog box has two list boxes: one for selecting a table type, such as Invoices or Invoice Details, and one for selecting a field from that table type. When you highlight different table types, the entries change in the Sample Fields list box. Find the field that you want to add, and click OK to add it to the table. This ensures that your new fields will have data types that are consistent with the fields added using the Table wizard.