<%dim crumb crumb="Alter Table" %> Alter Table in Microsoft Access
Home  Fees/Services  Access Examples  Tutorials  Access Download  Articles  Search  Contact  Privacy  Links
Manage Tables & User Access with
SQL Table Management Commands
Alter Table DDL Language Reference
Home > Access Programming Tutorials > Manage Tables & Users<%response.write " > " & crumb %>
<% dim Gnum,Ynum Gnum="7999663463" Ynum="7867" dim Page_Type Page_Type="Prog" %>

Create Table
Create Index
Alter Table


Microsoft Office:
  MS Access 2003
  Access 2007
  Access 2010
  Access 2013

Alter Table Statement Examples

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:


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:




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);"

End Sub

Contact Information

<% Response.write "Copyright 2000-" & year(now) & " Blue Claw Database Design" %>