Skip to content
Tutorials Center
My learning

Topics

Microsoft Access Tutorial

Learn Microsoft Access step by step: 36 lessons in 9 chapters with examples, quizzes, diagrams and projects.

Start learning →

of lessons completed

Getting Started

  1. 1 What Is Microsoft Access? Learn what Microsoft Access is, what a desktop relational database does, which objects an Access file holds, and when to choose Access over Excel or SQL Server.
  2. 2 The Access Interface and Navigation Tour the Microsoft Access window: the ribbon tabs, Navigation Pane, tabbed documents, Datasheet, Design and Layout views, Tell Me search and useful keyboard shortcuts.
  3. 3 Creating Your First Access Database Create a blank Access database, choose a safe file location, build your first table, enter data, save, back up and reopen it. Includes a runnable SQL version.
  4. 4 Access File Formats, Templates and Trusted Locations Understand ACCDB, ACCDE, ACCDR and MDB files, start from templates, split a database, set up trusted locations and fix the yellow security warning bar.

Tables and Relationships

  1. 5 Creating Tables in Access: Datasheet and Design View Build Access tables three ways: Datasheet view, Design view and a SQL CREATE TABLE statement. Learn naming rules, descriptions, saving, renaming and editing safely.
  2. 6 Access Field Data Types Explained Learn every Access field data type: Short Text, Long Text, Number sizes, Currency, Date/Time, AutoNumber, Yes/No, Attachment, Hyperlink, Calculated and Large Number.
  3. 7 Field Properties, Validation Rules and Lookups in Access Control what goes into Access tables with Format, Input Mask, Default Value, Required, Validation Rule and Text, Allow Zero Length, Caption and Lookup combo boxes.
  4. 8 Primary Keys and Indexes in Access Choose primary keys in Access: AutoNumber, natural and composite keys. Add single and multiple-field indexes, enforce uniqueness and know what indexes cost.
  5. 9 Relationships and Referential Integrity in Access Link Access tables with one-to-many, one-to-one and many-to-many relationships, set referential integrity, cascade updates and deletes, and use junction tables.

Queries

  1. 10 Select Queries and Query Design View in Access Build Access select queries in Query Design view: pick fields, sort, hide columns, set Top Values and Unique Values, and read the SQL Access writes for you.
  2. 11 Criteria and Expressions in Access Queries Filter Access queries with criteria: comparison operators, And/Or rows, Like wildcards, Between, In, Is Null, date literals, calculated fields, IIf, Nz and Format.
  3. 12 Joins in Access Queries: Inner, Outer, Unmatched and Self Combine tables in Access queries with inner, left and right joins, find unmatched records, join on several fields, build self joins and avoid accidental cross joins.
  4. 13 Totals Queries, Grouping and Calculated Fields in Access Summarize data in Access with the Totals row: Group By, Sum, Count, Avg, Min, Max, Where and Expression, filtering groups, grouping on expressions and avoiding double-counting.
  5. 14 Parameter, Crosstab and Action Queries in Access Ask for input with parameter queries, pivot data with crosstab TRANSFORM, and change data safely with update, append, delete and make-table queries and union queries.

Forms and Reports

  1. 15 Creating Forms in Access: Wizards, Views and Record Sources Build Access forms with the Form button, Form Wizard, Split Form and Multiple Items. Learn Form, Layout and Design views, the Record Source, navigation and data entry settings.
  2. 16 Form Controls, Combo Boxes, Subforms and Buttons in Access Add and configure Access form controls: text boxes, combo boxes, option groups, subforms with link fields, calculated controls, command buttons and conditional formatting.
  3. 17 Creating Reports in Access: Wizards, Views and Sections Build Access reports with the Report button and Report Wizard. Learn Report, Layout, Design and Print Preview views, report sections, page setup, labels and PDF export.
  4. 18 Grouping, Sorting, Totals and Running Sums in Access Reports Use the Group, Sort and Total pane in Access reports: add group levels, subtotals and grand totals, percentages, running sums, keep-together, page breaks and subreports.

Macros and VBA

  1. 19 Macros in Access Automate Microsoft Access without code. Learn the Macro Builder, actions, conditions, submacros, embedded and standalone macros, AutoExec and data macros.
  2. 20 VBA Basics for Access Start programming Microsoft Access with VBA. Learn the VBA editor, modules, variables and data types, Sub and Function procedures, conditions and loops.
  3. 21 Events and DoCmd in Access Forms Make Access forms respond to users. Learn form and control events, their firing order, DoCmd methods, validation in BeforeUpdate and safe SQL from VBA.
  4. 22 DAO Recordsets and Error Handling Read and change Access data from VBA with DAO recordsets. Learn OpenRecordset, looping, AddNew and Edit, transactions, On Error, Err and a reusable error handler.

Data Import, Export and Linking

  1. 23 Importing Data into Access Bring data into Microsoft Access from Excel, CSV and text files. Learn the Import wizard, field types, saved imports, cleaning with append queries and fixing import errors.
  2. 24 Exporting Data from Access Export Microsoft Access tables, queries and reports to Excel, CSV, PDF, Word and XML. Learn the Export wizard, saved exports, DoCmd methods and which format to pick.
  3. 25 Linked Tables and ODBC in Access Link Access to Excel, other Access files and SQL Server through ODBC. Learn linked versus imported data, the Linked Table Manager, DSN-less connections and pass-through queries.
  4. 26 Automating Excel, Word and Outlook from Access Control other Office programs from Access VBA. Learn late and early binding, creating an Excel report, filling a Word template, sending Outlook email and cleaning up objects.

Design and Normalization

  1. 27 The Database Design Process Design an Access database before you build it. Gather requirements, find entities and attributes, draw relationships, choose keys and naming conventions, and test with sample data.
  2. 28 Normalization in Access Normalize an Access database step by step. See update, insert and delete anomalies, then fix a flat orders table with 1NF, 2NF and 3NF, and learn when to denormalize.
  3. 29 Keys, Indexes and Data Integrity in Access Protect Access data with primary keys, composite keys, unique and multi-field indexes, referential integrity with cascade options, validation rules and required fields.

Administration and Security

  1. 30 Compact, Repair and Backup in Access Keep an Access database healthy. Learn why files bloat, how Compact and Repair works, automatic compact on close, backup strategies, version control for the front end and recovery steps.
  2. 31 Access Security and Trusted Locations Secure a Microsoft Access database. Learn the Trust Center and trusted locations, database encryption with a password, ACCDE files, hiding objects, SQL injection and user-level design.
  3. 32 Split Databases and Multi-User Access Share an Access database safely with several users. Learn the front-end/back-end split, the Database Splitter, record locking modes, conflicts, LDB lock files and performance tips.
  4. 33 Upsizing Access to SQL Server Move your Access data to SQL Server while keeping the Access front end. Learn when to upsize, data type mapping, the SSMA tool, linking, rowversion, pass-through queries and testing.

Projects and Practice

  1. 34 Project: A Library Database in Access Build a complete library system in Access. Design tables for books, members and loans, enforce integrity, write overdue and availability queries, then add forms, a report and VBA checks.
  2. 35 Project: Order Management in Access Build an order management database in Access with customers, products, orders, order lines and stock. Calculate totals and invoices, update stock with VBA and report on sales.
  3. 36 Access Practice Set and Troubleshooting Test yourself with a practice set for Microsoft Access: SQL exercises with solutions, Access SQL versus standard SQL, design questions, common errors and a checklist.