CREATE TABLE Statement

Description

Creates a new table.

Note The Microsoft Jet database engine doesn't support the use of CREATE TABLE, or any of the DDL statements, with non-Microsoft Jet database engine databases. Use the DAO Create methods instead.

Syntax

CREATE TABLE table (field1 type [(size)] [NOT NULL] [index1] [, field2 type [(size)]
ú [NOT NULL] [index2] [, ...]] [, CONSTRAINT multifieldindex [, ...]])

The CREATE TABLE statement has these parts

Part	Description

table	The name of the table to be created.
field1, field2	The name of field or fields to be created in the new table. You must create at least one field.
type	The data type of field in the new table.
size	The field size in characters (Text and Binary fields only).
index1, index2	A CONSTRAINT clause defining a single-field index. See the CONSTRAINT clause topic for more information on how to create this index.
multifieldindex	A CONSTRAINT clause defining a multiple-field index. See the CONSTRAINT clause topic for more information on how to create this index.

Remarks Use the CREATE TABLE statement to define a new table and its fields and field constraints. If NOT NULL is specified for a field, then new records are required to have valid data in that field.

A CONSTRAINT clause establishes various restrictions on a field, and can be used to establish the primary key. You can also use the CREATE INDEX statement to create a primary key or additional indexes on existing tables.

You can use NOT NULL on a single field, or within a named CONSTRAINT clause that applies to either a single field or to a multiple-field named CONSTRAINT. However, you can apply the NOT NULL restriction only once to a field, or a run-time error occurs.

See Also ALTER TABLE statement, CONSTRAINT clause, CREATE INDEX statement, CreateTableDef method ("DAO Language Reference"), DROP statement.

Example

This example creates a new table called ThisTable with two Text fields.

Sub CreateTableX1()

    Dim dbs As Database

    ' Modify this line to include the path to Northwind
    ' on your computer.
    Set dbs = OpenDatabase("Northwind.mdb")

    ' Create a table with two text fields.
    dbs.Execute "CREATE TABLE ThisTable " _
        & "(FirstName TEXT, LastName TEXT);"

    dbs.Close

End Sub

This example creates a new table called MyTable with two Text fields, a Date/Time field, and a unique index made up of all three fields.

Sub CreateTableX2()

    Dim dbs As Database

    ' Modify this line to include the path to Northwind
    ' on your computer.
    Set dbs = OpenDatabase("Northwind.mdb")

    ' Create a table with three fields and a unique
    ' index made up of all three fields.
    dbs.Execute "CREATE TABLE MyTable " _
        & "(FirstName TEXT, LastName TEXT, " _
        & "DateOfBirth DATETIME, " _
        & "CONSTRAINT MyTableConstraint UNIQUE " _
        & "(FirstName, LastName, DateOfBirth));"

    dbs.Close

End Sub

This example creates a new table with two Text fields and an Integer field. The SSN field is the primary key.

Sub CreateTableX3()

     Dim dbs As Database

    ' Modify this line to include the path to Northwind
    ' on your computer.
    Set dbs = OpenDatabase("Northwind.mdb")

    ' Create a table with three fields and a primary
    ' key.
    dbs.Execute "CREATE TABLE NewTable " _
        & "(FirstName TEXT, LastName TEXT, " _
        & "SSN INTEGER CONSTRAINT MyFieldConstraint " _
        & "PRIMARY KEY);"

    dbs.Close

End Sub

Example (Microsoft Access)

To try the following examples in Microsoft Access, first create a new query in the Northwind sample database. Close the Show Table dialog box without specifying a table or query. Switch to SQL view, paste an individual example into the SQL window, and run the query.

Warning These examples makes changes to the Northwind sample database. Before beginning, you may wish to make a backup copy of the sample database.

The following example creates a new table with two Text fields:

CREATE TABLE FirstTable (FirstName TEXT, LastName TEXT);

The next example creates a new table with two Text fields, a Date/Time field, and a unique index made up of all three fields:

CREATE TABLE SecondTable (FirstName TEXT,
LastName TEXT, DateOfBirth DATETIME,
CONSTRAINT MyTableConstraint UNIQUE (FirstName, LastName, DateOfBirth));

The following example creates a new table with two Text fields and an Integer Number field. The SSN field is the primary key.

CREATE TABLE ThirdTable (FirstName TEXT, LastName TEXT, SSN INTEGER
CONSTRAINT MyFieldConstraint PRIMARY KEY);