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.
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
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.
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