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.

 

 

Create Table in Interactive Design View (Access 2010)

1.  Open an existing database

2.  Select Create from the top row menu items

3.  In the ribbon menu select Table Design.  The table design view will appear.

4.  Enter the data field names in Field Name column and define the Data Type for each Data Field

5.  After all Data Fields have been defined choose a field that will be have unique values for each record in the table.  Set this field to be the Primary Key of the table by pressing the yellow key symbol in the menu ribbon.  Candidate fields for primary key may be SSN, Phone Number,  Employee Number, Employee Name, etc.  (note: at times you'll need to identify more than one field to create the primary key.  In this case select the fields by holding down the CTRL key and then pressing the primary key symbol in the ribbon menu.

6.  Save the table and give it an appropriate name.