By using SQL DDL, you can remove a table from the database with the following statement:
DROP TABLE MyTable;
Note that this action is subject to all the security restrictions and referential integrity constraints that are established for the database. If the table is part of one or more relationships that are defined in the database, you can’t delete the table until you have deleted the relationship. For more information, see the “Creating and Deleting Relationships by Using SQL DDL” section later in this chapter.
Similarly, you can add or remove a field (column) from an existing table by using the following statements:
ALTER TABLE MyTable ADD COLUMN NewColumn TEXT (20); ALTER TABLE MyTable DROP COLUMN NewColumn;
When you use SQL DDL statements, you have no control over the order in which new fields are added to a table. Fields are inserted at the end of the list of fields when an ALTER TABLE ADD COLUMN statement is used. Each additional field created by an ALTER TABLE ADD COLUMN statement that follows the original CREATE TABLE statement is placed after the most recently created existing field.
CREATE TABLE YourTable (A CHAR (10));
yields the field A in the first field slot.
ALTER TABLE YourTable ADD COLUMN G CHAR(10);
yields the field A in the first field slot and G in the second slot.
ALTER TABLE YourTable ADD COLUMN F CHAR(10);
yields the field A in the first field slot, G in the second slot, and F in the third slot.
You can’t alter the data type of a field once it’s created, or change its name. The only way to convert an existing field from one data type to another is to add a new field, run an update query to populate the field with values from the original field, and then drop the original field. The same limitation applies to changes made through DAO; you can change a field’s name through DAO, but not its data type, and you can work around this limitation by using the same method.