Access Data Definition Language (DDL): Creating and Altering Tables On The Fly

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),
      MyMemo       MEMO,
      MyByte       BYTE,
      MyInteger    INTEGER,
      MyLong       LONG,
      MyAutoNumber COUNTER,
      MySingle     SINGLE,
      MyDouble     DOUBLE,
      MyCurrency   CURRENCY,
      MyReplicaID  GUID,
      MyDateTime   DATETIME,
      MyYesNo      YESNO,
      MyOleObject  LONGBINARY,
      MyBinary     BINARY(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 warning:

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:

     DoCmd.SetWarnings False

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 requested.

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 temp
    tables are created in a temp mdb file that is created, loaded, used and deleted.)
• Maintenance scripts used to push new tables out to client applications.
• 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 entire database.

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