ADO: ActiveX Data Objects

Database Modification

The last part of the functionality of SQL is database modification. With SQL, you can dynamically change the structure of a database by adding, modifying, and deleting tables. When you perform database modification on tables, either by adding or modifying them, you can add or modify indexes and constraints.

CREATE TABLE

The CREATE TABLE statement creates a new table in the current data source. This statement has the following syntax:

CREATE TABLE table_expression
             (field_name1 data_type [(size)] [NOT NULL]
             [, field_name2 data_type [(size)] [NOT NULL] [, ...]])

The following CREATE TABLE statement creates the Automobile table:

CREATE TABLE Automobile
             (Year LONG NOT NULL,
              Make TEXT (12) NOT NULL,
              Model TEXT (15) NOT NULL,
              Color TEXT (10) NOT NULL,
              Cost LONG);

The NOT NULL keywords in the preceding example tell the data provider to create the particular field so that it does not accept NULLs as legal values for that field.

ALTER TABLE

The ALTER TABLE clause alters an existing table in your current data source. The syntax for the ALTER TABLE clause is:

ALTER TABLE table_expression
  [ADD COLUMN field_name1 data_type [(size)] [NOT NULL]
              [CONSTRAINT index_name1 | CONSTRAINT multifield_index_name]] |
  [DROP {COLUMN field_name1 | CONSTRAINT index_name1}]

The ALTER TABLE statement is very powerful. You can add or delete a particular field in a table or add or delete a CONSTRAINT within a table. The following example illustrates adding a field to the Automobile table:

ALTER TABLE ...