MS Access Create Table & Modify Table Commands

The following Access DDL example creates a table with 2 fields:

CREATE TABLE Inventory (ID Long, Description TEXT);

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

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

If you don't specify a maximum length then Access sets text length to 255 characters.

Create Table Statement Syntax

CREATE [TEMPORARY] TABLE table (field1 type [(size)] [NOT NULL] [WITH COMPRESSION | WITH COMP] [index1] [, field2 type [(size)] [NOT NULL] [index2] [, ...]] [, CONSTRAINT multifieldindex [, ...]])

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.
multifieldindex	A CONSTRAINT clause defining a multiple-field index.

Example Parent Child with AUTOINCREMENT

CREATE TABLE table1(id1 AUTOINCREMENT, name1 TEXT(50), CONSTRAINT table1_PK PRIMARY KEY(id1));

CREATE TABLE table2(id2 AUTOINCREMENT, id1 INT NOT NULL, name2 TEXT(100) NOT NULL,
CONSTRAINT t1-2_FK FOREIGN KEY (id1) REFERENCES table1 (id1),
CONSTRAINT table2_PK PRIMARY KEY(id2));

Alter Table Examples

ALTER TABLE Employees ADD COLUMN Emp_Email TEXT(25);

ALTER TABLE Article ADD COLUMN approved yesno;

DROP Statement Syntax

DROP {TABLE table | INDEX index ON table}

Deletes an existing table from a database or deletes an existing index from a table.

The Jet engine doesn't support the use of DROP, or any of the DDL statements, with non-Microsoft Jet databases. Use the DAO Delete method instead.

7 Needham Way, Skelmersdale, Lancashire, WN8 6PR 01695 720562

info@pssuk.com

Bespoke Software Development - Custom database & web business applications developer Lancashire North West, NW UK
Manchester, Liverpool, Warrington, St Helens, Southport, Preston, Blackburn, Bolton, Blackpool, Lancaster, Skelmersdale, Runcorn