This statement supports several different commands to change the structure of a table, including adding, deleting, moving, and renaming fields.
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 a 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 statement is used to rename a table, add a column, move a column, rename a column, or delete a field 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.
Tables cannot have duplicate field names.
Example
The following code sample shows how to use the ALTER TABLE statement to modify the structure of a table.
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