Home  Fees/Services  Access Examples  Tutorials  Access Download  Articles  Search  Contact  Privacy  Links
Manage Tables & User Access with
SQL Table Management Commands
Alter Table DDL Language Reference








Create Table
Create Index
Alter Table

 

Microsoft Office:
  MS Access 2003
  Access 2007
  Access 2010
  Access 2013


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.



We have dozens of downloadable Access small business databases for you to learn and examine.

With our new partnership with IT Impact we have extensive programming support in SQL/Server database, MS Access software, Power BI applications, .Net web development, PHP, Excel & Outlook customization.



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 Statement Syntax

 

 

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.







Contact Information

Copyright 2000-2017 Blue Claw Database Design