Making Changes to Tables and Fields

As with most other things in life, tables sometimes need to change. You might have to modify field names, change data types, adjust field sizes, or add fields. Before you make any significant or extensive changes, be sure to back up your original tables.

TIP

Access 2003 makes it easy to back up your database. In the Database window, choose File, Backup Database. In the Save Backup As dialog box, navigate to a convenient folder and save the file.

Modifying Field Names

In earlier versions of Access, the surest way to wreak havoc on your database was to change a field name. For example, you might have edited the name of a field that was in one or more queries. Access would then be baffled about where to retrieve the data for that field because the field as entered in the query no longer existed.

The latest versions of Access adapt somewhat better to field name changes. Beginning in Access 2000, the Name AutoCorrect feature for updating field names has been available from Tools, Options, General tab. In the Name AutoCorrect section, the Track Name AutoCorrect info and Perform Name AutoCorrect options must be checked.

Nevertheless, I still am reluctant to change field names, regardless of whether this option is implemented. (It is turned on by default.) First, the options don't change field names throughout your database in all instances. Second, according to some Access experts, implementing the options could result in reduced performance in certain situations. My personal experience with Name AutoCorrect has been mixed: Sometimes field names I thought would be updated were not.

As stated previously, you can always use the Caption property to display a more easily understandable name and leave the field name as it is.

Changing Data Types

Anyone contemplating a change in a field's data type should invoke the entreaty of theologian Reinhold Niebuhr: "God, give us the grace to accept with serenity the things that cannot be changed, courage to change the things which should be changed, and the wisdom to know the difference."

Some changes in data type should cause no unease. For the most part, you can change a field with a Number or Currency data type to a Text data type with little hassle. Yes/No and Date/Time fields can also easily be changed to Text. You can also change from Text to Memo, and vice versa (although any text in the Memo field that exceeds the maximum 255 characters for a Text field will be gone).

Conversions from Text to Number, however, are more ticklish. Values with letters, which are invalid in a Number field, will be entirely deleted. Other unpleasant surprises might arise: a value of 786- in a Text field becomes -786 (a negative number) in a Number field.

If you change from Text to Date, be sure the values are in a valid Date format, or they, too, will disappear. The same goes for changing from a Text to a Yes/No data type. For successful conversion, the values must be entered as either Yes/No, TRue/False, On/Off, or -1 for Yes and 0 for No.

Adjusting Field Sizes

Problems with changing field sizes center on losing data. If you change the Field Size of a Text field from, say, 20 to 10, values with more than 10 characters will be truncatedfor example, Indian elephant becomes Indian ele.

With Number fields, the major problem is changing from types that enable fractions (say, Single) to integer data types (say, Long Integer). Also note that the range of the Integer field size is from 32,768 to +32,767. If you change to Integer and have numbers outside this range, Access changes them to nulls.

Inserting Fields

You might want to insert a field above another field in the table. Click in the row above which you want a field to be inserted and choose Insert, Rows. You can insert multiple rows by highlighting several rows and right-clicking the last row. The number of rows selected is inserted.

Q&A

Q1:

Wait a minute. Didn't you tell us in Chapter 2 that one of the cornerstones of a relational database was that it didn't matter where a field was located? Didn't you say that no field or record came before or after any other field or record? So why don't you just add the field in the first empty row available?

A1:

Theoretically, what I wrote was correct. And in Datasheet view, you can move columns and rows with impunity. But as a practical matter, the default field order is the field order shown in Table Design view. So say you create a form based on the table. The initial order of the controls will be the same as the field order in Table Design view. You can change the field order afterward, but it's easier if it's right the first time. Moreover, if you do need to modify a field property in Table Design view, it's easier to locate a City field, for example, when it comes before State instead of an unrelated field such as Middle Name.