Microsoft Access SQL
Data-Definition Query

Microsoft Access 97/2000 can utilize Data Definition SQL. The process is a bit convoluted but it does work. Access has the limitation that you can only create one table per Data-Definition Query.

I recommend using an external text editor to inhibit MS Access' love for parenthesis and other odd characters. Then copy and paste your pure SQL code directly into the Access SQL Query window.

My recommended text editor is the shareware product TextPad. Be sure to download and install the SQL Syntax Definition Add-In to have color-coded SQL.

Much of the following information was taken directly from MS Access' own help files. The Access help file item can be viewed by searching doing a search for data definition query.

Use SQL data-definition queries to create, delete, or alter tables or create indexes in the current database.

1. In the Database window, click Queries under Objects, and then click New
   on the Database window toolbar.
   
2. In the New Query dialog box, click Design View, and then click OK.
3. Without adding tables or queries, click Close in the Show Table dialog box.
4. On the Query menu, point to SQL Specific, and then click Data Definition.
5. Enter the SQL statement for your data-definition query. Each data-definition query consists of just one data-definition statement. Microsoft Access supports these data-definition statements:
   • CREATE TABLE creates a table.
   • ALTER TABLE adds a new field or constraint to an existing table.
   • DROP deletes a table from a database or removes an index from a field or group of fields.
   • CREATE INDEX creates an index for a field or group of fields.
     
6. To run the query, click Run on the toolbar.

Caution: If you convert a data-definition query to another type of query, such as a select query, you'll lose the SQL statement that you entered.

CREATE TABLE Statement

Creates a database table (entity set) with the specified structure.

Syntax:

CREATE TABLE tablename (
   field1 dataType(size) fieldProperties 
          CONSTRAINT     pkField          PRIMARY KEY,
   field2 dataType       fieldProperties,
   field3 dataType(size) fieldProperties
); 

MS Access SQL Key Words

Example 1: Standard SQL Data Types that Access Recognizes

CREATE TABLE CAR (
    carID
           INT         NOT NULL    UNIQUE  
                CONSTRAINT  pkCAR       PRIMARY KEY,
    carMake     CHAR(20)    NOT NULL,
    carModel    CHAR(40)    NOT NULL,
    carDoors    SMALLINT    NOT NULL,
    carYear     SMALLINT    NOT NULL,
    carType     CHAR(1)     NOT NULL,
    carLength   NUMBER      NOT NULL,
    carPurchase DATE,
    carFeatures VARCHAR
); 

MS Access Data Types

Access seems to handle the standard SQL data types used in the example 1 code.

However, it is far better to use the data types specified in Access for an optimized table design.

Listed in the following table are some of the data types supported by access.

Example 2 illustrates the use of these Access specific data types in SQL in a similar problem as the previous example.

MS Access Supported Data Types

Example 2: Standard Access SQL Data Types (Do This)

CREATE TABLE CAR (
    carID       INTEGER         NOT NULL    UNIQUE  
                CONSTRAINT      pkCAR       PRIMARY KEY,
                
    carMake     TEXT(20)        NOT NULL,   
    carModel    TEXT(40)        NOT NULL,
    carDoors    BYTE            NOT NULL,
    carYear     SMALLINT        NOT NULL,
    carType     TEXT(1)         NOT NULL,
    carLength   SINGLE          NOT NULL,
    carPurDate  DATETIME,
    carPurPrice CURRENCY,
    carWeight   DOUBLE          NOT NULL,   
    carImport   YESNO           NOT NULL
); 

MS Access -Table Relationships

Defining relationships between two tables requires a foreign key specification.

MS Access allows you to specify the foreign key in the data definition query using the REFERENCES keyword.

This is best illustrated by creating a second table which specifies the car type details and then creating a relationship in the CAR table using a foreign key.

The two CREATE TABLE statements must be stored in seperate data definition queries in Microsoft Access.

The Relationship

Example 3: Statements must be stored in seperate queries

CREATE TABLE CARTYPE (
typeID      CHAR(1)     NOT NULL    UNIQUE  
            CONSTRAINT pkTYPE PRIMARY KEY,
typeName    CHAR(40)    NOT NULL    
); 
 
 
CREATE TABLE CAR (
    carID       INTEGER         NOT NULL    UNIQUE
                CONSTRAINT      pkCAR       PRIMARY KEY,
    carMake     TEXT(20)        NOT NULL,
    carModel    TEXT(40)        NOT NULL,
    carDoors    BYTE            NOT NULL,
    carYear     SMALLINT        NOT NULL,
    carType     TEXT(1)         NOT NULL
    CONSTRAINT  fkCARTYPE       REFERENCES  CARTYPE(TypeID),

    carLength   SINGLE          NOT NULL,
    carPurDate  DATETIME,
    carPurPrice CURRENCY,
    carWeight   DOUBLE          NOT NULL,
    carImport   YESNO           NOT NULL
);