Skip to content
Tutorials Center
My learning

Topics

Lesson 5 of 36 ยท Tables and Relationships

Creating Tables in Access: Datasheet and Design View

Build Access tables three ways: Datasheet view, Design view and a SQL CREATE TABLE statement. Learn naming rules, descriptions, saving, renaming and editing safely.

Tables are where your data lives, so every other object in a database depends on them. A good table holds facts about one kind of thing: customers, products, orders, loans. Each row describes one instance, and each column is one fact about it.

Access gives you three ways to create a table: type into a datasheet and let Access guess the types, define the fields precisely in Design view or run a CREATE TABLE statement in SQL. This lesson covers all three, and shows how to rename, modify, copy and delete tables without damaging your data.

Planning before you click

Spend five minutes with paper first. For a small shop database, list the things you track (customers, products, orders), then for each one list the facts: customer name, email, city. Ask of every fact: "Does it belong to this thing alone?" A product's price belongs to the product. A customer's phone number belongs to the customer. An order's date belongs to the order, not the customer.

Following this habit makes the later design chapter easy. If you jam everything into one wide table, you will have to fix it later.

Method 1: Datasheet view

Datasheet view is the quick way. Open Create > Table. A blank table opens with an ID AutoNumber field and a Click to Add column.

  1. Click Click to Add and choose a type (Short Text, Number, Currency, Date & Time, Yes/No and so on).
  2. Type the field name, then press Enter.
  3. Repeat for each field.
  4. Type your records.
  5. Press Ctrl+S and name the table.

A shortcut: if you type data into the first empty column without choosing a type, Access guesses the type from what you typed. Typing 5/3/2026 creates a Date field, 12.50 a Number field and Alice a Short Text field. This is fast but sometimes wrong, so check the result in Design view.

You can also use the Fields tab on the ribbon (visible while a datasheet is open) to add predefined fields such as Address, Phone or Email, which come with suitable types and formats.

Method 2: Design view

Design view gives you complete control and is the method most professionals use. Open Create > Table Design.

The window has two parts:

  • The field grid at the top has three columns: Field Name, Data Type and Description (Optional).
  • The Field Properties pane at the bottom changes with the selected field. It has a General tab and a Lookup tab.

To add a field, type a name in the first empty row, press Tab, choose a type from the dropdown and, if you like, add a description. To set the primary key, select the field row (click the grey box at its left) and click Primary Key on the Table Design tab. A key icon appears.

When you finish, press Ctrl+S and give the table a name.

Press F6 to jump between the field grid and the Field Properties pane without touching the mouse. Press F4 to show the full Property Sheet.

Field naming rules

Access allows field names up to 64 characters. The characters you cannot use are a period, exclamation mark, square brackets and a grave accent. Spaces are allowed, but avoid them. A name such as Order Date forces you to write [Order Date] every time it appears in a query or code, while OrderDate can be used as is.

Also avoid reserved words as names. Fields called Date, Name, Order, Group, Count or Level often cause confusing errors in queries and VBA. Use OrderDate, CustomerName or GroupNumber instead.

Good names are:

  • short but descriptive: Email, UnitPrice, HireDate
  • consistent: if one table has CustomerID, other tables use the same name for the same key
  • singular: a field holds one value

Method 3: A SQL statement

Every Access table can also be created with a data-definition query. In Access, open Create > Query Design, close the Show Table dialog, switch to SQL View, type the statement and click Run.

Access SQL uses TEXT (or VARCHAR) for short text, LONG or INTEGER for numbers, CURRENCY for money and COUNTER (or AUTOINCREMENT) for AutoNumber. A real Access statement looks like this:

SQL
CREATE TABLE Products (
  ProductID COUNTER PRIMARY KEY,
  ProductName TEXT(80) NOT NULL,
  UnitPrice CURRENCY,
  InStock YESNO,
  AddedOn DATETIME
);

The standard-SQL version below does the same in a form this page can run. The comments map each type to what Access would use.

SQL CREATE TABLE with constraints

Press Run (or Ctrl + Enter) to see the result here.

The result is a miniature Design view: each row is a field, with its type, whether it is required and whether it is the key.

You can also add or change columns with ALTER TABLE:

SQL ALTER TABLE

Press Run (or Ctrl + Enter) to see the result here.

Access SQL supports ALTER TABLE ... ADD COLUMN, ALTER COLUMN and DROP COLUMN. SQLite, which runs this page, supports adding and dropping columns but not changing a column's type in place.

Editing a table's structure safely

You can change a table in Design view at any time, but be careful when data already exists.

Change What happens
Add a field Safe. Existing rows get empty values.
Rename a field Access updates the table, but queries, forms and VBA that used the old name may break unless Name AutoCorrect fixed them. Check dependent objects afterward.
Make Text shorter Access warns that data longer than the new size is lost.
Change Text to Number Rows with non-numeric text become blank. Access warns first.
Delete a field Removes the column and its data. Cannot be undone.
Move a field Drag the row selector. Order does not affect the data.

Before a risky change, run Back Up Database, or copy the table (select it in the Navigation Pane, press Ctrl+C, then Ctrl+V and choose Structure and Data).

To see what depends on a table, right-click it in the Navigation Pane and choose Object Dependencies, or use Database Tools > Object Dependencies. If this is empty at first, Access asks you to update dependency information.

Renaming, copying and deleting tables

  • Rename: right-click the table in the Navigation Pane, choose Rename (or select it and press F2).
  • Copy: select and press Ctrl+C, then Ctrl+V. Choose Structure Only to start a new empty table with the same design, Structure and Data for a full copy or Append Data to Existing Table.
  • Delete: select and press Delete. Access refuses if the table is part of a relationship; remove the relationship first.

Deleting a table deletes all its data with no Undo. Always back up first, or move the table to an archive database by exporting it.

Table properties worth knowing

Open Property Sheet (F4) in Design view with nothing selected to see table-level properties:

  • Description appears when you hover over the table in the Navigation Pane.
  • Default View is Datasheet by default.
  • Validation Rule and Validation Text apply a rule across fields in a record (for example, [EndDate] >= [StartDate]).
  • Filter and Order By set a default filter and sort for the datasheet.
  • Subdatasheet Name controls the plus sign that expands related records; set it to [None] to turn it off and speed up large tables.

Check your understanding

Quick check ยท 4 questions

of 4 answered You scored / 4 ยท perfect!

  1. 1. Which view gives you full control over field types and properties?

    Design view shows each field's name, data type, description and properties. Datasheet view only guesses types from what you type.
  2. 2. Why should you avoid spaces and reserved words such as Date in field names?

    Names with spaces need square brackets, and reserved words can be mistaken for functions or keywords.
  3. 3. What happens to existing data if you shorten a Short Text field below the length of some values?

    Access warns that data may be lost and truncates longer values if you continue.
  4. 4. Which keyword creates an AutoNumber field in Access SQL?

    Access SQL uses COUNTER (or AUTOINCREMENT). Other databases use SERIAL, IDENTITY or AUTO_INCREMENT.

Summary

  • Plan one table per kind of thing, with one fact per field.
  • Create tables by typing in Datasheet view, by defining fields in Design view or with CREATE TABLE in SQL view.
  • Choose clear names without spaces or reserved words, and keep key names consistent across tables.
  • Back up before changing a table that already contains data; renames can break queries and forms.
  • Table properties such as Validation Rule and Subdatasheet Name tune behavior beyond the fields.

Next: Access Field Data Types