ALTER TABLE Statement Example
This example adds a Salary field with the data type Money 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 Money data type.
dbs.Execute "ALTER TABLE Employees " _
& "ADD COLUMN Salary MONEY;"
dbs.Close
End Sub
This example changes the Salary field from the data type Money to the data type Char.
Sub AlterTableX2()
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 Money data type.
dbs.Execute "ALTER TABLE Employees " _
& "ALTER COLUMN Salary CHAR(20);"
dbs.Close
End Sub
This example removes the Salary field from 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")
' 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 do not 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 AlterTableX4()
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 AlterTableX5()
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