ALTER TABLE

This statement supports several different commands to change the structure of a table, including adding, deleting, moving, and renaming fields. Tables cannot have duplicate field names.

Syntax

ALTER TABLE tablename TO tablename2

ALTER TABLE tablename ADD fieldname fieldtype [BEFORE fieldname2]

ALTER TABLE tablename DROP fieldname

ALTER TABLE tablename MOVE fieldname [BEFORE fieldname2]

ALTER TABLE tablename RENAME fieldname TO fieldname2

Parameters

tablename

Specifies the name of the table in which to make structural changes.

tablename2

Specifies the new name for an existing table. A table with the same name cannot already exist.

fieldname

Specifies the name of the column in the table to add, remove, or rename. In order to add a column, it cannot already exist.

fieldname2

Specifies the name of another column in the table. For ADD and MOVE, the name must exist in the table. For RENAME, the name cannot already exist.

fieldtype

Specifies the data type for the column. For more information about data types, see CREATE TABLE.

Remarks

The ALTER TABLE syntax enables you to rename a table, add a column, move a column, rename a column or delete a column from a table.

A table must contain at least one field. Attempting to drop the last field in a table results in an adErrIllegalOperation error.

Example

Dim rs, i
Set rs = CreateObject("adoce.recordset")
rs.open "create table changeme (f1 varchar)"
rs.open "alter table changeme to changed"
rs.open "alter table changed rename f1 to firstfield"
rs.open "alter table changed add secondfield varchar"
rs.open "alter table changed add lastfield varchar"
rs.open "alter table changed move lastfield before firstfield"
rs.open "changed"
For i = 0 To rs.fields.Count - 1
MsgBox rs.fields(i).Name
Next
rs.Close
rs.open "drop table changed"
Set rs = Nothing