Alter Table in Microsoft Access

 

Table Management Using Data Definition Language (DDL)

The DDL Alter Table statement provides you with the ability to ADD/DROP table fields and to change data types as well as modify or create indexes.
Here is an example of the alter table statement which will add a column ‘Emp_Email’ to our Employees table:

ALTER TABLE Employees ADD COLUMN Emp_Email TEXT(25);

Weeks later we discover that 25 characters is not enough to hold all email address so we need to increase the length to 50 characters using the following alter table statement:

ALTER TABLE Employees ALTER COLUMN Emp_Email TEXT(50);

Below is the syntax for the alter table statement borrowed from Microsoft Access 2003 help system.

ALTER TABLE table {ADD {COLUMN field type[(size)] [NOT NULL] [CONSTRAINT index] |
ALTER COLUMN field type[(size)] |
CONSTRAINT multifieldindex} |
DROP {COLUMN field I CONSTRAINT indexname} }

The ALTER TABLE statement has these parts:

Part Description table The name of the table to be altered. field The name of the field to be added to or deleted from table. Or, the name of the field to be altered in table. type The data type of field. size The field size in characters (Text and Binary fields only). index The index for field. multifieldindex The definition of a multiple-field index to be added to table. indexname The name of the multiple-field index to be removed.

From the above information you can see that the Alter Table statement has three common forms:

Alter Table Statement:

  • ADD COLUMN

  • ALTER COLUMN

  • DROP COLUMN

Here we add a foreign key constraint to the M_employees table for Dept_ID in the L_Departments table:

ALTER TABLE M_Employees ADD CONSTRAINT fk_Employee_Dept FOREIGN KEY (Dept_ID) REFERENCES L_Departments (Dept_ID);

Next we drop the constraint that we just added:

ALTER TABLE M_Employees DROP CONSTRAINT fk_Employee_Dept;

To send patches to customer you might want to send a little database that scripts the changes using Visual Basic code:

Sub Create_Table_Script()

Dim db As Database

Set db = OpenDatabase(“yourcustomers.mdb”)

‘alter employees table to add foreign key reference as above

db.Execute “ALTER TABLE M_Employees ADD CONSTRAINT fk_Employee_Dept FOREIGN KEY (Dept_ID) REFERENCES L_Departments (Dept_ID);”

db.Close
End Sub

Microsoft Office:
MS Access 2003
Access 2007
Access 2010
Access 2013
Alter Table Statement Examples

Create Table
Create Index
Alter Table