There are occasions when you may want to manipulate the structure of your database and in one example using the Access ALTER table to modify fields in a table without the hassle of going to the design area (under the hood) and physically change fields and their data types.

This is part of the Data Definition query series in MS Access and can only be built using the SQL view only as there is no design query grid (QBE grid) for your convenience.

The language and keywords required to change or modify a field in a table is as follows:

ALTER TABLE table_name ADD/DROP/ALTER column_name [datatype]

The ADD keyword will create a new uniquely named field with a specified data type (i.e. as Text(25)).

The DROP keyword will delete existing fields and their properties too.

The ALTER keyword will modify an existing field should you need to change fields size or set an index.

Access Alter Table: A Data Definition Query – The Steps

Here’s a simple example of adding a new field called ‘Notes’ to the Customers table setting the field’s data type as Text of size 255.

1. Open a new query in design view and either switch to the SQL view or click the Data Definition icon from the Ribbon bar (or for earlier versions, from the menu or toolbar).

2. In the query window, add the following example SQL:

ALTER TABLE Customers ADD COLUMN Notes Text(255)

3. Now run the table and then go and open the design view of the Customers table (or whichever table you used) and see the newly added field.

You don’t have to save the query as in this case trying to run it again will cause this query to raise an error as you cannot have the same named field more than once in the same table!

The Access ALTER table command can of course be added into your macros and VBA procedures too making it really smooth to maintain your database.

Claim your free guide SQL syntax guide here.

Tags: Access, access alter query, access data defintion ddl, add column using sql alter, how to create data defintion queries, writing sql in access