Access 2016 Advanced

Normalize non-normalized tables in Access 2016 using the table analyzer wizard, splitting a single table into related tables with primary keys and lookups.

Learn to diagnose normalization issues between employees and projects, set primary keys, and enforce referential integrity. Build a junction table and append queries to create properly related, normalized tables.

Create a junction table to connect employees and projects when no direct keys exist. Append combinations of EmployeeID and ProjectID, define a composite primary key, and establish two one-to-many relationships.

Back up your Access 2016 database using the backup database tool via file save as, saving copies with the date, and scheduling regular backups to protect data.

Learn how the compact and repair tool in Access 2016 optimizes database size and accuracy by defragmenting changes, with a demo of toggling compact on close.

Explore the object dependencies tool in a relational database to see what queries, forms, and reports depend on a table and which tables a query or form relies on.

Use the database documenter to generate a text backup of your database design, including tables, fields, and properties, enabling easy rebuilds or reference later.

Analyze your Access 2016 database with the performance analyzer to assess overall efficiency. Review the recommendations and suggestions, and choose to optimize with added indexes or ignore them.

Explore how to create and use a switchboard in Access, a multi-user solution built with interactive forms and command buttons to open forms, run reports, and restrict access.

Learn to create and manage Access 2016 switchboards with the Switchboard Manager, including a main and sub switchboards, and buttons to open forms, reports, and exit the application.

Modify the look and feel of a switchboard in design view, preserving text-based objects to avoid errors. Change colors, shadows, and layout, resize and move controls, and update form caption.

Configure Access 2016 startup options to auto-open the switchboard, hide the database window and navigation pane, and use overlapping windows for a streamlined user experience.

Split the database to enhance security by moving tables to a back-end and linking them to a front-end with forms, queries, and reports, potentially password-protecting the back-end.

Configure trusted locations in Access to treat databases in folders as trusted, enabling full access. Open file options, Trust Center settings, add a location, and enable trusted subfolders.

Open the backend database in exclusive mode, encrypt with a password to protect it, and require the correct password to access the database.

Protect your VBA modules in an Access 2016 database by opening the VBA editor, navigating to tools and database properties, then selecting protection to set a password.

Save your database as an accde file to create an executable only format that prevents design changes in forms, reports, and VBA, while keeping code functional.

Explore how VBA automates database tasks in Access, contrast macros with VBA, and navigate modules, procedures, and event procedures for forms and reports.

Create a VBA click event that exports the sales per category query to Excel, using variables, comments, and an if-then-else flow with vbYesNo prompts.

Export a table or query from Access using VBA and docmd.transferSpreadsheet, with an input box for the file name and saving to the default documents location.

Convert macros to VBA for your Access 2016 database, option to add error handling and comments, creating a new module that lets you call the function from other code.

Discover how table events in Access 2016 work by embedding event procedures directly in the table, covering before and after events like before change and after insert.

Explore how a before change event automatically updates a discount field (15%, 10%, 5%, or 0%) based on quantity thresholds using if/else logic before saving.

Set up an after update event to log unit price changes for each product ID, then view the application log in system objects to verify updates.

Review the course recap for Access 2016 advanced by exploring how to improve and maintain an Access database, create and use switchboards, configure multi-user access, and automate processes with VBA.