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.
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:
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:
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 :
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.
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.
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.
To delete a field, right-click over the field name and select Delete Rows from the shortcut menu that appears.
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.
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.
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.
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.