Understanding Data-Definition Queries

3 4

A data-definition query is a SQL-specific query that contains DDL statements. These statements allow you to create or alter objects in the database. Specifically, data-definition queries create, delete, or alter tables or create indexes in the current database. Each data-definition query consists of just one data-definition statement. Access supports the following statements:

• CREATE TABLE. Creates a table.
• ALTER TABLE. Adds a new field or constraint to a table.
• DROP. Deletes a table or an index from a database, or deletes a field in a table.
• CREATE INDEX. Creates an index.

CREATE TABLE

Execute the CREATE TABLE statement when you want to create a new table. For example, the following statement creates a new table named TableOne, with two text fields, FirstName and LastName:

 CREATE TABLE TableOne (FirstName TEXT, LastName TEXT); 

Other Ways to Create Tables

In addition to using the CREATE TABLE statement, there are several other ways to create tables in Access, as follows:

• Use the CopyObject method of the DoCmd object in VBA code to create a new table that's a copy of an existing table.
• Write a make-table query to create a table.
• Append a new TableDef to the TableDefs collection of the Data Access Objects (DAO) object model in VBA code.

You can add an index by specifying the UNIQUE statement in the following form:

 CREATE TABLE TableOne (FirstName TEXT,  LastName TEXT UNIQUE (FirstName, LastName)); 

You can specify a primary key for the table using a statement in the following form:

 CREATE TABLE TableOne (LastName TEXT CONSTRAINT   MyIndex PRIMARY KEY, FirstName TEXT); 

tip

---

The CREATE TABLE statement won't overwrite an existing table of the same name (unlike a make-table query). If the table already exists, Jet will return an error message to that effect and cancel the task.

ALTER TABLE

SQL's ALTER TABLE statement performs a number of tricks, from renaming a table, to adding and deleting fields, to moving fields. Table 11-2 lists the different tasks you can complete by using ALTER TABLE.

Table 11-2. ALTER TABLE tasks

Task	Query
Rename table	ALTER TABLE table1 TO table2
Add a field	ALTER TABLEtable ADD field1 type [BEFORE field2]
Delete a field	ALTER TABLE table DROPfield
Move a field	ALTER TABLE table MOVE field1 [BEFORE field2]
Rename a field	ALTER TABLE table RENAME field1 TO field2

tip

---

A table must contain at least one field. You can't use the DROP clause to delete a table's last field.

note

---

The following example alters the Northwind Employees table. If you don't want to alter the actual table, work with a copy of the Employees table.

To alter the Employees table, follow these steps:

1. Click Queries in the Objects list in the Database window, click New on the Database Window toolbar, make sure Design View is selected, and then click OK in the New Query dialog box.
2. Close the Show Table dialog box without adding a table or query to the query design window.
3. Choose Query, SQL Specific, and then select Data Definition.
4. In the Data Definition Query window, enter the following statement, as shown in Figure 11-24:

    ALTER TABLE Employees ADD COLUMN PayRate CURRENCY; 

   

   Figure 11-24. The ALTER TABLE statement will add a new field to the Employees table.

5. Click Run to execute the query.
6. Open the Employees table in Design view to verify that Access added the PayRate field as a Currency data type.

DROP

SQL's DROP statement allows you to delete a table or an index. For example, the following statement deletes the index on the LastName field in the Employees table from the Northwind database:

 DROP INDEX LastName ON Employees; 

The following statement deletes the Employees table from the Northwind database:

 DROP TABLE Employees; 

Be sure to use the DROP TABLE statement sparingly and with caution, because you can't recover the table or its data.

tip

---

To delete a field from a table, use the ALTER TABLE statement.

CREATE INDEX

The last SQL data definition statement is the CREATE INDEX statement. As you might expect, this statement creates an index. In its simplest form, the statement adds an index, as follows:

 CREATE INDEX index ON table (field); 

The resulting index is based on a single field, allows duplicate values, and is not based on a primary key. The index can consist of more than one field: Simply list more than one field, and separate the fields with commas.

To avoid duplicates in an indexed field, add the UNIQUE reserved word in the following form:

 CREATE UNIQUE INDEX index ON table (field); 

The DISALLOW NULL reserved word will require an entry in the field. Simply tack the following qualifier on at the end of the statement:

 CREATE INDEX index ON table (field) WITH DISALLOW NULL; 

To designate the indexed field as a primary key, use the PRIMARY reserved word in the following form:

 CREATE INDEX index ON table (field) WITH PRIMARY; 

InsideOut

There's not much advantage to running a pass-through or data-definition query from the query window. You can just as quickly complete most of these tasks manually, unless you're very familiar with SQL. Most people will use these SQL statements in VBA code to programmatically perform these specific tasks.