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








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.



We have dozens of downloadable Access small business databases for you to learn and examine.

With our new partnership with Jay McCormick we have extensive programming support in SQL/Server database, MS Access software, Power BI applications, .Net web development, PHP, Excel & Outlook customization.


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
 







Contact Information

Copyright 2000-2017 Blue Claw Database Design