Let's turn now to the specifics of creating Access tables for storing your data. Basically five ways exist for creating a new Access table:
Use the Table Wizard
Enter data directly into a datasheet
Import data or link to an external source
Use a Make Table query
Build the table in Design view
I will focus on the last choice of starting from scratch in Design view, which is usually preferred. Importing and linking are discussed in Chapter 13, “Importing and Exporting,” and make-table queries are described in Chapter 9, “Queries, Part II.” That leaves using the Table Wizard and entering data into a datasheet, both of which I quickly dispose of now.
The Table Wizard is of some use, but not for its intended purpose of creating a table. In the Database window of any database, click Tables and double-click Create Table by Using Wizard. The first dialog box of the wizard includes sample fields for both business and personal tables. As you move from table to table in the Sample Tables pane, you can see the possible fields for each. This is one check for making sure you've included all the fields you need.
The Table Wizard gives you some ideas for fields and field names. But unlike some other wizards that truly make object creation simpler and more efficient, I think the Table Wizard offers relatively few benefits. Tables are the foundation of your database, so you should spend the time to build them from scratch rather than use shortcuts.
To use the direct-entry method, you double-click Create Table by Entering Data in the Tables section of the Database window. In the direct-entry method, you enter column names (which become the field names) and a few rows of sample data into a datasheet. For example, let's say you want to create a table for investments. You might enter the following column headings and data for the first few fields:
Security Name | Security Symbol | Shares Owned |
---|---|---|
Boeing | BA | 700 |
DuPont | DD | 550 |
Ford Motor | F | 1,600 |
If you have entered the values with some consistency, Access will make a reasonable stab at creating a table with fields of the appropriate data type (in this case, Text in the first two columns and Number in the third) and display format.
There seems little to gain by this process because Access can easily suggest the wrong data types. In addition, by using column headings to name fields, the direct-entry method mingles field names and captions in a way that is disconcerting. I mention this issue again in the “Captions” section.
Table creation is such a vital component of making an Access database, so you usually want to create fields and specify field properties one by one in the Design view of a table. Part of this process is reviewing all field property settings entered by default, to make certain they are the best choices for your fields.
At this point, you have already designed your database; you know the tables you are going to include, the primary and foreign keys in each, and most or all of the fields and their data types. Here is the basic procedure for creating tables:
1. | In the Database window, click the Tables button and double-click Create Table in Design View. |
2. | Enter a field name in the Field Name column. |
3. | If applicable, designate the field (or fields) as the primary key. |
4. | Select the data type in the Data Type column. |
5. | Provide a description. |
6. | Assign field properties in the Field Properties pane. |
7. | Save the table with an appropriate name. As you've seen, I'm using the Leszynski naming convention, as described in the sidebar. Thus, the tables in the sample databases always have the prefix tbl, as in tblSuppliers, tblCountries, and so on. If you find this baffling and confusing, don't use these prefixes. |
8. | Enter additional fields as necessary. |
9. |
All of the steps except the first are described in detail in the rest of this chapter.
3.144.237.77