Part I: Access Building Blocks
56
Changing a Table Design
Even the best planned table will require changes from time to time. You might find that you want
to add another field, remove a field, change a field name or data type, or simply rearrange the
order of the field names.Although a table’s design can be changed at any time, special considerations must be given to
tables containing data. Be careful of making changes that damage data in the table, such as making
text fields smaller or changing the Field Size property of Number fields. You can always add
new fields to a table without problems, but changing existing fields might be an issue. And, with
very few exceptions, it’s almost always a bad idea to change a field’s name after a table has been put
into use in an application.Inserting a new field
To insert a new field, in the Table Design window, place your cursor on an existing field and right-
click on a field in the table’s design surface and select Insert ➪ Rows, or click the Insert Rows but-
ton in the ribbon. A new row is added to the table, and existing fields are pushed down. You can
then enter a new field definition. Inserting a field does not disturb other fields or existing data. If
you have queries, forms, or reports that use the table, you might need to add the field to those
objects as well.Deleting a field
There are three ways to delete a field. While the table is in Design view:l Select the field by clicking the row selector and pressing Delete.l (^) Right-click on the selected field and choose Delete Rows from the shortcut menu.
l Select the field and click the Delete Rows button from the Tools group on the ribbon’s
Design tab.
When you delete a field containing data, you’ll see a warning that you’ll lose data in the table for
the selected field. If the table contains data, make sure that you want to eliminate the data for that
field (column). You’ll also have to delete the same field from queries, forms, reports, macros, and
VBA code that use the field name.
Tip
When you delete a field, you can immediately click the Undo button and return the field to the table. But you
must undo changes before you save the table’s definition or make any other changes to the table’s design.
Tip
If you try to delete a field that’s part of a relationship (a primary or secondary key field), Access informs you
that you can’t delete the field until you remove the relationship in the Relationships window.