Advanced Microsoft Access for Microsoft 365
Advanced Microsoft Access for Microsoft 365 is a course that takes Access further with macros and VBA, from controlling forms with code and handling data through DAO and SQL to splitting the database and protecting it as an .accde file. It suits Access users with basic to intermediate skills, database staff and IT teams, and you leave able to build a ready-to-use database system in Access.
- Duration12 hours · 2 days
- FormatOnsite / live online
- Next roundOn request
- CertificateIncluded
Course overview
As an Access database grows, jobs that used to be done step by step become repetitive and error-prone, several users change the design by accident, and a single file shared on the network risks being damaged. Macros and VBA let Access work automatically, check data before it is saved and turn a database file into a ready-to-use system that ordinary users can run easily and safely.
This course builds on the basic level. It starts with embedded macros and data macros, then moves into VBA: referring to objects on forms and reports, writing code for events and controls, working with data through DAO recordsets and SQL, and handling errors. Learners then build condition-based search and reporting, split the database into a front end and a back end, create a custom ribbon and protect the database as an .accde file, and finish with a workshop that builds a complete ready-to-use database system. It works with both Access in Microsoft 365 and Access 2024. (2 days, 6 hours per day, 12 hours in total, Advanced level.)
What you’ll gain
- Choose between macros, data macros and VBA for each kind of task
- Write VBA correctly with variables, conditions and loops, using Option Explicit
- Refer to and control objects, controls and events on forms and reports with code
- Read, add, edit and delete data with DAO recordsets and SQL statements
- Handle errors and debug code with the tools in the VBA editor
- Split a database into a front end and a back end and link tables from SQL Server
- Build a ribbon and a start-up screen, and protect the system as an .accde file for hand-over to users
Who this course is for
- Access users at basic to intermediate level who want to automate their systems
- Staff who look after their department's Access databases and want to make them safer
- IT staff and developers who maintain or modify existing Access systems
- Teachers, lecturers and students who want to take database programming further
Prerequisites
- Able to build tables, relationships, queries, forms and reports in Access, or completed a basic Access course
- An understanding of basic programming ideas, such as variables and conditions, will help you learn faster
- Able to read English commands on the program screen reasonably well
- A Windows laptop with Access from Microsoft 365 or Access 2024 (Access is not available on macOS)
Curriculum
Course Details
This is the 2026 edition of the Advanced Microsoft Access 2016/2019 course. It runs for 2 days, 6 hours per day (12 hours in total, 09:00-16:00), as lectures with labs built on a sample sales database, at Advanced level, and follows on from a basic Access course. The content has been rewritten for Access in Microsoft 365 and Access 2024, uses DAO recordsets as the main data method, and adds data macros, front-end and back-end splitting, linked SQL Server tables and VBA code signing. Access runs on Windows only, and learners use their own or their company's copy. Learners take home the course handbook, sample databases and VBA code, and the database system they build in the workshop.
Day 1 From Macros to VBA and Working with Data in Code
Section 1: Lab: Embedded Macros and Data Macros
- The Macro Builder and the actions and arguments used most often
- Embedded macros on buttons and form events, and conditional macros with If
- Data macros: Before Change and After Insert for checking and logging data at table level
- Lab: build a button that opens a form and a data macro that logs price changes
Section 2: Lab: VBA Basics in Access
- The VBA editor, Project Explorer, the Immediate window and converting macros to VBA
- Sub and Function procedures, data types, variables, constants and variable scope
- Option Explicit, operators, and the If, Select Case, For and Do Loop structures
- Lab: write a discount calculation function and test it in the Immediate window
Section 3: Lab: Objects, Properties and Events on Forms and Reports
- The Access object model: Forms, Reports, Controls and Me
- Read and set properties in code, and use methods such as Requery and SetFocus
- The order of key form events, such as Load, Current, BeforeUpdate and AfterUpdate
- Lab: check data before saving with BeforeUpdate and cancel when it is incomplete
Section 4: Lab: Controlling Controls with Code
- Text boxes, combo boxes, list boxes, option groups and check boxes
- Cascading combo boxes whose list changes with the value selected
- DoCmd: OpenForm, OpenReport and passing conditions with WhereCondition
- Lab: build a form that filters products automatically by the chosen category
Section 5: Lab: Working with Data through DAO Recordsets and SQL
- CurrentDb, recordsets, looping through records and searching with FindFirst
- Add, edit and delete records with AddNew, Edit, Update and Delete
- Run SQL with CurrentDb.Execute and parameterised QueryDefs
- How ADO differs from DAO, and when to choose each
- Lab: save an order and reduce product stock in code
Section 6: Lab: Error Handling and Debugging
- Syntax errors, runtime errors and logic errors
- On Error GoTo, Err.Number and messages that users can understand
- Breakpoints, Step Into, the Watch window and the Locals window
- Lab: add error handling to the order-saving code and test the failure cases
Day 2 Building a Ready-to-Use System for Hand-over
Section 7: Lab: Your Own Functions and Standard Modules
- Keep reusable code in standard modules and pass values with parameters
- User-defined functions that can be called from queries, forms and reports
- Date and text functions, and domain functions such as DLookup and DSum
- Lab: build a function that issues document numbers automatically
Section 8: Lab: Condition-Based Search and Reports
- A multi-condition search form that builds SQL from the values users enter
- Changing the Filter and RecordSource of forms and reports in code
- The new SQL view in Microsoft 365 for checking SQL statements
- Export reports to PDF and data to Excel with DoCmd.OutputTo
- Lab: a sales search form by date range and customer, with a PDF export button
Section 9: Lab: Splitting the Database and Linking SQL Server
- The Database Splitter: front end and back end for multi-user work
- The Linked Table Manager, and relinking tables in code when files move
- Link tables from SQL Server through ODBC, and performance points to watch
- Lab: split the sample database and test it from the front end
Section 10: Lab: The Ribbon and the System Start-up Screen
- Navigation forms and setting the start-up form in Access Options
- Hide the Navigation Pane, the standard ribbon and default shortcut menus from everyday users
- Build a ribbon with XML stored in the USysRibbons table and run code through callbacks
- Lab: create a ribbon for the sales system with buttons that open forms and reports
Section 11: Lab: Security and System Hand-over
- Encrypt the database with a password and set trusted locations
- Create an .accde file to protect forms, reports and code from changes
- Sign VBA code with a digital signature and the related Trust Center settings
- Compact and Repair, backups, and delivering new front-end versions to users
- Lab: make an .accde file of the sample system and test it with ordinary user rights
Section 12: Workshop: A Ready-to-Use Database System
- Workshop: plan a system from a real brief, such as a supplies requisition system or a customer register
- Write code to save, check and search data, with error handling
- Build the ribbon, start-up screen and monthly report
- Split the front end and back end, then create an .accde file for hand-over
Schedule & training options
For individuals — public rounds
No public rounds are open right now. Join the waiting list and we will contact you first when the next round opens, or ask us on LINE. Or call 02-570-8449 or 088-807-9770
For organisations — in-house / private
- Tailor the content to your team’s tools and projects
- Your dates, at your office or live online
- Quotation with tax ID for procurement
Instructors
Frequently asked questions
What is the difference between macros and VBA in Access, and which should you use?
A macro strings together ready-made actions without writing code, which suits simple jobs such as opening a form or printing a report when a button is clicked. A data macro is attached directly to a table, so it suits checking or logging data every time a record changes. For work that needs variables, complex conditions, loops, error handling and recordset access, use VBA. Access can also convert existing macros into VBA code as a starting point.
What is the difference between DAO and ADO in Access VBA?
DAO is the library designed for Access databases, using CurrentDb and recordsets to read, add, edit and delete data in Access tables and linked tables, so it is the main choice for most work. ADO is a general library for connecting to external data sources and suits some cases, such as connecting directly to another database through a connection string. This course uses DAO as the main method and compares it with ADO so you know when to use each.
Why split an Access database into a front end and a back end?
When several people open the same Access file on a network, the file is more likely to be damaged and the system is hard to update. Splitting it so the back end holds only the tables on a shared location, while each user has a front end with the queries, forms, reports and code, reduces the risk of damage, lets you deliver new versions without touching the data, and makes it easier to move the tables to SQL Server later.
Who is the Advanced Microsoft Access for Microsoft 365 course for, and what should I know beforehand?
It suits Access users at basic to intermediate level who want to automate their systems, staff who look after departmental databases, IT staff who maintain existing Access systems, and teachers, lecturers and students who want to go further. You should already be able to build tables, queries, forms and reports in Access, basic programming ideas will help you learn faster, and you need a Windows laptop with Access from Microsoft 365 or Access 2024, as Access is not available on macOS.
What will I be able to do after the Advanced Microsoft Access for Microsoft 365 course?
You will be able to write macros, data macros and VBA that control forms and reports, work with data through DAO recordsets and SQL, handle errors, build condition-based search and reports, split the database into a front end and a back end, create a custom ribbon and deliver the system as an .accde file. Participants who complete the training receive a Certificate of Completion with its own number, which can be verified online.
Can the Advanced Microsoft Access for Microsoft 365 course be run in-house for our team?
Yes. It can be run in-house for your team, with exercises adapted to your own work, such as using the structure of an Access database your department already uses as the workshop brief. You can request a quotation from this page, or contact us on LINE @itgenius or by phone on 02-570-8449.
Related articles
Articles are published in Thai.