Choosing How to Create Your Database

Before you can create your database tables and actually enter data, you must create a database file. The database is really just a container file that holds all the database objects, such as the tables, forms, and reports that I introduced in Lesson 1. You have three options for creating a new database: You can create a blank database from scratch, create a new database based on a database template, or you can create a new database based on the structure of an existing database file. This option actually creates a copy of the existing database file including the database's structure and the objects contained in the database (minus the data it contains). The third alternative would be great in situations where you want to share the structure for a database with a colleague and also show them how your data is organized in the database.

What Are Projects? Another option when you use the New File Task Pane is a project. Projects are Access front-ends or conduits to powerful SQL databases maintained on a server running Microsoft SQL Server. Access allows you to access this remote data just as you would a database that you created that you have saved on your computer.

Creating a new database based on a template (a template other than the Blank Database template) means that you take advantage of a Database Wizard, which not only creates your new database file but also helps you quickly create tables, forms, and other objects for the database.

Database Wizard Access provides several templates for creating new database files, and the Database Wizard walks you through the process of creating objects, such as tables, for the new database.

Whether you create your new database from scratch or use one of the database templates depends on how closely one of the Access templates meets your database needs. If one of the templates provides you with the type of tables and other objects necessary for your database, it makes sense to use a template. For example, if you want to create a database that helps you manage your company's inventory, you can take advantage of the Inventory Control template that Access provides. This template provides you with the basic tables and other objects to start the process of getting a handle on your inventory database.

In some cases, the templates might not meet your needs. For example, if you want to create a complex database that allows you to track sales, customers, and employee performance, it might be easier to create a blank database and then create each table for the database as needed. Let's start the overview of database creation with creating a blank database.

Selecting a Database File Type

One thing to discuss before you look at creating a new database is the database file format. By default, new databases created in Access are created in the Access 2000 file format. This makes your database files compatible with earlier versions of Access, such as Access 2000 and Access 97.

Saving the database in the Access 2000 file format does not prevent you from using any of the tools or features available in Access 2003. If you use your database files only in Access 2002 or 2003, you can set the default file format for new databases to Access 2002-2003. You must have a database (blank or otherwise ) open to access the Options dialog box. Select the Tools menu, and then select Options . The Options dialog box opens.

Select the Advanced tab on the Options dialog box. Click the Default File Format drop-down box and select Access 2002-2003 . Now let's take a look at creating new databases.

Creating a Blank Database

Creating a blank database is very straightforward. As mentioned previously, you are just creating the container file that holds all the objects that actually make up the database. To create a blank database, follow these steps:

1. In the Access window select the New button on the Database toolbar or select File , then New . The New File Task Pane will appear.

2. Select Blank Database in the task pane. The File New Database dialog box appears (see Figure 2.1).

   Figure 2.1. Provide a location and a name for the new database file.

   

3. Use the Save In drop-down box to locate the folder in which you want to save the new database. Type a name for the new file into the File Name text box.

4. When you are ready to create the database file, click Create . The new database window appears in the Access workspace (see Figure 2.2).

   Figure 2.2. A new database window opens in Access.

   

The database window provides you with a set of icons that enable you to select a particular object type. For example, the Tables icon is selected by default after you create the new database (which makes sense, because you need to create at least one table before you can create any of the other object types, such as a form or a report).

Shortcuts for different methods of creating tables are provided at the top of the Object pane. After you create a new table for the database, it is listed in this pane. In Lesson 3, "Creating a Table with the Table Wizard," and Lesson 4, "Creating a Table from Scratch," you will take a look at creating tables.

The database window enables you to view the different objects that you've created for a particular database (or those that were created when you used the Database Wizard). When you want to switch the database window's focus to a different Access object, all you have to do is click the appropriate icon in the Objects list.

Different Ways to View the Database Windows The toolbar on the database window provides buttons for opening or creating a particular database object, such as a table or a form. The toolbar also provides buttons that can be used to change the view in the Object pane: Large Icons, Small Icons, List (the default view) and Details (which provides information such as when the object was last modified). To collapse the icons shown on the left side of the Database window to categories such as Objects, click the Groups button.

Creating a Database from a Template

Another option for creating a new database is using one of the Access database templates. Templates are available for asset tracking, contact management, inventory control, and other database types. Another perk of using an Access template to create a new database is that a Database Wizard creates tables and other objects, such as forms and reports, for the new database. The wizard also sets up the relationships between the various tables (making your database relational).

Your interaction with the Database Wizard is somewhat limited; the wizard allows you to select the fields that will be used in the tables that it creates for the database. However, you don't have a say about which tables are initially created (tables can always be deleted later if you don't need them). You are, however, given the opportunity to select the format for screen displays (for forms and reports) and select the format for printed reports.

To create a database from a template, follow these steps:

1. In the Access window, open the New File task pane: Select File , New . In the Templates area of the New File task pane, click the On My Computer link.

2. The Templates dialog box appears. If necessary, click the Databases tab on the dialog box to view the database templates (see Figure 2.3).

   Figure 2.3. Access provides several database templates.

   

3. Click the database template you want to use (for example, the Contact Management template) and then click OK . The File New Database dialog box appears (refer to Figure 2.1).

4. Specify a location for the database using the Save In drop-down list, type a name for the database, and then click Create to continue. A new database file is created, and then the Database Wizard associated with the template starts. For example, if you chose the Contact Management template, the wizard appears and explains the type of information that the database holds.

5. To move past the wizard's opening screen, click Next . On the next screen, a list of the tables that will be created appears (see Figure 2.4). The tables in the database are listed on the left of the screen and the selected table's fields appear on the right.

   Figure 2.4. You can examine and deselect (or select) the fields that will be contained in each table.

   

6. Select a table to examine its fields. If you do not want to include a field in the table, clear the check box next to the field name. Optional fields are also listed for each field and are shown in italics. To include an optional field, click it to place a check mark next to it. When you have finished viewing the tables and their fields, click Next to continue.

   

   Be Careful Deselecting Fields! Because you are stuck with the tables that the Database Wizard creates, you must be very careful removing fields from the tables. This is especially true of fields that uniquely identify the records in a table, such as Contact ID. These fields are often used to relate the tables in the database. You might want to leave all the fields alone initially when you use the wizard.

   
   

7. The next screen asks you to select the screen display style you want to use. This affects how forms appear on the screen. Click a display style in the list to preview the style; after selecting the style you want to use, click Next .

8. On the next screen, the wizard asks you to choose a visual style for your printed reports. Click a report style and examine the preview of it. When you decide on a style, click it, and then click Next .

   

   Report Background The colored backgrounds used for some report styles look nice onscreen, but they don't print well on a black-and-white printer. Unless you have access to a color printer, stick to plain backgrounds for the best report printouts.

   
   

9. On the next wizard screen, you are asked to provide a title for the database. This title appears on reports and can be different from the filename. Enter a title as shown in Figure 2.5.

   Figure 2.5. Enter a title for the database, and as an option, choose a graphic to use for a logo.

   

10. (Optional) To include a picture on your forms and reports (for example, your company's logo), click the Yes, I'd Like to Include a Picture check box. Then click the Picture button, choose a picture file from your hard drive (or other source), and click OK to return to the wizard.

11. Click Next to continue. You are taken to the last wizard screen. On this screen there is a checkbox that says "Yes, start the database." Make sure that this is selected so that the database will open when you complete the process. Click Finish to open the new database. The wizard goes to work creating your database and its database objects.

When the wizard has finished creating the database, the database's Main Switchboard window appears (see Figure 2.6). The Main Switchboard opens automatically whenever you open the database.

Figure 2.6. The Switchboard window is a database navigation tool provided by the Database Wizard.

All the databases created using one of the Access templates (other than the Blank Database template) include a Main Switchboard. The Switchboard is actually a form with some programming built into it. It enables you to perform common tasks related to database management by clicking a button. It is very useful when a person is unfamiliar with how to manipulate the various objects in a database.

For example, to enter or view contacts in the database shown in Figure 2.6, you would click Enter/View Contacts . This action opens a form (which is used to view and edit data into a database table) that allows you to view and enter contact information. If you click the Preview Reports button, a second Switchboard opens and you are provided with a list of ready-made reports that are available for you to view. Again, these reports were created by virtue of the fact that you used a template to create your new database.

Using the Main Switchboard for a database is a quick and straightforward way of quickly getting data into a database and taking advantage of a number of ready-made objects that were created for you. You will find, however, that as you become more familiar with Access, you will probably want to work with your database objects directly (such as tables, forms, and reports) and will no longer use the Main Switchboard. To close the Switchboard, click its Close ( X ) button.

I Hate That Switchboard! To prevent the Switchboard from opening when you open the database, choose Tools , Startup . In the Startup dialog box, select the Display Form/Page drop-down list and select [None] . Click OK .

After you close the Switchboard window, you will find that the database window has been minimized in the Access workspace. Just double-click its title bar (at the bottom-left corner of the screen) to open it. To see the tables that the wizard created, click the Tables object type. Click the other object types (such as forms) to see the other objects that were created by the wizard.

The tables that the wizard creates are, of course, empty. After you fill them with data (either inputting the data directly into the table or using a form), you will be able to run queries and create reports.