Microsoft Access 2007 Level 1
(2 days - £450, plus VAT)

Prerequisites

Computer literacy: the ability to use a mouse and keyboard; familiarity with common computer software; the ability to open and save files.

Course Goals

To familiarise delegates with the core features of Microsoft Acess 2007 and relational databases. At the end of the training, delegates will be able to create tables, queries, forms and reports.

Course Outline

Working with Access Databases and Tables

Understanding Access Database Files
Creating a New Access Database from Scratch
Exploring the Properties of Tables and Fields
Choosing Field Data Types, Sizes, and Formats
Setting Default Values of Fields
Working with Relations, Key Fields, and Indexes
Altering Fields and Relationships

Entering, Editing, and Validating Access Table Data

Entering Data in Access Tables
Using Keyboard Operations for Entering and Editing Data
Adding Records to a Table
Selecting, Appending, Replacing, and Deleting Table Records
Validating Data Entry

Sorting, Finding, and Filtering Data

Understanding the Role of Sorting and Filtering
Sorting Table Data
Finding Matching Records in a Table
Replacing Matched Field Values Automatically
Filtering Table Data
Applying Advanced Filters and Sort Orders
Customizing Table Datasheet View

Linking, Importing, and Exporting Tables

Moving Data from and to Other Applications
Importing and Linking Spreadsheet Files
Working with Microsoft Outlook and Exchange Folders
Importing Text Files

Designing Queries for Access Databases

Introducing Access Queries
Trying the Simple Query Wizard
Using the Query Design Window
Testing the Other Query Wizards
Creating Other Types of Queries

Creating and Using Access Forms

Understanding the Role of Access Forms and Controls
Auto-Generating a Basic Transaction-Processing Form
Form Layout View's Contextual Ribbons
Rearranging the Default Form Layout
Changing Form View from a Tabbed Document to a Modal Pop-up Window
Setting Form Appearance Properties

Working with Simple Reports and Mailing Labels

Understanding the Relationship Between Forms and Reports
Categorizing Types of Access Reports
Auto-generating a Simple Tabular Report
Creating a Grouped Report with the Report Wizard
Using Access's Contextual Report Ribbons
The Print Preview Ribbon
Modifying a Basic Wizard Report

Table Relationships

Relationship Basics
One-to-One Relationships
One-to-Many Relationships
Many-to-Many Relationships
Ensuring Referential Integrity

Understanding Access Operators and Expressions

Writing Expressions for Query Criteria and Data Validation
Understanding the Elements of Expressions
Operators
Literals
Identifiers
Functions
Intrinsic and Named Constants
Creating Access Expressions

Creating Multitable and Crosstab Queries

Introducing Joins on Tables
Joining Tables to Create Multitable Queries
Using Lookup Fields in Tables
Updating Table Data with Queries
Making Calculations on Multiple Records
Designing Parameter Queries
Creating Crosstab Queries
Writing UNION Queries and Subqueries

Creating and Updating Access Tables with Action Queries

Getting Acquainted with Action Queries
Creating New Tables with Make-Table Queries
Creating Action Queries to Append Records to a Table
Deleting Records from a Table with an Action Query
Updating Values of Multiple Records in a Table
Testing Cascading Deletion and Cascading Updates

Designing Custom Multitable Forms

Working in Form Design View
Adding Label and Text Box Controls to a Blank Form
Adding Group Boxes with the Wizard
Using the Clipboard to Copy Controls to Another Form
Adding Combo and List Boxes
Adding an Attachment Control for Images
Working with Tab Controls
Optimizing the Form's Design
Adding Page Headers and Footers for Printing Forms

Preparing Advanced Reports

Creating Reports from Scratch
Grouping and Sorting Report Data
Working from a Blank Report
Incorporating Subreports
Customizing De Novo Reports
Mailing Reports as Attachments

Back to top

Microsoft Access 2007 Level 2
(1 day - £245, plus VAT)

Prerequisites

A good knowledge of all of the basic and intermediate features of Microsoft Access 2007.

Course Goals

To give experienced users an insight into advanced Access 2007 features for querying, managing, securing and automating databases.

Course Outline

Adding Graphs, PivotCharts, and PivotTables

Generating Graphs and Charts with Microsoft Graph
Printing Graphs or Charts in Reports
Creating a Linked Graph from an Access Crosstab Query
Working with PivotChart Forms
Substituting or Adding a PivotTable in a Form

Moving from Access Queries to Transact-SQL

Understanding the Role of SQL in Access 2007
Understanding SQL Grammar
Writing SELECT Queries in SQL
Writing Action Queries and Stored Procedures
Working with Tables in Another Database
Using SQL Statements with Forms, Reports, and Controls

Managing a Database

Backing Up a Database
Compacting and Repairing a Database
Changing Database Properties
Documenting a Database
Analyzing a Database
Using Add-Ins
Creating a Database Switchboard
Managing a Switchboard
Adding a Database Title and Icon
Splitting a Database

Database Protection

Adding Security Encryption to a Database
Creating a Digital Certificate
Adding a Digital Signature to a Database Project
Packaging and Digitally Signing a Database
Avoiding Harmful Attacks
Using the Trust Center
Setting Add-in Security Options
Setting Macro Security Options
Locking a Database
Locking Database Records

Automating Access Applications with Macros 2007

Understanding the Role of Macros in Access 2007
Exploring Access 2007's Event Repertoire
Generating Embedded Macros with the Command Button Wizard
Responding to Events from Combo and List Boxes
Exploring Access 2007's Macro-based Switchboard Manager

Back to top

Microsoft Access 2007 VBA
(2 days - £550, plus VAT)

Prerequisites

A good knowledge of all of the basic, intermediate and advanced features of Microsoft Access 2007.

Course Goals

To introduce users to the core elements of Access VBA: variables, arrays, conditionals, looping, the creation of sub-routines and functions. Delegates are also shown how to integrate basic error-handling and debug their code.

Course Outline

Using the Visual Basic Editor

First Look at the Visual Basic Editor
Explaining VBA Modules
Entering and Running Code
Getting Help on Code
Good Coding Habits

Using Variables, Constants, and Data Types

Declaring Variables and Constants
VBA Data Types
Referencing Syntax
Using Arrays

Building Procedures

Types of Procedures
Passing Arguments
Error Handling

Conditional and Looping Statements

Introducing Flow of Control Statements
Using If...Then...Else
Using Select Case
Using For...Next
Using Do Loops
Using GoTo

Object and Event-Driven Coding

Understanding Objects
Invoking Methods
Using Collections
Working with an Object Model
Creating Objects
Working with Events

Working with Forms

Opening and Closing Forms
The Form Module
Form and Control Properties
Form Events

Working with Controls

Selection Controls
Populating a List Control
Working with Text Boxes
Working with Check Boxes, Radio Buttons, or Toggle Buttons
Working with Subforms
Working with the Tag Property

Understanding Data Access Objects, OLE DB, and ADO

Gaining a Perspective on Microsoft Data Access Components
Creating DAO.Recordset2 and ADODB.Recordset Objects
Using the Object Browser to Display DAO and ADODB Properties, Methods, and Events
Working with the ADODB.Connection Object
Using the ADODB.Command Object
Understanding the ADODB.Recordset Object

Debugging and Error Handling

Debugging Code
Using Debugging Tools
Stepping Through Code
Using Breakpoints
Tracking Values in Variables and Expressions
The Purpose of Error Handling
Using On Error and Resume Statements
Exiting a Procedure
The Err Object

Back to top

Microsoft Access 2007 Intensive
(5 days - £1,095, plus VAT)

Prerequisites

Computer literacy: the ability to use a mouse and keyboard; familiarity with common computer software; the ability to open and save files.

Course Goals

If you need to master Microsoft Access 2007 in a hurry for an upcoming project, the Microsoft Access 2007 Course allows you to take all four levels of Access training in a one-week course.

Course Outline

The course comprises the following modules:

Microsoft Access 2007 level 1 (2 days)

Microsoft Access 2007 Level 2 (1 day)

Microsoft Access 2007 VBA (2 days)

Book a Microsoft Access 2007 Course

Access Level 1

Access Level 2

Access Programming

Access 1 Week Intensive