Microsoft Access Training
Data Base Management with MS Access
You will learn how to use MS Excel with MS Access to handle large data.
Microsoft Access Training
Our Microsoft Access training courses will show you how to get the most from this powerful database application. Access is a popular database program enabling the user to manipulate data in a number of ways. Data can be analysed and sorted, specific information can be searched for, and the results can be used to produce reports, lists and more. Microsoft Access is extremely flexible and can be linked to data stored in other applications or to databases held in other programs (for example, MS SQL). Access is part of the Microsoft Office suite and so widely available. This also means that visual basic for applications (VBA) can be used to customise MS Access.
Led by highly experienced MS Access trainers with many years of experience our hands-on training courses mean that you will leave with practical experience as well as useful tips and tricks.
We run scheduled Access training courses at three levels and use Access 2016 on Windows for the training.
One-to-one or group bookings can also be arranged on dates to suit you and customised to your specific requirements. We are very happy to organise these courses anywhere.
Microsoft Access Base Training
This introductory Microsoft Access training course will teach you the fundamentals of using Access.
This course is instructor led, involves the use of a number of examples and exercises in a workshop environment to bring the classroom teaching to life. The course is led by an experienced Access trainer.
Duration: 2 days
Prerequisites
It is assumed that delegates have little or no knowledge of the software. However, it would be beneficial to have a general understanding of personal computers and the Windows operating system environment
Our two day Microsoft Access Introduction / Intermediate course will equip you with the skills to create your own databases to manage your information more effectively. By the end of this training course, students will be able to describe and create databases, access, view and process data using queries, produce forms for data entry and view data in report format. You will be a confident user of Access.
At the completion of this course you should be able to:
- understand how Access is used and how to navigate around it
- design a database with lookup tables
- create a database structure using Access
- modify the structure of an existing table
- set table relationships and join tables together
- add records to a new table
- add transactional records to a lookup database
- use various data validation features in Access to protect data
- work with the records in a database table
- format the data in a table
- sort and filter records in a table
- export records to and import records from a wide variety of sources and applications
- create simple and effective queries
- perform more advanced queries using a variety of querying techniques
- create queries based on one or more tables
- create and use parameter queries
- create calculated queries
- create meaningful reports from tables
- create and use forms.
GETTING TO KNOW ACCESS
- Understanding Microsoft Access
- Starting Access From the Windows Start Screen
- Starting Access From the Desktop
- Understanding the Start Screen
- Creating a New Blank Database
- Understanding the Backstage View
- Opening an Existing Database File
- Understanding the Access 2013 Screen
- Using the Ribbon
- Working With the Navigation Pane
- Adding Commands to the QAT
- Working With Touch Mode
- Working With a Table
- Working With Other Database Objects
- Closing a Database File
- Exiting From Access
DESIGNING A LOOKUP DATABASE
This session is focused on database design. Before creating a database it is critical to design it correctly for it to function efficiently. This session explained the different datatypes that Access can store before leading delegates though the process of designing and refining a database design.
The key topics trained include:
- Understanding How Access Stores Data
- Understanding Access Data Types
- Scoping Your New Database
- Identifying Table Problems
- Refining Table Structures
- Finalising the Design
CREATING A LOOKUP DATABASE
Having designed a database the next step is to actually create it. This session leads delegates through that process. So how to create a new file and then the table (or tables) within it. It then looks at creating and defining primary keys for the table created. The next part focuses on working with a multiple table database. It explains how relationships are defined, managed and created. Finally it shows you how to review the relationships within a database.
The key topics trained in this session are:
- Creating A New Database File
- Creating the Lookup Table
- Defining the Primary Key
- Saving and Closing A Table
- Creating the Transaction Table
- Understanding Lookup Table Relationships
- Connecting To A Lookup Table
- Viewing Table Relationships
MODIFYING TABLE STRUCTURES
Once you have created your database the next step is to define the datafield that are held within each of your tables. This session gives delegates a thorough overview of defining and working with table field properties.
The key topics trained include:
- Opening an Existing Table
- Adding Fields to an Existing Table
- Understanding Field Properties
- Changing Field Size
- Changing Field Names
- Changing Decimal Places
- Changing Date Formats
- Indexing Fields
- Deleting Fields From a Table
- Copying a Table Within a Database
- Deleting a Table From a Database File
SETTING TABLE RELATIONSHIPS
The relationships between tables are critical in an Access database. This session focuses on all aspects of those relationships. It explained how they work, how to view them and then how to create and edit table joins which are defined by table relationships. It finishes by showing you how to create a report that shows the relationships within a database.
The key topics trained include:
- Understanding Table Relationships
- Understanding Lookup Relationships
- Looking Up the Employees Table
- Looking Up the Expense Types Table
- Viewing Table Relationships
- Understanding Table Joins
- Editing the Employee Table Join
- Editing the Expense Type Table Join
- Creating a New Join
- Creating a Relationship Report
ADDING RECORDS TO A TABLE
Having established a database which contains a number of linked tables and in which the fields have been established it is time to start using your database to store data. This session focuses on entering data into a database. It shows you how to manually enter records, how to create and use a form to make data entry and how to save that form for future use. It finishes by showing you how to import data into Microsoft Access from Excel.
The key topics trained include:
- Typing Records In A Table
- Adding Records Using A Form
- Saving A Form Layout For Reuse
- Adding Records Using An Existing Form
- Assignment – Adding Records
- Importing From Microsoft Excel
ADDING TRANSACTIONAL RECORDS
- Typing Transactional Records
- Transactional Records Using A Form
- Assignment – Adding Transactional Records
- Adding Records Using A Subdatasheet
- Removing A Subdatasheet
- Inserting A Subdatasheet
DATA VALIDATION
The next step in creating a database is to ensure that only the correct types of data are entered into the various tables.In addition to helping to ensure that the correct data is entered into the right fields it also avoids a large number of potential problems later when incorrect data could cause problems with queries and other database functions. Access makes data validation straight forward luckily.
The key topics trained include:
- Assigning Default Values
- Validation Rules and Text
- Validating Numbers
- Setting Required Fields
- Working With Validations
WORKING WITH RECORDS
Once you have a populated database the next skill to master is how you work with and manipulate those records. This session shows you all of the basic skills. It shows you how to navigate around your table and how to edit the records that are held in your tables. It then moves to show you how to delete the records held in your table.
The next part of the session shows you how to search through the records held in your database, either the whole table or an individual field, and how to use ‘find and replace’. Finally it shows you how to print from your database.
The key topics trained include:
- Table Navigation
- Navigating to a Specific Record
- Editing a Record
- Deleting Record Data
- Undoing a Change
- Deleting a Record
- Deleting Several Records
- Searching in a Table
- Searching in a Field
- Finding and Replacing
- Printing Records From a Table
- Compacting a Database
FORMATTING TABLES
Formatting your tables will make working with your Access database far simpler and easier. This session covers the basics. It shows you how to change column widths, change fonts and formats with in a cell. It also covers moving, hiding and freezing columns. All of this makes navigating a database far simpler.
The key topics trained are:
- Changing Column Widths
- Formatting Cells In The Table
- Changing Fonts
- Moving Columns In A Table
- Freezing Columns In A Table
- Hiding Columns In A Table
- Unhiding Columns
SORTING AND FILTERING
Another very useful skill when working with a populated database is sorting and filtering the data in the database so that you can see only the records that you are interested in.
This session shows you the various ways that you can filter databases to isolate just the data that you want to seek. The key topics trained are:
- Simple Sorting
- Sorting On Several Fields
- Simple Filtering
- Working With Filters
IMPORTING & EXPORTING RECORDS
- Exporting Records to Microsoft Excel
- Exporting Records to a Text File
- Importing From Microsoft Excel
- Importing From a Text File
- Linking to an External Source
CREATING QUERIES
Often a simple filter will not allow you to isolate the data that you require. This session introduces the query functions in MS Access. It beings by explaining how they work before leading you though using the query designer. It shows you how to create a query, setup the various criteria to be applied to the data and how to run a query. It then covers saving a query so that it can be used agin in future.
The key topics trained in this session are:
- Understanding Queries
- Creating A Query Design
- Working With A Query
- Changing A Query Design
- Applying Record Criteria
- Clearing Selection Criteria
- Saving A Query
- Running Queries Via The Navigation Pane
- Deleting A Query
- Assignment – Creating Queries
QUERYING TECHNIQUES
Having introduced queries in the previous session this session builds on that to explain the more advanced functionality that Access offers.
It begins by showing delegates how to modify the query they saved at the end of the previous session. It then moves to looking at compound queries involving AND or OR logic before looking at querying for ranges and then sorting data in a query as well. It then moves to cover using wildcard characters in queries and looking at the characters that can cause problems in queries.
The key topics covered in this session are:
- Modifying A Saved Query
- Creating AND Queries
- Creating OR Queries
- Querying Numeric Data
- Querying Dates
- Using A Range Expression
- Querying Opposite Values
- Moving Fields In A Query
- Sorting Query Data
- Removing Fields From A Query
- Querying Using Wildcards
- Problem Characters
- Querying With A Lookup Table
- Sorting Query Data Numerically
- Displaying NULL Values
- Querying for Uniqueness
MULTI TABLE QUERIES
- Understanding Relational Queries
- Creating a Relational Query Design
- Filtering a Relational Query
- Filtering Related Fields
- Adding More Tables and Fields
- Utilising Hidden Fields
- Understanding Query Joins
- Creating an Inner Join
- Creating a Left Outer Join
- Creating a Right Outer Join
PARAMETER QUERIES
- Creating a Parameter Query
- Displaying All Using Parameters to Display a Range
- Using Parameters in Expressions
- Using Parameters With Wildcards
CALCULATIONS IN QUERIES
- Creating a Calculated Field
- Formatting Calculated Fields
- Summarising Data Using a Query
- Changing the Grouping
- Calculating With Dates
- Using Criteria in Calculations
- Concatenating String Fields
CREATING AND USING REPORTS
The final session of the introduction to MS Access course covers reporting. Having shown delegates how to create, populate and query their database the final topic is reporting.
Access contains some very powerful reporting tools. These allow reports on data to be created very quickly and simply. We cover creating, saving, editing, printing and deleting reports. After that we look at more advanced topics like creating statistical and grouped reports.
The key topics trained include:
- Understanding Reporting In Access
- Creating A Basic Report
- Working With Existing Reports
- Previewing and Printing A Report
- Changing The Report Layout
- Using The Report Wizard
- Creating A Grouped Report
- Creating A Statistical Report
- Working With Grouped Reports
CREATING AND USING FORMS
Forms are a very useful way to interact with your data in Access. This session gives covers forms and the actions that forms can be used for in detail. It covers creating forms, using forms with queries, editing data using forms and finally saving and deleting forms themselves.
The key topics trained include:
- Understanding Forms
- Creating A Basic Form
- Creating A Split Form
- Binding A Form To A Query
- Using The Form Wizard
- Working With Existing Forms
- Editing Records In A Form
- Deleting Records Through A Form
- Deleting An Unwanted Form
Microsoft Access Advanced Training
This advanced Microsoft Access training course will build on your existing skills to teach you advanced techniques for making the most of Access’s functionality.
Duration: 2 days
Prerequisites
A good working knowledge of Access is required; delegates should have an understanding of concepts covered in the Access Introduction/Intermediate course. These include creating and modifying table structures, creating a relational database, working with records, creating queries, forms and reports.
This is a two day Microsoft Access Advanced course.
At the completion of this course you should be able to:
- export records to and import records from a wide variety of sources and applications
- create and work with aggregation queries
- create and use a series of action queries
- modify and adapt an existing form according to specific needs
- create forms using a combination of wizards and manual techniques
- create a navigation form for a database in Access
- understand and use a variety of tools that ensure the wellness of your database files
- create and use macros in Access
- work with a number of macro techniques
- create and work with macros in forms
- create some simple programming code using VBA
- Importing From a Text File
- Linking to an External Source
IMPORTING AND EXPORTING RECORDS
This introductory session is a quick revision of all of the key concepts around importing and exporting data from Access databases to Excel and text files. We also cover using Access to interface with an external datasource.
The key concepts trained in this session include:
- Exporting Records to Microsoft Excel
- Exporting Records to a Text File
- Importing From Microsoft Excel
- Importing From a Text File
- Linking to an External Source
AGGREGATION QUERIES
This section covers more advanced queries. It stars covering aggregation queries. These queries do not present each item of information but the aggregation of this. For example, if you would like to know your total sales for the last month that would be an aggregation query as it would calculate all sales for last month and then aggregate them.
This session coves aggregation queries in detail. It covers working with simple and multiple aggregations, for simple and calculated fields. It then moves to looks at using queries to modify your data and also look at working with expressions in queries.
The key topics trained include:
- Creating an Aggregation Query
- Working With Aggregation Queries
- Multiple Aggregations
- Modifying Aggregation Headings
- Aggregating Calculated Fields
- Applying Criteria to Aggregates
- Understanding Nested Queries
- Creating an Assembly Query
- Creating the Nesting Query
ACTION QUERIES
- Creating a Make Table Query
- Using a Make Table Query
- Understanding Expressions and Update Queries
- Preparing an Update Query
- Running an Update Query
- Updating Using Expressions
- Running an Expression-Based Update
- Creating a Delete Query
- Running a Delete Query
- Creating an Append Query
- Running an Append Query
- Turning Action Messages Off
MODIFYING FORMS
This session looks at advanced form use in MS Access. It begins by looking at more advanced form design in Access, before looking at controls and tab orders in forms.
Forms are an essential way to make databases accessible to non-users by controlling and managing their access to the underlying tables.
The key skills that are trained include:
- Understanding Form Design and Layout
- Switching Between Form Views
- Selecting Form Objects
- Working With a Control Stack
- Changing Control Widths
- Moving Controls on a Form
- Aligning Controls
- Understanding Properties
- Changing Label Captions
- Adding an Unbound Control
- Adding a Control Source
- Formatting a Control
- Checking the Current Tab Order
- Changing the Tab Order
- Inserting the Date Into the Form Header
DIY FORMS
- Creating a Multiple Item Form
- Adjusting the Layout of a Form
- Typing Into a Form Layout
- Disabling Fields
- DIY Sub Forms
- Removing SubForm Redundancy
- Splitting and Merging Layout Cells
- Adding an Easy Lookup Field
- Adding Buttons to a Form
- Adjusting Form Properties
USING A NAVIGATION FORM
- Creating a Simple Navigation Form
- Creating a More Complex Navigation Form
- Creating the Navigation Form Page Tabs
- Creating Subsidiary Pages
- Assignment Adding Subsidiary Pages
- Editing Page Tab Names
- Applying a Theme
- Modifying Properties
- Automatically starting the Navigation Form
DATABASE WELLNESS
- Compacting and Repairing a Database
- Understanding Database Splitting
- Using the Database Splitter
- Managing Linked Tables
- Checking Object Dependencies
- Analysing Database Performance
- Documenting a Database
CREATING AND USING MACROS
As with all Microsoft Office products, MS Access allows you to record macros to automate repetitive tasks. This session covers the basics of Access macros. So creating, saving, editing and running a macro.
For experienced macro users in Excel or Word this will be relatively familiar. For people who are not experience marco users this can be an eye-opener when they learn how simple it can be to automate tasks in an Office program.
The key skills trained in this session are:
- Understanding Macros and VBA
- Creating a Macro
- Running a Macro
- Modifying an Existing Macro
- Interacting With the User
- Stepping Through a Macro
- Documenting Macros
MACRO TECHNIQUES
Having learned the basics of macros in the previous session this session covers some more advanced techniques. It looks at using macros to print work, working with message boxes to interact with a macro and other ways to get the most from macros.
The key concepts trained are:
- Creating a Print Macro
- Using Conditions to Enhance a Macro
- Creating a Sequence of Conditions
- Understanding the Versatility of MsgBox
- Using the MsgBox Function
- Reconfiguring a Message Box
- Using the InputBox Function
MACROS ON FORMS
The use of macros on user forms can be extremely powerful. It allows even non-technical users of Access databases to be able to access macros to automate repetitive tasks.
This session explains how this can be done, through event macros. It shows you want one is, how to create one and how to create a button to trigger a macro. It also walks delegates through how to create a search macro for MS Access.
The key topics trained are:
- Understanding Macros on Forms
- Creating Navigation Macros
- Accessing Event Macros
- Creating Unassigned Buttons
- Programming an Event
- Running an Event Macro
- Modifying an Event Macro
- Setting Echo Off
- Adding a Close Button
- Creating a Search Macro
- Running the Search Macro
- Understanding the Search Macro
- Naming Macros
- Referencing Macro Sheet Macros
AN INTRODUCTION TO VBA
- Understanding VBA
- Coding VBA for an Event
- Running VBA Event Code
- Understanding MsgBox and InputBox
- Modifying an Existing Procedure
- Understanding the Modified Code
- Creating a Standard Module
- Loan Simulator Code
- Running the Code
- Understanding the Loan Simulator Code
- Creating a Button for the Simulator
- Attaching the Simulator Code
DATABASE SECURITY
- Understanding Database Trust
- Enabling Blocked Content
- Understanding the Trust Centre
- Creating a Trusted Location
- Opening a Database From a Trusted Location
- Encrypting a Database
- Decrypting and Opening a Database
- Removing a Database Password
- Understanding Package and Sign
- Creating a Self Signed Certificate
- Creating a Signed Package
- Extracting and Using a Signed Package
Microsoft Access VBA Training
This Microsoft Access VBA training course will teach you how to manipulate your Access database using VBA (Visual Basic for Applications) code, automating the database. Access VBA extends the features of the database, providing you with the full power of Visual Basic programming to manipulate Access objects.
This course is instructor led, involving the use of numerous examples and exercises in a workshop environment in order to practice the techniques and methods that have been taught.
Duration: 3 days
Prerequisites
This Access VBA course requires prior knowledge of Access up to the level in our Advanced Microsoft Access training course. No prior knowledge of Access macros or Visual Basic for Applications (VBA) is required.
This 3 day course will ensure that you go back to your office as a confident, relaxed Access VBA programmer. It will lead you through programming and controlling Access using VBA using multiple examples to reinforce the concepts that you are being taught.
THE VBA CODE EDITOR
This session concentrates on the VBA code editor that is contained within MS Access. Once you are comfortable with accessing the code editor it moves on to show you how to create the modules that you will use to hold your code. It then discusses the project explorer which you will work in when writing your VBA code, and also to look at sub-routines and commenting your VBA code.
The key concepts trained are:
- Two ways to create modules
- Using Project Explorer
- Basic subroutines and comments
MESSAGES AND VARIABLES
This session focuses on using message boxes to communicate with and get input from users and also on the basics of variables.
The key concepts trained in this session are:
- The all-powerful DoCmd object
- Displaying messages
- Getting input from users
- Using variables
CREATING FUNCTIONS
Functions are one of the key elements of a VBA program. Functions take input data, carry out an operation on that data and then return a value when they complete. A simple example of a function would be a calculator to work out the area of a rectangle. The function would require as inputs the two different lengths of the sides. It would multiply them together and return the area of the rectangle.
The input data into a VBA function are know as arguments which are passed through a function.
The key concepts trained in this session are:
- Passing arguments
- Returning values
VISUAL BASIC EXPLAINED
Now that you have been introduced to the nuts and bolts of VBA code this session steps back a little bit. It explains at a high level how VBA programs are organised and manipulated.
This session introduces delegates to the object model in Access and also to collections of objects. It also introduces methods and property as well as other parts of the VBA framework.
The key concepts trained in the is session are:
- Objects and collections
- Methods and properties
- The Access object model
- Getting help in VBA
ATTACHING CODE TO FORMS
Once you are comfortable writing VBA code the next step is to understand how to connect it to forms. This is the most user friendly way to access the power of VBA on a day-to-day basis. It allows non-programmers to access your code and also for you to run your code without opening the code editor.
The key concepts trained in this session are:
- Attaching code to buttons
- Attaching code to events
- Making code not form-specific
- Manipulating Form/Control Properties
LOOPING
Often within VBA code you want as certain task to be repeated many times. This is known as looping. This session covers looping in detail including it’s application to forms and controls.
The key concepts trained in this session are:
- The four types of loop
- Looping over all open forms
- Looping over controls
- Looping over Access Objects
MODIFYING QUERIES
SQL (Structured Query Language) is the core database querying language. This session introduces SQL and then shows delegates how to amend the SQL of an Access query. This gives you complete control over queries in Access.
The key concepted trained in this session are:
- Basic SQL statements
- Amending queries’ SQL
RECORDSETS
Recordsets, along with SQL, are the most common way for VBA developers to interact with a database. A recordset is simply just a collection of records. They may either have come from a table, a number of tables or more often be the result of a query.
This session leads you through using recordsets to interact with your database. It starts by running through the differences between the major ways of using recordsets, ActiveX Data Objects (ADO) and Data Access Objects (DAO) before looking at the practicalities of adding, deleting and editing data.
The key concepted trained are:
- Choosing ADO or DAO
- Adding data
- Editing data
- Deleting data
DEBUGGING AND ERROR HANDLING
Inevitably when you write VBA code in Access you will make errors. Learning to debug code is a key skill. The VBA code editor contains a number of tools to help you identify the pieces of your code that are causing the issues.
This session shows you the various methods that are available to you to help you isolate your problematic pieces of code.
The key concepts trained in this session are:
- Stepping through macros
- Setting breakpoints
- Trapping for errors
LINKING TO EXCEL AND WORD
The final session of this course covers working with Excel and Word. Inevitably as your programming skills develop you will want your Access code to interact with other Microsoft Office programs. Most like Excel and Word.
This session covers how you can use Access to access other applications and also how you can manipulate Excel from Access using your VBA code.
The key concepts trained are:
- Referencing other applications
- Manipulating Excel from Access