Altering Fields and Relationships

When you're designing a database, you often discover that you must alter the original choices you made for the sequence of fields in a table, data types, or relationships between tables. One reason for adding substantial numbers of records to tables during the testing process is to discover any necessary changes before putting the database into daily use.

You can change formats, validation rules and text, lengths of Text fields, and other minor items in the table by changing to design mode, selecting the field to modify, and making the changes in the property boxes. Changing data types can cause a loss of data, however, so be sure to read the later "Changing Field Data Types and Sizes" section before you attempt to make such changes. Changing the data type of a field that participates in a relationship with another table requires that you delete and, if possible, recreate the relationship. Changing relationships between tables is considered a drastic action if you have entered a substantial amount of data, so this subject is also covered later in "Changing Relationships Between Tables."

Note

Access 2000 introduced the Name AutoCorrect feature. Renaming a database object in previous versions required you to search manually through all objects of your database and change all references to the renamed objects. The Name AutoCorrect feature handles the corrections for you; when you open a database object, Access scans and fixes discrepancies. New databases you create in Access 2003, whether in 2000 or 2002 file format, have this feature turned on by default. Databases you convert from previous versions require you to turn on Name AutoCorrect by choosing Tools, Options, and then marking the Track Name AutoCorrect Info and Perform Name AutoCorrect check boxes on the General page of the Options dialog.

Track Name AutoCorrect must be enabled to view object dependencies and enable table field property change propagation, but Perform Name AutoCorrect isn't required.


To learn how field property value changes propagate to dependent database objects, see "Working with Object Dependencies and Access Smart Tags," p. 200.


Rearranging the Sequence of Fields in a Table

If you're typing historical data in Datasheet view, you might find that the sequence of entries isn't optimum. You might, for example, be entering data from a printed form with a top-to-bottom, left-to-right sequence that doesn't correspond to the left-to-right sequence of the corresponding fields in your table. Access makes rearranging the order of fields in tables a matter of dragging and dropping fields where you want them. You can decide whether to make the revised layout temporary or permanent when you close the table.

To rearrange the fields of the HRActions table, follow these steps:

  1. graphics/opening_table_displays.gif Click the View button. Rearranging the sequence of fields is the only table design change you can implement in Access's Datasheet view.

  2. Click the field name button of the field you want to move. This action selects the field name button and all the field's data cells.

  3. Hold down the left mouse button while over the field name button. The mouse pointer turns into the drag-and-drop symbol, and a heavy vertical bar marks the field's leftmost position. Figure 5.33 (top) shows the ScheduledDate field being moved to a position immediately to the left of the EffectiveDate field.

    Figure 5.33. In Datasheet view, you can rearrange the left-to-right sequence of a table's fields without changing their sequence in Design view. These two windows illustrate dragging the Effective(Date) field to the left of the Approved By field.

    graphics/05fig33.gif

  4. Move the vertical bar to the new position for the selected field and release the mouse button. The field assumes the new position shown in Figure 5.33 (bottom).

  5. When you close the HRActions table, you see the familiar Save Changes message box. To make the modification permanent, click Yes; otherwise, click No.

Rearranging the field sequence in Datasheet view doesn't change their order in Design view's fields grid. To reposition fields in Design view, click the select button of the row of the field you want to move and then drag the row vertically to a new location. Changing the position of a table's field doesn't change any of the field's other properties.

Changing Field Data Types and Sizes

You might have to change a field data type as the design of your database develops or if you import tables from another database, a spreadsheet, or a text file. If you import tables, the data type automatically chosen by Access during the importation process probably won't be what you want, especially with Number fields. Chapter 8, "Linking, Importing, and Exporting Data," discusses importing and exporting tables and data from other applications. Another example of altering field properties is changing the number of characters in fixed-length Text fields to accommodate entries that are longer than expected, or converting Text to Memo fields.

Caution

graphics/paste.gifgraphics/copy.gif

Before making changes to the field data types of a table that contains substantial amounts of data, back up the table by copying or exporting it to a backup Access database. If you accidentally lose parts of the data contained in the table (such as decimal fractions) while changing the field data type, you can import the backup table to your current database. Chapter 8 covers the simple and quick process of exporting Jet tables. After creating a backup database file, you can copy a table to Windows Clipboard and then paste the table to the backup database. The later section "Copying and Pasting Tables" discusses Clipboard operations.


For details on propagating field property value changes to dependent database objects, see "Working with Object Dependencies and Access Smart Tags," p. 200.


Numeric Fields

Changing a data type to one that requires more bytes of storage is, in almost all circumstances, safe; you don't sacrifice your data's accuracy. Changing a numeric data type from Byte to Integer to Long Integer to Single and, finally, to Double doesn't affect your data's value because each change, except for Long Integer to Single, requires more bytes of storage for a data value. Changing from Long Integer to Single and Single to Currency involves the same number of bytes and decreases the accuracy of the data only in exceptional circumstances. The exceptions can occur when you are using very high numbers or extremely small decimal fractions, such as in some scientific and engineering calculations.

On the other hand, if you change to a data type with fewer data bytes required to store it, Jet might truncate your data. If you change from a fixed-point format (Currency) or floating-point format (Single or Double) to Byte, Integer, or Long Integer, any decimal fractions in your data are truncated. Truncation means reducing the number of digits in a number to fit the new Field Size property that you choose. If you change a numeric data type from Single to Currency, for example, you might lose your Single data in the fifth, sixth, and seventh decimal places (if any exists) because Single provides as many as seven decimal places and Currency provides only four.

You can't convert any field type to an AutoNumber-type field. You can use the AutoNumber field only as a unique record identifier; the only way you can enter a new value in an AutoNumber field is by appending new records. You can't edit an AutoNumber field. When you delete a record in Access, the AutoNumber values of the higher-numbered records are not reduced by 1.

Text Fields

You can convert Text fields to Memo fields without Jet truncating your text. You can't add indexes to Memo fields, so any index(es) on the converted Text field disappear. Jet won't let Memo fields participate in relationships.

Converting a Memo field to a Text field truncates characters beyond the 255-character limit of Text fields. Similarly, if you convert a variable-length Text field to a fixed-length field, and some records contain character strings that exceed the length you chose, Jet truncates these strings.

Conversion Between Number, Date, and Text Field Data Types

Jet makes many conversions between Number, Date, and Text field data types for you. Conversion from Number or Date to Text field data types does not follow the Format property that you assigned to the original data type. Numbers are converted with the General Number format, and dates use the Short Date format. Jet is intelligent in the methods it uses to convert suitable Text fields to Number data types. For example, it accepts dollar signs, commas, and decimals during the conversion, but ignores trailing spaces. Jet converts dates and times in the following Text formats to internal Date/Time values that you then can format the way you want:

 1/4/2003 10:00 AM 04-Jan-03 January 4 10:00 10:00:00 

Changing Relationships Between Tables

Adding new relationships between tables is a straightforward process, but changing relationships might require you to change data types so that the related fields have the same data type. To change a relationship between two tables, complete the following steps:

  1. Close the tables involved in the relationship.

  2. graphics/window_database.gif If the Database window is not active, click the Database Window button, or choose Window, 1 Database.

  3. graphics/relationships.gif Display the Relationships window by clicking the Relationships button of the toolbar or by choosing Tools, Relationships.

  4. Click the join line that connects to the field whose data type you want to change. When you select the join line, the line becomes darker (wider).

  5. Press Delete to clear the existing relationship. Click Yes when the message box asks you to confirm your deletion.

  6. If you intend to change the data type of a field that constitutes or is a member field of the primary table's primary key, delete all other relationships that exist between the primary table and every other table to which it is related.

  7. Change the data types of the fields in the tables so that the data types match in the new relationships.

  8. Re-create the relationships by using the procedure described earlier in the section "Establishing Relationships Between Tables."



Special Edition Using Microsoft Office Access 2003
Special Edition Using Microsoft Office Access 2003
ISBN: 0789729520
EAN: 2147483647
Year: 2005
Pages: 417

flylib.com © 2008-2017.
If you may any questions please contact us: flylib@qtcs.net