Skip to Main Content
University of York Library
Library Subject Guides

Essential Access: a Practical Guide

Access basics

In this section, we'll explore the terminology used in Microsoft Access, how to navigate Access and explore different views of objects, the basics of using tables in datasheet view to access and edit data, and tips for working with Access database files.

Access terminology

To work with Access, the first thing you'll need to know is the terminology used in the menus and commands. We'll cover various bits of terminology as we go through this guide, but here's some good starting points:

  • Tables - Tables store data in a database. You should have a table for each individual thing that you have data about, for example you might have a table for students, a table for modules, a table for tutors, a table for rooms, etc, and each of these tables would contain information about these.
  • Records - A record in a row in the table and is an individual item of data and all of its associated information.
  • Fields - A field is a column in a table where a particular piece of information about each record is stored.
  • Objects - Objects are things you can create in Access to work with your database. Tables are a kind of object, and so are queries, forms, and reports, which we'll look at later in this guide.
  • Relationships - Relationships in Access define how different fields of data in different tables relate to each other (hence using a relational database). We'll explore how to create and edit relationships later in this guide.

Navigating Access

Access has various ways of opening and viewing the objects you need, and navigating between objects in your database.

Tip

When you open an Access database file, you may see a Security Warning banner in yellow, telling you that some active content has been disabled. You may need to choose Enable Content to ensure all features of the database work, but only do this if you trust the database and its creator.

Using tables in datasheet view

The datasheet view for a table displays all the fields (columns) and the records (rows) in that table. New records are added in the empty bottom row or by using the New (blank) record button at the bottom next to the navigation controls.

The datasheet view in access allows you to view records, edit individual records, navigate between them, and add new records.

Key things to bear in mind when working with tables in datasheet view:

  • The integrity of each record is maintained, meaning that each row always stays the same - you cannot sort or change one column and cause the order of one column to change without changing the rest so the rows stay the same.
  • There is always one blank row at the bottom of the table.
  • When a new record is added or existing data is edited, the unsaved record is shown by a pencil icon. Moving to another record will automatically save the edited record.
  • The order of records in a table is not important. You will use queries to define your view of the data, including the order of the records.
  • You can open and work with several Access tables (and other objects) at the same time.
  • Column widths and row heights can be manually adjusted, but all rows have to be the same height.

Working with Access files

An Access file, typically with a .accdb file extension, is usually referred to as an Access database, and contains all the tables, queries and any other objects that form that database.

You save the overall Access database file with a suitable name and then save the individual tables, queries, forms, and reports with other sensible names. For example, you might have a database called Training.accdb and then within it, tables called Sessions, Students, Marks, and Trainers. There also might be queries called qrySessionTrainers and qryStudentMarks, as typically query names start with qry. Often report names start with rpt and form names start with frm. You'll notice that these examples don't have spaces in them - this helps to prevent errors in Access so it is best practice to use camelCase, i.e. start each letter of a new word with a capital letter, when naming things in Access.

Always keep backups of your Access database. The files can corrupt so regular backups are best so you don't lose any recent updates if you need to revert to a backup copy.

Compact and Repair

A newly-created Access database file takes a portion of disk space in which to store and manage the tables and other objects, and extends as needed. However, if anything is deleted within the file, Access does not release this space back, meaning that the file size of an Access database can get very big.

There is a feature built into Access to keep your database at a reasonable file size, called Compact and Repair. Running this periodically helps to keep your file manageable. To do this, go to Database Tools > Tools > Compact and Repair Database. Any open objects will be closed and the database re-opened.

It is also possible to set a database to compact each time it closes, by going to File > Options > Current Database and then in the Application Options tick Compact on Close.

Exercises

Make sure you've downloaded the Essential Access exercise files before attempting these exercises.

Feedback
X