Creating a Database

Generally, when you need to create a new database, you'll start Access and select Blank Database from the New File task pane. Enter the name of your database in the File New Database dialog box and click Create to generate the blank database file. Access offers the default database filename db1.mdb , but you should give your database a more meaningful name. For example, if you want to track rental property information, you might name the database Rentals. (Access adds the .mdb extension, so your database will be stored as Rentals.mdb .)

When you enter a name and click the Create button, Access displays the Database window , shown in Figure 17.4. The Database window title bar includes the name of your database ( Rentals in Figure 17.4). As you add to your database, the Database window shows the various components of the database, such as tables.

Figure 17.4. The Database window displays a list of your database objects as you create them.

Tables are not the only items that appear in databases. You will generate database reports and other elements, called objects , as you work on your database file. The Database window lists the names of all database objects at the left of the window. In the right side, you will see a list of tasks that Access is ready to perform on whatever object you click.

A database object is a piece of an Access database. A table is an object, for example. A database report that prints database data is an object. Your data values, however, are not objects.

In most cases, when you create a database, the first task you want to perform is to create the database's first table. The first Database window task, Create Table in Design View, will help you create a new table to hold data.

Understanding Database Objects

As you create your database, you add objects to the Database window's seven object categories. Any database can contain many objects from each category. The following are brief descriptions of seven kinds of Access objects:

• Tables Related data within a database

• Queries Stored instructions that select data from one or more tables for reporting, analysis, and data-management purposes

• Forms Onscreen representations of paper forms that you and others use to enter data into tables

• Reports Printed listings of database data

• Pages (also called Data Access Pages) Internet-ready data table pages that you can view with Internet Explorer 5 from Web pages

• Macros Stored task lists for Access commands

• Modules Programs written in Visual Basic, a powerful (but advanced for nonprogrammers) programming language with which you can automate any database task

As you create your database, you create one or more instances of the database objects. You might create 25 tables and 50 reports, for example. When you want to create, edit, or work with one of the database objects, return to the Database window to do so. The toolbar always contains a Database Window toolbar button that quickly returns you to the Database window. To create a new instance of one of the objects, click on the object and select the appropriate task that works with the object.

Access provides an Outlook-style grouping mechanism at the bottom-left of the Database window. If you find yourself creating many objects within the same database, you might want to group some objects into new groups that you create. To create a new group, right-click a blank area under the Groups label and select New Group, enter a group name, and click OK to add the new group to the existing group list. For simple databases, you'll probably just keep all the objects ungrouped within the Database window.

To Do: Create a Table

You must create tables before you can enter data in a database. The tables hold the data on which the other objects operate .

When you create a table, you follow these general steps:

1. After creating a new database, select the Database window's option called Create Table in Design View and then click Open to create a new table. The Design view window appears (shown in Figure 17.5). You must now describe your table's fields in the Design view dialog box.

   Figure 17.5. Define your table's fields in the Design view window.

   

2. Type a field name, such as First Name or Quantity , for the first field in your database. The names have nothing to do with the data type that you will eventually store in the table's field. The field name enables you to refer to the field as you design your table. Only after you completely design the table do you enter data in the table. The order in which you add fields does not affect the order in which you ultimately enter the table data. Nevertheless, try to add the fields in the general order in which you want to enter the table data.

3. Press Tab and click the drop-down list that appears in the Data Type field to select the field's data type.

4. Press Tab and type a description for the field. Some field names are optional and don't require a description, but the more you document and describe your data, the easier it is to modify your database later.

5. After you enter the first field's name, data type, and optional description, describe the field properties in the lower half of the Design view dialog box. Some fields do not require property settings, but most require some type of setting.

The next section describes in more detail how you set field property values.

Setting Field Properties

The lower half of the Design view contains settings for your field property values. Each field has a data type, as you already know. In addition to describing the field's data type in the Design view's top half, you can further refine the field's description and limitations in the Field Properties section.

You can configure a different set of field property values for each data type. Text fields contain properties related to text data (such as an address or a name), whereas numeric fields contain properties related to numbers (such as decimal positions ).

The field properties appear in the lower half of the screen. A few common field property values that you might want to set as you create your table are as follows :

• Field Size Limits the number of characters the field can hold, thereby limiting subsequent data entry of field data.

• Format Displays a drop-down list with several formats that the field's data type can take.

• Caption Holds a text prompt that Access displays when you enter data into this table's field. If you don't specify a caption, Access uses the field name. Access displays the caption in its status bar when you enter data into the table.

• Default Value Contains the field's default value, which appears when you enter data into this table. The user can enter a value that differs from the default if desired. A common default field value would be your company's state. Many of your vendors will be located in your own state so, if this is the case, your vendor database's State field would include your company's state as the default value.

• Required Holds either Yes or No to determine whether Access requires a value in this field before you can save a table's data record. If you don't want the user leaving a field blank, enter Yes for the Required property.

• Decimal Places Holds the number of default decimal places shown for numbers entered into this field.

Figure 17.6 shows a completed table's Design view. The selected field's (the field with the arrow, or field selector in the left column) property values appear at the bottom of the dialog box. As you enter your own table fields, edit any information that you type incorrectly by clicking the field name, data type, description, or property value, and move the insertion point to the mistake to correct the problem.

Figure 17.6. A complete table definition shows you how the column names, data types, and descriptors appear.

Setting the Key and Saving the Table

After you complete your table's fields, you cannot close the table's Design view without designating a field as the table's primary key field. If you do not designate a key and attempt to close the table, Access warns you that no key exists. Access adds a key field using the AutoNumber format if your data does not contain a key field.

Consider adding your own key field for tables that you access often. The key field enables you to search the table more quickly. The key might be a Social Security number, a phone number, or some other code (such as a unique inventory code or customer number) that is unique for each record in the table. The Key field also prevents duplicate entries.

To specify a key field, select the field by clicking the row selector at the left of the field name and then clicking the Primary Key toolbar button (the button with the icon of the key). Access adds a small key icon to the left of the record, indicating the table's key field.

After you add the key field, save the table by clicking the Save button on the toolbar. (If you attempt to close the table before saving it, Access prompts you for a name.) If you don't specify a new name, Access uses Table1 (and Table2 , Table3 , and so on as you create additional tables); however, you should use a more meaningful table name, such as Tenants , so that you can easily identify the table. When you close the table, Access returns you to the Database window where you will see the table in the right pane.

Modifying Table Structures

The beauty of Access is that, unlike some other database programs, you can easily change the structure of your tables even after you add data. Access makes it easy to add and delete fields, as well as change field properties.

Some table-structure changes affect table data. After you add data to a table, for example, you lose columns of data if you delete fields, and you lose some data through truncation if you limit a field's Size property after you've entered data. If you add fields to an existing table, you must add the data for the new fields in every existing record in the table.

To modify a table, switch to the table's Design view. If you have closed the table and returned to the Database window, select the table name (which now appears in the Database window) and click the Database window toolbar's Design button.

Adding Fields

When you return to the table's Design view, you can add a field to the end of your table just by clicking the first empty Field Name box and entering the field information as you did when adding the table's initial fields.

To insert a new field between two other fields, right-click the row to display the shortcut menu and select Insert Rows. Access opens a new field row and enables you to enter the new field information. Figure 17.7 shows a new field being inserted into a table.

Figure 17.7. The new field will go in the empty space.

Deleting Fields

To delete a field, right-click over the field name and select Delete Rows from the shortcut menu that appears.

Use Undo (Ctrl+Z) to reverse an accidental field deletion. Access supports multiple levels of Undo so that you can reverse several recent row deletions by issuing Undo multiple times. Once you save your new table design, however, you will not be able to reverse the row deletions.

Resizing and Rearranging Fields

Drag your mouse pointer to make minor size adjustments to your table (such as the height of each row and the width of columns). Although the Field Size property determines the exact data storage width of each field in your table, the column widths determine how much of a field you can read while entering and editing table data and the table's structure.

At any time during your table design or subsequent data entry, you can drag a column divider left or right to increase or decrease the width of a column displayed. For example, if your field names are short, you might want to shorten the width of the Field Name column by dragging the right edge to the left to make more room for the field's Description. You can also drag a record divider up or down to increase or decrease a record height.

To rearrange the location of a field, drag the field name by dragging its selector (the gray area to the left of the field) to its new location in the table and release the mouse. Access moves the field to the location you select.

After you make changes to a table's design, Access will prompt you to save your changes when you leave the Design view. Always save any changes you want Access to keep. regardless of the physical order in which you entered the fields. Order your fields in whatever way makes the most sense to you.

The order in which you structure a table's fields has little bearing on the table's use. You can report a table's data in any field order that you want

Viewing Table Design and Entering Simple Data

Until now, you have worked exclusively in the Design view of the table, which describes the table's fields, properties, and key. From the Database window, if you double-click a table name (or select the table and click the Open button), Access displays your table with the Datasheet view, the view shown in Figure 17.8. Unlike the Design view, the Datasheet view enables you to enter and edit data in the table. For a new table, only one table row appears and it's blank because the table has no data. If the table contained data, you would see rows from the table with data. Unlike the Design view, you cannot change the table's structure from the Datasheet view.

Figure 17.8. Use the Datasheet view to enter data into your table.

You can easily switch from Datasheet view and Design view by clicking the Access toolbar's View button. As you design a table and add the table's initial data, the two views help you pinpoint design and data problems.

The Datasheet view enables you to work with your table in row and column format, similar to an Excel worksheet. Until you enter data in the table, the Datasheet view shows only one empty record, but as you add to the table, the Datasheet view grows to resemble an Excel worksheet with cells that represent each table's field.

Although Access offers several ways to enter data into a table, the Datasheet view is the fastest and simplest if you understand records and fields. The Datasheet view is not fancy, however, and some users need more help when entering data. If you build a database application for a video store's inventory, for example, the clerk should not be adjusting the inventory table directly within the Datasheet view when a customer rents a tape. You learn in Hour 18, "Entering and Displaying Access 2003 Data," how to design data-entry forms that walk the user through the data-entry process for specific scenarios so that the user does not inadvertently change information in the wrong record.

Although the Datasheet view is not fancy, it enables you to quickly see your table's design results and to enter data. If you cannot read a full field name, drag the field separator left or right to increase or decrease the field width shown on the screen.

Most of the Database window objects offer two views: a Design view and another view that displays the final object, such as the table's Datasheet view and the form's Form view.

You can design a table from the Datasheet view, but you make more work for yourself if you do. The default Datasheet view field names are Field1 , Field2 , and so on until you right-click the names and change them. You cannot set specific field properties from the Datasheet view because Access assumes that all fields are text. In addition, you cannot specify a primary key field in the Datasheet view. Use the Datasheet view for simple data entry and for testing your table's design. Skip the Design view phase only if you want to create an extremely simple, all-text table.

So that you can practice manipulating a database and all its objects, Access comes with a comprehensive database called the Northwind Traders, Inc., Database , which you can study to learn about advanced database operations. Use File, Open and locate the database in your Office 2003 folder to open and work with the sample database.

Always quit Access and shut down Windows before turning off your computer; otherwise , you might lose data that didn't get saved in your database tables.