One of the first things an Access developer
learns is how to write SQL Queries. A simple request for data might look
something like this:
SELECT FullName, Address, Phone
FROM tblEmployee ORDER BY FullName
This type of query uses Data Manipulation Language (DML). While this
example does not perform any great manipulation of the data, some very clever
calculations are possible. The SQL language is powerful indeed.
However, once you have gotten a handle on DML, you will want to explore the
world of DDL, Data Definition Language. This too is part of the SQL
language and can be run from an Access query, or through VBA code, but unlike
DML, these commands do not return a result set. DDL is used to create and
alter database objects, such as tables. First, let's see how this is done
and then consider some scenarios where this technique will save you time and in
some cases, a lot of work.
Help Yourself: Where to find answers
Before we look at some examples, it is worth
revisiting the old "teach a man to fish" analogy. Truth is, if
I do not use a particular code technique frequently, I tend to forget the
syntax. While I am sure a comprehensive explanation exists in one of the
mammoth technical volumes resting on my bookshelf, it is easier to simply
search the Internet for the answer. My favorite resource is the advanced
search page at Google Groups.
In fact, I find this resource to be so valuable, I have saved the following
hyperlink to my Internet Explorer LINKS toolbar: Access GoogleNewsgroup Search (This
hyperlink pre-populates frequently used fields, saving me time.) A quick
search for 'DDL' in the Access related newsgroups yielded a plethora
of results. What follows in this article was culled from suggestions
found there, as well as from an article from the Microsoft Knowledge
Base: 'How to' write Access DDL that I found while
searching the Google results.
The scripts used in this article have been collected into a demo database that
is available for download.
Create a Table
For starters, let's consider the example provided by Microsoft in
the KB Article mentioned above. This script shows, in a nutshell,
how to create a table with one column of each type of field, including
AutoNumber, which is only intuitive if you remember that in Access 2.0, the
AutoNumber was called a COUNTER field.
CREATE TABLE TestAllTypes
( MyText TEXT(50),
The process to create a DDL query is a little different from what you might
be used to. Begin in the normal way, by choosing New Query from the Query
window, but when prompted, do not add any tables. You are then
presented with the QBE (query by example) grid with no
tables. Select Data Definition from the Query
| SQL Specific menu to continue.
Selecting Data Definition will take you to a very plain, Notepad-like interface
where you can paste or type in your DDL SQL script. Save the query and
select Run from the Query menu to execute the
script. When you run this kind of query, Microsoft Access displays the following
If you know what you are doing, and want these warnings to go away, there is
an application option that allows you to turn off the confirmation message for
Action Queries. Choose Options from the Tools
menu and go to the Edit/Find tab. Deselect the checkbox
next to Action Queries. Alternatively, you can toggle
this option from the Immediate window using the following VBA command:
After running the script, open up the table in design view. Notice how
each field was created with its specific type and though the screen shot does
not display it, the TEXT field is indeed a 50-character field, just as we
It is not at all difficult to add an index, or a unique index, to a
column. For the example above, the following script puts an ordinary
index on the DateTime field and a unique index on the Text field. (Note:
Unlike SQL Server, DDL scripts in Microsoft Access must be run one at a time.)
CREATE INDEX MyDateTimeIndex ON TestAllTypes ([MyDateTime] ASC)
CREATE UNIQUE INDEX MyTextUniqueIndex ON TestAllTypes ([MyText] ASC)
DROP INDEX MyTextUniqueIndex ON TestAllTypes
Note from the last script above that you can also delete, or DROP,
indexes or entire tables. However, if you want to change the column
structure of a table, the syntax changes a bit. Rather than simply
dropping a column, you need to perform an ALTER TABLE command.
ALTER TABLE TestAllTypes DROP COLUMN MyBinary
ALTER TABLE TestAllTypes ADD COLUMN ExtraInfo Text(255)
When to use DDL
There are several scenarios where
DDL might be the perfect solution. Here are some that I've
encountered over the years:
Create and delete temp tables for reports or other calculations.
(Caution: will cause database file bloat. I have seen this done where the
tables are created in a temp mdb file that is created, loaded, used and
Maintenance scripts used to push new tables out to client
Used in conjunction with DAO to loop through a table's fields,
check their properties
and create a new table with proper naming, data types and sizes.
If you are ambitious, you could write a script to recreate your
As for that last point, I am surprised that
someone has not written a tool that will analyze your Microsoft Access MDB file
and build all the scripts necessary to recreate the tables, indexes,
constraints and maybe even load the data by means of SQL DDL. Perhaps
someone has created such a tool, but I searched for it last year without
success. If anyone knows of such a utility, please email me the lead.
There are, of course, aspects of DDL we did not touch on in this brief
article. It is a fascinating technology and once you start playing with
it, you will no doubt see its value.
See All Articles by Columnist Danny J. Lesandrini