About this lesson
Overview of the Access objects covered so far. The main object types are Tables, Queries, Forms, Reports, Macros, and Modules.
Download this lesson’s exercise file.Services_11.accdb
Access 11. Perspective on Access Objects
Like a bucket, an Access database is a container for different types of objects. The main object types are Tables, Queries, Forms, Reports, Macros, and Modules. Tables are the most important type of object in an Access database. Other objects are ultimately based on tables.
Design View of Table
When you look at the Design View of a table, you can define and see the field names, data types, field descriptions, and other properties.
Within each table, indexes can be defined. Indexes are lists that Access makes, much like the indexes in the back of a book to make finding things easier.
As a general rule, Tables are what all other objects are ultimately based on and act upon.
Forms are for adding and editing information and are designed for the screen. Forms can also be used for menus and interactive dashboards.
An index is a list to find things faster, like the index in the back of a book. The most common table index is called Primary Key.
Key fields are used to link data in one table to another. There are Primary Keys and Foreign Keys.
Macros can be created to automate. When you create a macro, you can drop a list of choices for macro actions. You will then be prompted to fill out information that action needs. Macros are a good way to get started automating.
Modules contain VBA and are for automating. Modules can do more than macros.
A property is a characteristic of an object. Field properties include Field Name, Data Type, Description, Field Size, Default Value, and Format.
A query can show data from more than one table, sort, and filter.
Forms and Reports have a Record Source that defines where data comes from. A Tables, Query, or SQL statement can be used for a Record Source.
A Relationship defines how tables are connected to each other.
Reports are for printing and viewing information on the screen.
SQL is Structured Query Language and defines what data will be gotten, where it will come from, how it will be grouped and sorted, and under what conditions it will be included. Queries store SQL statements and show you the latest information when requested. This is basic SQL syntax:
GROUP BY fieldlist
HAVING conditions for fields that are grouped
ORDER BY fieldlist;
The only required clauses are SELECT and FROM. The FROM clause can join more than one table.
Data is stored in tables. Tables are composed of rows, which are called records, and columns, which are called fields.
Tables can be resident and stored in the database. Tables can also be linked to data that is stored in other sources. You can see data in a linked table as if the table were actually in the database.
When you open a table, you see a Datasheet View of the information.
Visual Basic for Applications is the programming language that Access desktop databases use.
Go to the Design View of a Table from the Navigation Pane
- Right-click on a table name in the Navigation Pane
- Choose Design View from the shortcut menu.
Go to the Design View of a Table from the Relationships Window
- Right-click on a table in the Relationships Window
- Choose Table Design from the shortcut menu
Go to the Design View of a Table from the Datasheet View
- When you are looking at the datasheet view of a table, click Design from the Views group on the HOME ribbon.
Lesson notes are only available for subscribers.