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 TableIf 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:
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 SizesYou 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
For details on propagating field property value changes to dependent database objects, see "Working with Object Dependencies and Access Smart Tags," p. 200. Numeric FieldsChanging 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 FieldsYou 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 TypesJet 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 TablesAdding 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:
|