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.
Microsoft Access is a fully capable database and it's primary limitations is the number of concurrent users it will support. Generally, we recommend a limit of 10 to 15 users.
We create databases large and small. Some of our databases help run entire small businesses. We also handle unique businesses such as flyrod manufacturing, cheese making, marina management, and cable tv inventory.
The following Access DDL create table example creates an Inventory Tracking table with 3
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,
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
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
6. Save the table and give it an appropriate name.