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
Make sure you are careful not to leave these types of queries lying around in your database if you decide to save and keep them. Perhaps store these objects in another database file (as you library file) to preserve and protect your database structures.
Also, in order to execute these queries successfully, you must ensure that your tables are not open.
I have to wonder if the changes in the latest two versions of Access (2010 and the yet to be released new version) are better able to handle forms/user interfaces that update with these new fields, of course it would have to be a continous type form, based on the table itself and not a query, unless the query has Select * from …
I know and fully understand the havoc that could be wrought from letting end-users modify the database structure, but this thought was nothing more of a curiousity, not a support of practicing such a thing.
Yes, I understand you point of view.
The intention to this practice is reserved for the more advanced (and administrator level) use of Access and if cleverly designed via forms can be way to control how general end-users could (if allowed) be able to maintain and manage their own structures namely adding custom fields in addition to existing tables etc.
Thanks for your input – anyone else has a point of view?