Access Create Table Statement Example

Database and Table Management Using SQL Data Definition Language (DDL)

Get into the create new query wizard and then close the Show Table dialog box without specifying a table or query. Change to SQL view and type the Access table creation SQL statement.

The following Access DDL create table example creates an Inventory Tracking table with 3 fields:

CREATE TABLE Inventory (Inventory_ID Long, Inventory_Item TEXT, Inventory_Count Long);

The next example creates a new employee table with two Text fields, a Date/Time field, and a multi-field (composite) primary key index using the three fields.

CREATE TABLE Employees (First_Name TEXT(20), Last_Name TEXT(25), dob DATETIME, Constraint Employees_PK Primary Key (First_Name, Last_Name, dob));

Note the Text(20) field type and length designation - this says create a text field with a maximum of 20 characters.  If you don't specify the maximum length then Access will typically set the length to 255 characters.

Below is the syntax for the create table statement from the Microsoft Access 2003 help system.