Adding new table fields from code

[Charles Tam]

I would like to add several table fields from code. How could this be
implemented? What are my options?

 

[Douglas J. Steele]

Options include using DAO (the CreateField method), ADOX (the Append method
of the Columns collection) or DDL (Data Definition Language: ALTER TABLE
MyTable ADD COLUMN ...)

 

[cynthia]

If you have time could you help me with adding a new field to an existing
table and how to changing the field name in an existing table.

I have searched under alter table and cannot find this information.

 

[Douglas J. Steele]

From the Access 97 Help file:

This example adds a Salary field with a data type of Currency to the
Employees table.

Sub AlterTableX1()

Dim dbs As Database

' Modify this line to include the path to Northwind
' on your computer.
Set dbs = OpenDatabase("Northwind.mdb")

' Add the Salary field to the Employees table
' and make it a Currency data type.
dbs.Execute "ALTER TABLE Employees " _
& "ADD COLUMN Salary CURRENCY;"

dbs.Close

End Sub

This example removes the Salary field from the Employees table.

Sub AlterTableX2()

Dim dbs As Database

' Modify this line to include the path to Northwind
' on your computer.
Set dbs = OpenDatabase("Northwind.mdb")

' Delete the Salary field from the Employees table.
dbs.Execute "ALTER TABLE Employees " _
& "DROP COLUMN Salary;"

dbs.Close

End Sub

This example adds a foreign key to the Orders table. The foreign key is
based on the EmployeeID field and refers to the EmployeeID field of the
Employees table. In this example, you don't have to list the EmployeeID
field after the Employees table in the REFERENCES clause because EmployeeID
is the primary key of the Employees table.

Sub AlterTableX3()

Dim dbs As Database

' Modify this line to include the path to Northwind
' on your computer.
Set dbs = OpenDatabase("Northwind.mdb")

' Add a foreign key to the Orders table.
dbs.Execute "ALTER TABLE Orders " _
& "ADD CONSTRAINT OrdersRelationship " _
& "FOREIGN KEY (EmployeeID) " _
& "REFERENCES Employees (EmployeeID);"

dbs.Close

End Sub

This example removes the foreign key from the Orders table.

Sub AlterTableX4()

Dim dbs As Database

' Modify this line to include the path to Northwind
' on your computer.
Set dbs = OpenDatabase("Northwind.mdb")

' Remove the OrdersRelationship foreign key from
' the Orders table.
dbs.Execute "ALTER TABLE Orders " _
& "DROP CONSTRAINT OrdersRelationship;"

dbs.Close

End Sub