Microsoft Access 2021 Advanced: Expert Database Management

Learn primary keys and indexing guidelines in the Night Movies actor table, including choosing indexed fields, tradeoffs between fast queries and updates, and practical examples.

Delve into composite and auto indexing in Access, including ignore nulls and primary keys, then learn how compact and repair boosts database performance.

Set up the Northwind database template in Access 2021, log in to the sample database, and prepare data sheet view for upcoming lessons.

Explore data sheet view in Access, update dates with an SQL file or find and replace, and master record navigation, addition, and deletion using keyboard shortcuts.

Customize data sheet view in the Northwind Traders orders table by formatting, reordering columns, adjusting grid lines and fonts, and applying universal or table-specific changes.

Explore advanced sorting and filtering in datasheet view, performing single or multi-column sorts, using composite filters and filter by form, and adding a total row for calculations.

Explore how to add, view, and edit sub data sheets in data sheet view to display tours for trips, using one-to-many relationships and update propagation.

Add a sub data sheet to the genre table to expand genres like biopic and display the corresponding movie titles, using a query.

Link external access tables and use the linked table manager to refresh or relink when files move. Create one-to-many relationships with linked data, noting that referential integrity cannot be enforced.

Acquire and install SQL Server Express, then set up open database connectivity with ODBC to access data using Access 2021. Load Wide World Importers sample data to demonstrate cross-database access.

Restore the Worldwide Importers database from backup in SQL Server Express. Use SSMS to attach and browse, create a DSN, and link data to Access 2021 via ODBC.

Create a new Access database with linked tables dimension_customer and dimension_supplier from Worldwide Importers database using a DSN after restoring the database in SQL Server to practice past two lessons.

Split an access database into front end and back end to share data, reduce network traffic, and simplify security; learn the split process, back-end password, and selecting a shared location.

Convert a shared table to a local table in the front end to give users control, then back up data and save in compiled ACD format.

Address multi-user data sharing in Access by explaining record locking, opening mode, and advanced client settings. Explore default locking options, page versus record locking, and practical maintenance scenarios.

Turn version 22 of the Knight Movies database into exercise three by splitting it into front end and back end, relinking tables, and selecting the 32- or 64-bit front end.

Explore basics of macros in Microsoft Access, compare macros with VBA, build and run a simple macro that displays hello world, and edit macros to automate repetitive tasks.

Explore how autoexec macros manage startup in Northwind Traders, including login dialog, navigation pane control, and displaying or hiding categories.

Master macro security settings in Microsoft Access 2021 by configuring trust center options, including macro settings, trusted documents, trusted locations, and trusted publishers.

Copy Knight Movies version 23, link the front end to the back end, and build a startup welcome form with an autoexec macro and a settings table.

Customize the quick access toolbar, ribbon, and tabs in Access to speed up work by adding or removing commands, showing labels, and building a browse tab with sort and filter.

Learn to convert macros to VBA in Access 2021, compare macros and VBA, and navigate the VBA editor and properties window to automate tasks and enhance transportability of code.

Learn to run VBA code and handle errors in Microsoft Access 2021 Advanced, covering the VBA editor, modules, compiling, on error go to, and debugging with comments.

Create a vba standard module and write subs and functions. Use intellisense to declare variables like a, b, and c, and perform arithmetic with constants, dim, and assignment statements.

Master VBA scope and naming in access, including module level, private vs public, and Hungarian notation and Leszynski conventions, plus intellisense tips, string handling, concatenation with & and continuation characters.

Master VBA standard functions, conditions, and branching with case statements and loops in Access, using date and weekday functions, for next and do while loops.

Learn how to add and customize events in the assets database using access events, a timer for the welcome screen, and VBA code to control forms.

Recreate exercise five by linking front end to back end, close the splash screen via double-click, set a clapperboard background, and display version info as shown in the exercise files.

Explore form and report events in access and learn the order of events using help files. Apply an on current event example with VBA to suppress the splash screen caption.

Explore exercise six in the Knight Movies database to implement a switchboard with hidden navigation pane, separate front and back ends, embedded macro, and meaningful code and control naming.