Use a Query to Create a New Table Complete with Indexes

Problem

You know how to create a table from a make-table query, but when you create a table in this way it has no primary key or any other indexes. Furthermore, you can only create a new table with a structure based on that of an existing table. You’d like a way to create a table on the fly with the data types and field sizes you want and with appropriate indexes.

Solution

Access provides the data definition language (DDL) query, which is used to programmatically create or modify tables. It is one of the SQL-specific queries, which can be created only using SQL view. This solution shows you how to create and modify table definitions using DDL queries.

Follow these steps to create a table using a DDL query:

  1. Design your table, preferably on paper, deciding which fields and indexes you wish to create. For example, before creating qryCreateClients, we came up with the design for tblClients shown in Table 1-8.

    Table 1-8. Design for tblClients

    FieldName

    DataType

    FieldSize

    Index

    ClientID

    AutoNumber

    Long Integer/Increment

    Yes, primary key

    FirstName

    Text

    30

    Yes, part of ClientName index

    LastName

    Text

    30

    Yes, part of ClientName index

    CompanyName

    Text

    60

    Yes

    Address

    Text

    80

    No

    City

    Text

    40

    No

    State

    Text

    2

    No

    ZipCode

    Text

    5

    No

  2. Create a new query. Click on Close at the Add Table dialog. Select Query SQL Specific Data Definition. This will place you in SQL view.

  3. Enter a CREATE TABLE SQL statement. To create the sample table tblClients, enter the following SQL:

    CREATE TABLE tblClients
    (ClientID AutoIncrement CONSTRAINT PrimaryKey PRIMARY KEY,
    FirstName TEXT (30),
    LastName TEXT (30),
    CompanyName TEXT (60) CONSTRAINT CompanyName UNIQUE,
    Address TEXT (80),
    City TEXT (40),
    State TEXT (2),
    ZipCode TEXT (5),
    CONSTRAINT ClientName UNIQUE (LastName, FirstName) );
  4. Save your query and run it by selecting Query Run or clicking on the exclamation point icon on the toolbar. You should now see the newly created table in the database container.

To see how this works, open 01-14.MDB. Note that there are no sample tables in this database. Open the sample DDL query, qryCreateClients (see Figure 1-43). Select Query Run or click on the exclamation point icon on the toolbar to execute the DDL query. The tblClients table will be created, complete with a primary key and two other indexes.

A sample DDL query and the table it creates

Figure 1-43. A sample DDL query and the table it creates

Discussion

When you run a DDL query, Access reads through the query’s clauses and creates a table according to your specifications. This allows you to precisely control the structure of the table and its indexes.

A DDL query can contain only one data-definition statement. The five types of data-definition statements are:

CREATE TABLE

Creates a table

ALTER TABLE

Adds a new field or constraint to an existing table (a constraint creates an index on a field or group of fields)

DROP TABLE

Deletes a table from a database

CREATE INDEX

Creates an index for a field or group of fields

DROP INDEX

Removes an index from a field or group of fields

Note that we specified the lengths of most of the text fields in the sample query to save space. If you don’t specify a length for a text field in a DDL query, Access will assign it the maximum length of 255 characters, but that length won’t necessarily affect the size of the database. The field length is just a maximum—the space is not used unless it is needed.

If you wish to create field names with embedded spaces, you’ll need to surround the names with brackets; otherwise, the brackets are optional.

Like make-table queries, DDL queries do not automatically overwrite an existing table. However, unlike make-table queries, you aren’t offered the option of overwriting the existing table if you want to. If you need to overwrite an existing table when running a DDL query, first execute another DDL query containing a DROP TABLE statement.

After you create (or delete) a table with a DDL query, the new table won’t immediately appear in (or disappear from) the database window. To refresh the display and see the change you made, click on another object type in the database window (for example, Forms) and then on the Table tab again.

Warning

As with other SQL-specific queries, be careful not to switch a DDL query to another query type, such as a select query. If you do, your SQL statement will be discarded, because SQL-specific queries don’t have a design view equivalent.

You can also create tables complete with indexes using Data Access Objects (DAO) or ADOX, using VBA code, and you can use DAO QueryDefs or ADO commands to execute your DDL statements in code.

Tip

New DDL syntax was added in Access 2000 ( Jet 4.0), but few Access programmers ever used it because it didn’t work in the SQL pane of the Access user interface. The only way to take advantage of the new syntax was by executing ADO commands. In Access 2002, this new syntax is now supported inside of Access. For example, you can use ALTER TABLE ALTER COLUMN to change the data type of an existing field in a table. In the past, you had to drop the column and create a new one.

Get Access Cookbook now with the O’Reilly learning platform.

O’Reilly members experience books, live events, courses curated by job role, and more from O’Reilly and nearly 200 top publishers.