Altering Tables
After you have created and populated a table, you may need to modify the table's design. To do so, use the ALTER TABLE statement. But be forewarned, altering an existing table's structure may cause you to loose some of the data. For example, changing a field's data type can result in data loss or rounding errors, depending on the data types you are using. It can also break other parts of your application that may refer to the changed field. You should always use extra caution before modifying an existing table's structure.
With the ALTER TABLE statement, you can add, remove, or change a column (or field), and you can add or remove a constraint. You can also declare a default value for a field; however, you can alter only one field at a time. Let's suppose that we have an invoicing database, and we want to add a field to the Customers table. To add a field with the ALTER TABLE statement, use the ADD COLUMN clause with the name of the field, its data type, and the size of the data type, if it is required.
ALTER TABLE tblCustomers
ADD COLUMN Address TEXT(30)
To change the data type or size of a field, use the ALTER COLUMN clause with the name of the field, the desired data type, and the desired size of the data type, if it is required.
ALTER TABLE tblCustomers
ALTER COLUMN Address TEXT(40)
If you want to change the name of a field, you will have to remove the field and then recreate it. To remove a field, use the DROP COLUMN clause with the field name only.
ALTER TABLE tblCustomers
DROP COLUMN Address
Note that using this method will eliminate the existing data for the field. If you want to preserve the existing data, you should change the field's name with the table design mode of the Access user interface, or write code to preserve the current data in a temporary table and append it back to the renamed table.
A default value is the value that is entered in a field any time a new record is added to a table and no value is specified for that particular column. To set a default value for a field, use the DEFAULT keyword after declaring the field type in either an ADD COLUMN or ALTER COLUMN clause.
ALTER TABLE tblCustomers
ALTER COLUMN Address TEXT(40) DEFAULT Unknown
Notice that the default value is not enclosed in single quotes. If it were, the quotes would also be inserted into the record. The DEFAULT keyword can also be used in a CREATE TABLE statement.