Chapter 2. What Every Developer Needs to Know About Databases and Tables

IN THIS CHAPTER

• Why This Chapter Is Important
• Creating a New Database
• Building a New Table
• Selecting the Appropriate Field Type for Your Data
• Working with Field Properties
• Using the All-Important Primary Key
• Working with the Lookup Feature
• Working with Table Properties
• Using Indexes to Improve Performance
• Using Access Tables with the Internet
• Viewing Object Dependencies
• Examining Database Specifications and Limitations
• Examining Table Specifications and Limitations
• Practical Examples: Designing the Tables Needed for a Computer Consulting Firm’s Time and Billing Application

Why This Chapter Is Important

You might find it is useful to think of table design as similar to the process of building a foundation for your house. Just as a house with a faulty foundation will fall over, an application with a poor table design will be difficult to build, maintain, and use. This chapter covers all the ins and outs of table design in Access 2007. After reading this chapter, you will be ready to build the other components of your application, knowing that the tables you design provide the application with a strong foundation.

Creating a New Database

In generic terms, a database stores a collection of information. Access databases are composed of tables, queries, forms, reports, data access pages, macros, and modules. Each table within a database should contain information about a particular subject. You use queries to extract specific information from one or more tables. The forms and reports provide a means of displaying your data. Finally, macros and modules allow you to build an integrated application.

When you are building an Access application, the first step you must take is to perform the necessary analysis and design steps. The section in Chapter 1 titled “How Do I Get Started Developing an Access Application?” covers these initial steps. After you have a design document in place, you are ready to build the Access database. You can complete this process by basing your database on a template or by building the database yourself from the ground up. The text that follows covers both of these options.

Creating a Database Using a Template

Getting started working with Microsoft Access is easy using the new database templates. Each template is a different type of application, complete with the necessary tables, relationships, queries, forms, reports, and macros. In addition to the predefined templates that ship with Microsoft Office Access 2007, templates are also available on Microsoft Office Online. There, you can download the latest revisions to existing templates, as well as any new templates that Microsoft has created. The following categories of templates are available (see Figure 2.1):

• Business
• Education
• Personal
• Sample

Figure 2.1. You can select the appropriate template category.

Building a Database Based on a Template

Here are the steps you take to build a new database based on a template:

1. Click the Microsoft Office button and select New. Your screen should appear as in Figure 2.1.
2. Click to select the category of template that you want to create. For example, in Figure 2.2, Business is selected. All the appropriate templates appear.

   Figure 2.2. When you select a category of template, the appropriate templates appear.

   

3. Click to select the specific template that you want to use. Sales Pipeline is selected in Figure 2.3.

   Figure 2.3. The Sales Pipeline template is available under the Business templates.

   

4. Select the name and location of the new database. Notice that the database will have the new file format (.accdb).
5. Click Download if the database is available on the Internet or click Create if the template is available locally.
6. If you click Download, Access will download the template.
7. The new database appears, as shown in Figure 2.4.

   Figure 2.4. The new database appears with the Open Opportunities List open.

   

8. You can now begin working with the database just as you would work with any database.

Creating a Database from Scratch

When none of the available databases that the templates generate give you what you need, you will have to create your own database. To create a new database from scratch, follow these steps:

1. Click the Microsoft Office button and select New.
2. Enter a filename for the new database in the File Name box on the right side of the screen.
3. Click the Browse icon to select a drive or folder where you will place the database.
4. Click OK to close the browse window.
5. Click the Create button.

Access creates a new blank database.

Database filenames have the following rules:

• Database names can contain up to 255 characters.
• Database names can contain spaces, but you should avoid special characters such as asterisks, semicolons, commas, and so on.
• Access will assign the extension .accdb to the databases that you create.

Building a New Table

You can add a new table to an Access 2007 database in several ways: by building the table from a spreadsheet-like format, designing the table from scratch, using a table template, importing the table from another source, or linking to an external table. This chapter discusses the process of building a table using a spreadsheet-like format, designing a table from scratch, and using a table template; importing and linking are covered extensively throughout this book.

Designing a Table from Scratch

Designing tables from scratch offers flexibility and encourages good design principles. This approach is almost always the best choice when you are creating a custom business solution. To design a table from scratch, click to select the Create tab and then select Table Design. The Table Design view window, pictured in Figure 2.5, appears. Follow these steps:

1. Define each field in the table by typing its name in the Field Name column.
2. Tab to the Data Type column. Select the default field type, which is Text, or use the drop-down combo box to select another field type. You can find details on which field type is appropriate for your data in the “Selecting the Appropriate Field Type for Your Data” section of this chapter. If you use the Field Builder, it sets a data type value for you that you can modify.
3. Tab to the Description column. What you type in this column appears on the status bar when the user is entering data into the field. This column is also great for documenting what data is actually stored in the field.
4. Continue entering fields. If you need to insert a field between two existing fields, click the Insert Rows button on the ribbon. Access inserts the new field above the field you were on. To delete a field, select it and click the Delete Rows button.
5. To save your work, click the Save tool on the Quick Access toolbar. The Save As dialog box, shown in Figure 2.6, appears. Enter a table name and click OK. A dialog box appears, recommending that you establish a primary key. Every table should have a primary key. The section of this chapter titled “Using the All-Important Primary Key” discusses the details of primary keys.

   Figure 2.5. The Table Design view window enables you to create a table from scratch.

   

   Figure 2.6. Use the Save As dialog box to name a table.

   

The naming conventions for table names are similar to those for field names, except that the standard for table names is that they should begin with the tag tbl. Chapter 1, “Access as a Development Tool,” and Appendix A, “Naming Conventions,” (available for download at www.samspublishing.com) cover the details of naming conventions.

Field names can be up to 64 characters long. For practical reasons, you should try to limit them to 10–15 characters—enough to describe the field without making the name difficult to type.

Field names can include any combination of letters, numbers, spaces, and other characters, excluding periods, exclamation points, accents, and brackets. I recommend that you stick to letters. Spaces in field names can be inconvenient when you’re building queries, modules, and other database objects. Don’t be concerned that your users will see the field names without the spaces. The Caption property of a field, discussed later in this chapter, allows you to designate the text that Access displays for your users.

Field names cannot begin with leading spaces. As mentioned, field names shouldn’t contain spaces, so this convention shouldn’t be a problem. Field names also cannot include ASCII control characters (ASCII values 0 through 31).

Try not to duplicate property names, keywords, function names, or the names of other Access objects when naming your fields. Although your code might work in some circumstances, you’ll get unpredictable results in others.

To make a potential move to the client/server platform as painless as possible, you should be aware that not all field types are supported by every back-end database. Furthermore, most back-end databases impose stricter limits than Access does on the length of field names and the characters that are valid in field names. To reduce the number of problems you’ll encounter if you migrate your tables to a back-end database server, you should consider these issues when you’re naming the fields in your Access tables.

Adding descriptions to your table, query, form, report, macro, and module objects goes a long way toward making your application self-documenting. This information helps you, or anyone who modifies your application, to perform any required maintenance on the application’s objects. Chapter 29, “Documenting Your Application,” covers the details of documenting your application.

Selecting the Appropriate Field Type for Your Data

The data type you select for each field can greatly affect the performance and functionality of your application. Several factors can influence your choice of data type for each field in your table:

• The type of data that’s stored in the field
• Whether the field’s contents need to be included in calculations
• Whether you need to sort the data in the field
• The way you want to sort the data in the field
• How important storage space is to you

The type of data you need to store in a field has the biggest influence on which data type you select. For example, if you need to store numbers beginning with leading zeros, you can’t select a Number field because Access ignores leading zeros entered into a Number field. This rule affects data such as ZIP codes (some begin with leading zeros) and department codes.

If the contents of a field need to be included in calculations, you must select a Number or Currency data type. You can’t perform calculations on the contents of fields defined with the other data types. The only exception to this rule is the Date field, which can be included in date/time calculations.

You also must consider whether you will sort or index the data in a field. You cannot sort by OLE, Attachment, and Hyperlink fields, so don’t select these field types if you must sort or index the data in the field. Furthermore, you must think about the way you want to sort the data. For example, in a Text field, Access would sort a set of numbers in the order of their left most character, then the second character from the left, and so on (that is, 1, 10, 100, 2, 20, 200) because Access sorts data in the Text field in a standard ASCII sequence. On the other hand, Access would sort the numbers in a Number or Currency field in ascending value order (that is, 1, 2, 10, 20, 100, 200). You might think you would never want data sorted in a standard ASCII sequence, but sometimes it makes sense to sort certain information, such as department codes, in this fashion. Access 2007 enables you to sort or group based on a Memo field, but it performs the sorting or grouping based only on the first 255 characters. Finally, you should consider how important disk space is to you. Each field type takes up a different amount of storage space on your hard disk, which could be a factor when you’re selecting a data type for a field.

Ten field types are available in Access: Text, Memo, Number, Date/Time, Currency, AutoNumber, Yes/No, OLE Object, Attachment, and Hyperlink. Table 2.1 summarizes the appropriate uses for each field type and the amount of storage space each type needs.

Table 2.1. Appropriate Uses and Storage Space for Access Field Types

The most difficult part of selecting a field type is in knowing which type is best in each situation. The following detailed descriptions of each field type and when you should use them should help you with this process.

Text Fields: The Most Common Field Type

Most fields are Text fields. Many developers don’t realize that it’s best to use Text fields for any numbers not used in calculations. Examples are phone numbers, part numbers, and ZIP codes. Although the default size for a Text field is 50 characters, you can store up to 255 characters in a Text field. Because Access allocates disk space dynamically, a large field size doesn’t use hard disk space, but you can improve performance if you allocate the smallest field size possible. You use the FieldSize property to control the maximum number of characters allowed in a Text field.

Memo Fields: For Those Long Notes and Comments

Memo fields can store up to 65,536 characters of text, which can hold up to 16 pages of text for each record. Memo fields are excellent for any types of notes you want to store with table data. Remember, you can sort by a Memo field under Access 2007.

Number Fields: For When You Need to Calculate++

You use Number fields to store data that you must include in calculations. If you must include currency amounts in calculations, or if your calculations require the highest degree of accuracy, you should use a Currency field rather than a Number field. The Number field is actually several types of fields in one because Access 2007 offers seven sizes of numeric fields. Byte can store integers from 0–255, Integer can hold whole numbers from -32768 through 32767, and Long Integer can hold whole numbers ranging from less than -2 billion to just over 2 billion. Although all three of these sizes offer excellent performance, each type requires an increasingly larger amount of storage space. Two of the other numeric field sizes, Single and Double, offer floating decimal points and, therefore, much slower performance. Single can hold fractional numbers to seven significant digits; Double extends the precision to 14 significant digits. Decimal is a numeric data type introduced with Access 2002. The Decimal data type allows storage of very large numbers and provides decimal precision up to 28 digits! The final size, Replication ID, supplies a unique identifier required by the data synchronization process (available with the .MDB file format).

Date/Time Fields: Tracking When Things Happened

You use the Date/Time field type to store valid dates and times. Date/Time fields allow you to perform date calculations and make sure dates and times are always sorted properly. Access actually stores the date or time internally as an 8-byte floating-point number. It represents time as a fraction of a day.

Currency Fields: Storing Money

The Currency field type is a special type of number field you use when you are storing currency values in a table. Currency fields prevent rounding off data during calculations. They hold 15 digits of whole dollars, plus accuracy to the hundredths of a cent. Although very accurate, this type of field is quite slow to process.

AutoNumber Fields: For Unique Record Identifiers

Access automatically generates AutoNumber field values when the user adds a record. In earlier versions of Access, counter values had to be sequential. The AutoNumber field type in Access 2007 can be either sequential or random. The random assignment is useful when several users are adding records offline because it’s unlikely that Access will assign the same random value to two records. A special type of AutoNumber field is a Replication ID. This randomly produced unique number helps with the replication process (available with the .MDB file format) by generating unique identifiers used to synchronize database replicas.

You should note a few important points about sequential AutoNumber fields. If a user deletes a record from a table, its unique number is lost forever. Likewise, if a user adds a record and cancels the action, the unique counter value for that record is lost forever. If this behavior is unacceptable, you can generate your own counter values.

Yes/No Fields: When One of Two Answers Is Correct

You should use Yes/No fields to store a logical true or false. What’s actually stored in the field is -1 for Yes, 0 for No, or Null for no specific choice. The display format for the field determines what the user actually sees (normally Yes/No, True/False, On/Off, or a third option—Null—if you set the TripleState property of the associated control on a form to True). Yes/No fields work efficiently for any data that can have only a true or false value. They not only limit the user to valid choices, but they also take up only one bit of storage space.

OLE Object Fields: The Place to Store Just About Anything

Microsoft designed OLE Object fields to hold data from any object linking and embedding (OLE) server application registered in Windows, including spreadsheets, word processing documents, sound, and video. There are many business uses for OLE Object fields, such as storing resumes, employee reviews, budgets, or videos. However, in many cases, it is more efficient to use a Hyperlink field to store a link to the document rather than store the document itself in an OLE Object field.

Attachment Fields: Storing Several Files in a Single Field

Using the attachment field type, you can store multiple attachments in a single field. Those attachments can even be of various types. For example, you can use an Excel spreadsheet and a Word document in a single field. Attachment fields are meant to replace their predecessor, OLE Object fields. With OLE Object fields, Access stored the bitmap of the object in the Access database. This caused database bloat. Access stores the data in Attachment fields much more efficiently.

There are additional benefits of Attachment fields. For example, Access renders image files and displays the program icon associated with other file types. If a field contains a photo, spreadsheet, and Word document, Access will display the image and will present application icons for the other objects. Access compresses the objects as it stores them, unless those files are compressed natively. Finally, you can manipulate attachments programmatically!

There are also some other things about Attachment fields that you should be aware of. You can attach a maximum of 2GB of data per database, and each attachment must be less than 256MB in size. You must use the Attachments dialog box (see Figure 2.7) to add, edit, and manage attachments, unless you manage them programmatically.

Figure 2.7. You use the Attachments dialog box to manage your attachments.

Hyperlink Fields: Your Link to the Internet

You use Hyperlink fields to store uniform resource locator addresses (URLs), which are links to web pages on the Internet or on an intranet, or Universal Naming Convention paths (UNCs), which are links to a file location path. Access breaks the Hyperlink field type into three parts:

• What the user sees
• The URL or UNC
• A subaddress, such as a range name or bookmark

After the user places an entry in a Hyperlink field, the entry serves as a direct link to the file or page it’s referring to. I cover the Hyperlink field type in more detail later in this chapter, in the section “Using Access Tables with the Internet.”

Working with Field Properties

After you have added fields to your table, you need to customize their properties. Field properties let you control how Access stores data as well as what data the user can enter into the field. The available properties differ depending on which field type you select. You will find the most comprehensive list of properties under the Text field type (see Figure 2.8). The following sections describe each field property.

Figure 2.8. Field properties available for a Text field.

Field Size: Limiting What’s Entered into a Field

The first property is Field Size, available for Text and Number fields only. As mentioned previously, it’s best to set the Field Size property to the smallest value possible. For Number fields, a small size means lower storage requirements and faster performance.

Build a table with the following fields and types:

CompanyID: AutoNumber
CompanyName: Text
State: Text
PhoneNumber: Text
ContactDate: Date/Time
CreditLimit: Currency

1. To set the Field Size property of the State field to two characters, click anywhere in the field and then type 2 in the Field Size property.
2. Switch to Datasheet view. Access prompts you to save the table. Name it tblCustomers. Because you have not assigned a primary key, Access prompts you to do so. When you try to enter data into the State field, notice that you can enter only two characters.

Format: Determining How Data Is Displayed

The second property is Format, available for all but OLE Object fields and Attachment fields. It allows you to specify how Access displays your data. Access lets you select from predefined formats or create your own custom formats. The available formats differ, depending on the field’s data type. For example, with Access you can select from a variety of Date/Time formats, including Short Date (7/7/07); Long Date (Saturday, July 7, 2007); Short Time (7:17); and Long Time (7:17:11AM). The formats for a Currency field include Currency ($1,767.25); Fixed (1767.25); and Standard (1,767.25).

Set the Format property of the ContactDate field to Medium Date. Switch to Datasheet view and enter some dates in different formats, such as 07/04/07 and July 4, 2007. Notice that, no matter how you enter the dates, as soon as you tab away from the field, they appear in the format dd-mmm-yyyy as 04-Jul-07.

Input Mask: Determining What Data Goes into a Field

Another important property is Input Mask, available for Text, Number, Date/Time, and Currency fields. The Format property affects how Access displays data, but the Input Mask property controls what data Access stores in a field. You can use the Input Mask property to control, on a character-by-character basis, what type of character (numeric, alphanumeric, and so on) Access can store and whether Access requires a particular character. The Input Mask Wizard, shown in Figure 2.9, helps you create commonly used input masks for Text and Date fields only. To access the Input Mask Wizard, click the button to the right of the Input Mask field.

Figure 2.9. The Input Mask Wizard helps you enter an input mask.

For example, the input mask 000-00-0000;;_ (converted to 000-00-0000;;_ as soon as you tab away from the property) forces the entry of a valid Social Security number. Everything that precedes the first semicolon designates the actual mask. The zeros force the entry of the digits 0 through 9. The dashes are literals that appear within the control as the user enters data. The character you enter between the first and second semicolon determines whether Access stores the literal characters (the dashes in this case) in the field. If you enter a 0 in this position, Access stores the literal characters in the field; if you enter a 1 or leave this position blank, Access does not store the literal characters. The final position (after the second semicolon) indicates what character Access displays to indicate the space where the user types the next character (in this case, the underscore).

Here’s a more detailed example: In the mask (999") "000-0000;;_, the first backslash causes the character that follows it (the open parenthesis) to display as a literal. The three nines allow the user to enter optional numbers or spaces. Access displays the close parenthesis and space within the quotation marks as literals. The first three zeros require values 0 through 9. Access displays the dash that follows the next backslash as a literal. It then requires that the user enter four additional numbers. The two semicolons have nothing between them, so Access does not store the literal characters in the field. The second semicolon is followed by an underscore, so Access displays an underscore to indicate the space where the user types the next character.

Use the Input Mask Wizard to add a mask for the PhoneNumber field, which you should have set up as a Text field. The steps are as follows:

1. Click anywhere in the PhoneNumber field and then click the Input Mask property.
2. Click the ellipsis to the right of the Input Mask property.
3. Select Phone Number from the list of available masks and choose not to store the literal characters in the field when the wizard asks “How do you want to store the data?”
4. Switch to Datasheet view and enter a phone number. Notice how your cursor skips over the literal characters. Try leaving the area code blank; Access should allow you to do this.
5. Now try to enter a letter in any position. Access should prohibit you from doing this.
6. Try to leave any character from the seven-digit phone number blank. Access shouldn’t let you do this, either.

Caption: A Great Timesaver

The next available property is Caption. The text placed in this property becomes the caption for fields in Datasheet view. Access also uses the contents of the Caption property as the caption for the attached label added to data-bound controls when you add them to forms and reports. The Caption property becomes important whenever you name your fields without spaces. Whatever is in the Caption property overrides the field name for use in Datasheet view, on forms, and on reports.

Default Value: Saving Data Entry Time

Another important property is the Default Value property, used to specify the default value that Access will place in the field when the user adds new records to the table. Default values, which can be either text or expressions, can save the data entry person a lot of time. However, Access in no way uses them to validate what the user enters into a field.

Enter the following default values for the State, ContactDate, and CreditLimit fields:

State: CA

ContactDate: =Date()

CreditLimit: 1000

Switch to Datasheet view and add a new record. Notice that default values appear for the State, ContactDate, and CreditLimit fields. You can override these defaults, if you want.

Validation Rule: Controlling What the User Enters in a Field

The Default Value property suggests a value to the user, but the Validation Rule property actually limits what the user can place in the field. The user cannot violate validation rules; the database engine strictly enforces them. As with the Default Value property, this property can contain either text or a valid Access expression, but you cannot include user-defined functions in the Validation Rule property. You also can’t include references to forms, queries, or tables in the Validation Rule property.

Add the following validation rules to the fields in your table. (Access will place quotation marks around the state abbreviations as soon as you tab away from the property.)

State: In (CA, AZ, NY, MA, UT)

ContactDate: <= Date()

CreditLimit: Between 0 And 5000

1. Switch to Datasheet view. If the table already contains data, when you save your changes, the message shown in Figure 2.10 appears.

   Figure 2.10. The message box asking whether you want to validate existing data.

   

   ---

   Note

   In this example, the expression <= Date() is used to limit the value entered into the field to a date that is on or before the current date. Because the Date() expression always returns the current date, the validation rule applies whether the user is adding a new row or is modifying an existing row.

   ---

   If you select Yes, Access tries to validate all existing data using the new rules. If any errors are found, you’re notified that errors occurred, but you aren’t informed of the offending records (see Figure 2.11). You have to build a query to find all the records violating the new rules.

   Figure 2.11. A warning that all data did not validate successfully.

   

   If you select No, Access doesn’t try to validate your existing data, and you aren’t warned of any problems.

2. After you have entered Datasheet view, try to enter an invalid state in the State field; you should see the message box displayed in Figure 2.12. As you can see, this isn’t the friendliest message, which is why you should create a custom message by using the Validation Text property.

Figure 2.12. The message displayed when a validation rule is violated, and no validation text has been entered.

Validation Text: Providing Error Messages to the User

Use the Validation Text property to specify the error message users see when they violate the validation rule. The Validation Text property must contain text; expressions aren’t valid in this property.

Add the following to the Validation Text properties of the State, ContactDate, and CreditLimit fields:

State: The State Must Be CA, AZ, NY, MA, or UT
ContactDate: The Contact Date Must Be On or Before Today
CreditLimit: The Credit Limit Must Be Between 0 and 5000

Try entering invalid values for each of the three fields and observe the error messages.

Required: Making the User Enter a Value

The Required property is very important: It determines whether you require that a value be entered into a field. This property is useful for foreign key fields, when you want to make sure data is entered into the field. It’s also useful for any field containing information that’s needed for business reasons (company name, for example).

Set the Required property of the CompanyName and PhoneNumber fields to Yes. Switch to Datasheet view and try to add a new record, leaving the CompanyName and PhoneNumber fields blank. Make sure you enter a value for at least one of the other fields in the record. When you try to move off the record, the error message shown in Figure 2.13 appears.

Figure 2.13. A message appears when you leave blank a field that has the Required property set to Yes.

Allow Zero Length: Accommodating Situations with Nonexistent Data

The Allow Zero Length property is similar to the Required property. Use it to determine whether you allow the user to enter a zero-length string (""). A zero-length string isn’t the same as a Null (which represents the absence of an entry); a zero-length string indicates that the data doesn’t exist for that particular field. For example, a foreign employee might not have a Social Security number. When you enter a zero-length string, the data entry person can indicate that the Social Security number doesn’t exist.

Add a new field called ContactName and set its Required property to Yes. Try to add a new record and enter two quotation marks ("") in the ContactName field. You should not get an error message because, in Access 2007, the Allow Zero Length property defaults to Yes. Your zero-length string will appear blank when you move off the field. Return to the Design view of the table. Change the setting for the Allow Zero Length property to No. Go back to Datasheet view and once again enter two quotation marks in the ContactName field. This time you should not be successful. You should get the error message shown in Figure 2.14.

Figure 2.14. The result of entering "" when the Allow Zero Length property is set to No.

Indexed: Speeding Up Searches

You use indexes to improve performance when the user searches a field. Although it’s generally best to include too many indexes rather than too few, indexes do have downsides (see the next Tip). A general rule is to provide indexes for all fields regularly used in searching and sorting, and as criteria for queries.

Set the Indexed property of the CompanyName, ContactName, and State fields to Yes – (Duplicates OK). Click the Indexes button in the Show/Hide group on the Design tab of the ribbon. Your screen should look like the one in Figure 2.15.

Figure 2.15. The Indexes window shows you all the indexes defined for a table.

To create non-primary-key, multifield indexes, you must use the Indexes window. You create an index with one name and more than one field. See Figure 2.15, which shows an index called StateByCredit that’s based on the combination of the CreditLimit and State fields. Notice that only the first field in the index has an index name. The second field, State, appears on the line below the first field but doesn’t have an index name.

Indexes speed up searching, sorting, and grouping data. The downside is that they take up hard disk space and slow down the process of editing, adding, and deleting data. Although the benefits of indexing outweigh the detriments in most cases, you should not index every field in each table. Create indexes only for fields, or combinations of fields, on which the user will search or sort. Do not create indexes for fields that contain highly repetitive data, such as a field that can contain only two different values. Finally, never index Yes/No fields. They are only 1 bit in storage size; furthermore, they apply to the previous rule in that they can take on only one of two values. For these reasons, indexes offer no benefits with Yes/No fields.

Unicode Compression: Compressing Your Data

Another important property is Unicode Compression. The Unicode Compression property applies to Text and Memo fields only. You use this property to designate whether you want the data in the field to be compressed using Unicode compression. Prior to Access 2000, data was stored in the double-byte character set (DBCS) format, which was designed to store character data for certain languages such as Chinese. With Access 2000 and higher, all character data is stored in the Unicode 2-byte representation format. Although this format requires more space for each character (2 bytes, rather than 1 byte), the Unicode Compression property allows the data to be compressed, if possible. If the character set being used allows compression and the Unicode Compression property is set to Yes, the data in the column is stored in a compressed format.

Using the All-Important Primary Key

The most important index in a table is called the Primary Key index; it ensures uniqueness of the fields that make up the index and also gives the table a default order. You must set a primary key for the fields on the “one” side of a one-to-many relationship. To create a Primary Key index, select the fields you want to establish as the primary key and then click the Primary Key button on the ribbon.

Figure 2.16 shows the tblCustomers table with a Primary Key index based on the CompanyID field. Notice that the Index Name of the field designated as the primary key of the table is called PrimaryKey. Note that the Primary and Unique properties for this index are both set to Yes (True).

Figure 2.16. A Primary Key index based on the CompanyID field.

Working with the Lookup Feature

Using the Lookup Wizard, you can instruct a field to look up its values in another table or query or from a fixed list of values. You can also display the list of valid values in a combo or list box. A lookup is generally created from the foreign key (the “many” side) to the primary key (the “one” side) of a one-to-many relationship.

You can invoke the Lookup Wizard by selecting Lookup Wizard from the list of data types for the field. The first wizard dialog box asks whether you want to look up the values in a table or query or whether you want to input the values (see Figure 2.17). I recommend that you always look up the values in a table or query; this makes your application easier to maintain. The second wizard dialog box asks you to indicate the table or query used to look up the values (see Figure 2.18). Select a table or query and click Next to open the third wizard dialog box. This step of the Lookup Wizard asks you which field in the table or query will be used for the lookup (see Figure 2.19). The fourth step of the Lookup Wizard asks you the sort order you want for your list. The fifth step, shown in Figure 2.20, gives you the opportunity to control the width of the columns in your combo or list box.

Figure 2.17. The first step of the Lookup Wizard asks you for the source of the values.

Figure 2.18. In the second step of the Lookup Wizard, you select the table or query whose data will appear in the drop-down.

Figure 2.19. In the third step of the Lookup Wizard, you designate the field that Access will use for the lookup.

Figure 2.20. In the fifth step of the Lookup Wizard, you can adjust the column widths.

Finally, the wizard lets you specify a title for your lookup column. When you click Finish, Access will prompt you to save the table, and the wizard will fill in all the appropriate properties; they appear on the Lookup tab of the field (see Figure 2.21). The Display Control property is set to Combo Box, indicating that the valid values will be displayed in a combo box. This occurs whether the user is in Datasheet view or in a form. The Row Source Type indicates that the source for the combo box is a table or query, and the Row Source shows the actual SQL Select statement used to populate the combo box. Other properties show the column in the combo box that is bound to data, the number of columns in the combo box, the width of the combo box, and the width of each column in the combo box. These properties are covered in more detail in Chapter 5, “What Every Developer Needs to Know About Forms.” You can modify the SQL statement for the combo box later, if necessary.

Figure 2.21. The field properties set by the Lookup Wizard.

Working with Table Properties

In addition to field properties, you can specify properties that apply to a table as a whole. To access the table properties, click the Property Sheet button on the ribbon while in a table’s Design view. The available table properties are shown in Figure 2.22. The Description property is used mainly for documentation purposes. The Default View property designates the view in which the table appears when the user first opens it. The Validation Rule property specifies validations that must occur at a record level, instead of a field level. For example, credit limits might differ depending on what state a customer is in. In that case, what’s entered in one field depends on the value in another field. When you enter a table-level validation rule, it doesn’t matter in what order the user enters the data. A table-level validation rule ensures that the proper dependency between fields is enforced. The validation rule might look something like this:

Figure 2.22. Viewing the available table properties.

This validation rule requires a credit limit of $2,500 or less for applicants in California and New York and a limit of $3,500 or less for applicants in Massachusetts and Arizona, but it doesn’t specify a credit limit for residents of any other states. Table-level validation rules can’t be in conflict with field-level validation rules.

The Validation Text property determines the message that appears when the user violates the validation rule. If this property is left blank, a default message appears.

You use the Filter property to indicate a subset of records that appear in a datasheet, form, or query. You use the Order By property to specify a default order for the records. The Filter and Order By properties aren’t generally applied as properties of a table.

The Subdatasheet Name property identifies the name of a table used as a drill-down. If this property is set to Auto, the drill-down table is automatically detected based on relationships established in the database. The Link Child Fields and Link Master Fields properties are implemented to designate the fields that are used to link the current table with the table specified in the Subdatasheet Name property. These properties should be left blank when Auto is selected for the Subdatasheet Name. You use the Subdatasheet Height property to specify the maximum height of the subdatasheet and the Subdatasheet Expanded property to designate whether the subdatasheet is automatically displayed in an expanded state.

The Orientation property determines the layout direction for the table when it is displayed. The default setting for USA English is obviously Left-to-Right. This property is language-specific, and the Right-to-Left setting is available only if you are using a language version of Microsoft Access that supports right-to-left language displays. Arabic and Hebrew are examples of right-to-left languages. By installing the Microsoft Office Multilanguage Pack and the Microsoft Office Proofing Tools for a specific language, and by enabling the specific right-to-left language under the Microsoft Office Language Settings, you can also turn on right-to-left support.

Using Indexes to Improve Performance

As previously mentioned, indexes can help you improve your application’s performance. You should create indexes on any fields you sort, group, join, or set criteria for, unless those fields contain highly repetitive data. Queries can greatly benefit from indexes, especially when indexes are created for fields included in your criteria, fields used to order the query, and fields used to join two tables that are not permanently related but are joined in a query. In fact, you should always create indexes for fields on both sides of a join. If your users are using the Find dialog box, indexes can help reduce the search time. Remember, the downsides to indexes are the disk space they require and the amount of time it takes to update them when adding, deleting, and updating records. You should always perform benchmarks with your own applications, but you will probably find indexes helpful in many situations.

Using Access Tables with the Internet

Microsoft has made it easier to develop Internet-aware applications by including the Hyperlink field type and by allowing users to save table data as HTML. The Hyperlink field type lets your users easily store UNC or URL addresses within their tables. The ability to save table data as HTML makes it easy for you or your users to publish table data on an Internet or intranet site. The sections that follow cover these features.

The Hyperlink Field Type

When you use the Hyperlink field type, your users can store a different UNC or URL address for each record in the table. Although you can type a different UNC or URL address directly into a field, it’s much easier to enter the address by using the Insert Hyperlink dialog box (see Figure 2.23). Here, users can graphically browse hyperlink addresses and subaddresses, and the address is entered automatically when they exit the dialog box. To invoke the Insert Hyperlink dialog box, right-click the Hyperlink field and then select Hyperlink, Edit Hyperlink.

Figure 2.23. With the Insert Hyperlink dialog box, users can select or create a hyperlink object for the field.

The Text to Display text box is used to enter the text the user will see when viewing the field data in Datasheet view, in a form, or on a report. The hyperlink can be to any of the following:

• An existing file or web page
• Another object in the current database
• A new data access page
• An email address

To select an existing file or web page, click the appropriate Link To icon and either type the file or web page name, or select it from the list of Recent Files, Browsed Pages, or the Current Folder. The Browse for File button is used to browse for an existing file, and the Browse the Web button is used to browse for an existing web page.

To link to an object in the current database, click the appropriate Link To icon. Click a plus (+) sign to expand the list of tables, queries, forms, reports, pages, macros, or modules. Then click the database object to which you want to link.

To link to a new data access page that you create, click the appropriate Link To icon. Enter the name of the new page and designate whether you want to edit the new page now or later.

To designate an email address you want to link to, click the appropriate Link To icon. Enter the email address and subject, or select from the list of recently used email addresses.

After all the required information has been entered, the link is established, and the hyperlink is entered in the field. If a UNC was entered, clicking the hyperlink invokes the application associated with the file. The selected file is opened, and the user is placed in the part of the document designated in the subaddress. If a URL is entered, and the user is logged on to the Internet or connected to her company’s intranet, she is taken directly to the designated page. If the user isn’t currently connected to the Internet or an intranet, the Connect To dialog box appears, allowing her to log on to the appropriate network.

Saving Table Data as HTML

Table data can be easily saved as HTML so that it can be published on an Internet or intranet site. You can save a file as HTML by using the File, Export menu item. The steps are as follows:

1. Click within the Navigation Pane to select the table you want to export.
2. Click to select the External Data tab.
3. Click the More drop-down in the Export group.
4. Select HTML Document from the drop-down menu. The Export – HTML Document dialog box appears (see Figure 2.24).

   Figure 2.24. The Export – HTML Document dialog box allows you to select the name and location for the exported file.

   

5. Click Browse to select a name and location for the .htm file. The File Save dialog box appears.
6. Provide a filename and location and then click Save. Access returns you to the Export – HTML Document dialog box.
7. Specify the export options as desired.
8. Click OK. The HTML Output Options dialog box appears. Here, you can select an HTML template and designate the type of encoding that you want to use for the file.
9. Click OK. If you opted to open the destination file after the export operation is complete, the exported document appears in your browser (see Figure 2.25). Figure 2.26 displays the underlying HTML that you can edit using any HTML editor.

   Figure 2.25. Viewing an HTML document in a browser after a table was saved as HTML.

   

   Figure 2.26. Viewing the source HTML for the exported document.

   

10. The final step of the wizard asks if you want to save your export steps. If you do, click the Save Export Steps check box.
11. Click Close to complete the process.

Saving Table Data as XML

Access 2007 allows you to export your data to XML and to import data from XML. Using either code or the Access user interface to export XML data, you can generate data (XML), schema (XSD), and presentation (XSL) files. Although in-depth coverage of Access and XML is included in Alison Balter’s Mastering Access 2002 Enterprise Development, this section provides you with basic information about the import and export processes. To export a table to XML, follow these steps:

1. Select the table you want to export.
2. Click to select the External Data tab.
3. Click the More drop-down in the Export group.
4. Select XML File from the drop-down menu. The Export – XML Document dialog box appears.
5. Click Browse to select a name and location for the .xml file. The File Save dialog box appears.
6. Provide a filename and location and then click Save. Access returns you to the Export – XML Document dialog box.
7. Click OK. The Export XML dialog box appears. Here, you designate whether you want to export data, schema of the data, and the presentation of your data (see Figure 2.27).

   Figure 2.27. The Export XML dialog box allows you to specify what XML documents you want to generate as part of the export process.

   

8. Click OK. Access generates the appropriate files.
9. The final step of the wizard asks if you want to save your export steps. If you do, click the Save Export Steps check box.

Figure 2.28 displays the underlying XML that you can edit using any XML editor.

Figure 2.28. Viewing the XML generated when you save a table as XML.

Just as you can export data to XML, you can import XML data into Access. To import XML data into an Access table, follow these steps:

1. Click to select the External Data tab.
2. Click the Import XML file button in the Import group.
3. Click Browse to select the file you want to import and click Import. The File Open dialog box appears.
4. Select the file you want to import and click Open. Access returns you to the Get External Data – XML File dialog box.
5. Click OK to perform the import. The Import XML dialog box appears (see Figure 2.29).

   Figure 2.29. The Import XML dialog box allows you to designate options used for the import process.

   

6. Indicate whether you want to import the structure only, the structure and data, or append data to existing table(s).
7. Click OK to continue. Access completes the import process.

Viewing Object Dependencies

Sometimes you will need to know what objects depend on a particular table. Here’s how this process works:

1. Click to open the Navigation Pane drop-down and select Tables and Related Views. The first time you perform this task for a database, a dialog box appears, prompting you to update object dependency information for the database (see Figure 2.30). After you click OK, Access updates the dependency information for the database and displays each table along with the objects that depend on it (see Figure 2.31).

   Figure 2.30. The first time you attempt to display object dependencies within a database, Access prompts you to update dependency information for that database.

   

   Figure 2.31. Access displays each table, along with the objects that depend on it.

   

2. By default, the Object Dependencies window shows you the objects that depend on the selected object. You can click the Objects That I Depend On button to view the objects that the selected object depends on.
3. You can click the node of an item to drill down to additional dependencies. Because we have not established relationships between the tables, and this database contains no queries, forms, or reports that are based on the tables in the database, no dependencies yet exist. If you practice this exercise on the Northwind database, you will find the results to be much more interesting.
4. Close the Object Dependencies window when you are finished viewing and working with object dependencies.

Examining Database Specifications and Limitations

Databases have a number of specifications and limitations that you should be aware of. Fortunately, you will generally not find them too restricting. They are listed in Table 2.2.

Table 2.2. Table Specifications and Limitations

Examining Table Specifications and Limitations

Tables have a number of specifications and limitations as well. Although you will generally not find them restricting, you should keep them in mind. They are listed in Table 2.3.

Table 2.3. Table Specifications and Limitations

You also can save table data under the XML format.

Practical Examples: Designing the Tables Needed for a Computer Consulting Firm’s Time and Billing Application

Create a new database and try designing a few of the tables needed by a computer consulting firm’s time and billing application. You will build tblClients and tblProjects tables. The main table for the application, tblClients, will be used to track the key information about each client. The second table, tblProjects, will hold all the key information users need to store for the projects they’re working on for each client. Table 2.4 shows the field names, data types, and sizes for each field in tblClients. You should include indexes for all fields except Notes. Table 2.5 shows the properties that need to be set for these fields. Table 2.6 shows the fields, data types, and sizes for the fields in tblProjects, and Table 2.7 shows the properties that need to be set for these fields. You should include indexes for all fields except ProjectDescription.

Table 2.4. Field Names, Data Types, and Sizes for the Fields in tblClients

Table 2.5. Properties That Need to Be Set for the Fields in tblClients

Table 2.6. Field Names, Data Types, and Sizes for the Fields in tblProjects

Table 2.7. Properties That Need to Be Set for the Fields in tblProjects

The rest of the tables needed by the time and billing application are listed in Appendix B, “Table Structures,” which is downloadable at www.samspublishing.com. The finished table structures can be found in CHAP2.ACCDB. You can find this file, and all files referred to in this book, on the book’s sample code website.

Summary

Tables are the foundation for your application. A poorly designed table structure can render an otherwise well-designed application useless. This chapter began by walking you through several methods for creating tables. It then discussed theoretical issues, such as selecting the correct field type and effectively using field properties. Each property, and its intended use, was discussed in detail. Finally, table properties and indexes were covered. After reading this chapter, you should be ready to harness the many features that the Access table designer has to offer.