ALTER TABLE Statement Example

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