Access 2021 / Microsoft 365 Programming by Example E-Book

Mercury Learning and Information

• Herausgeber: Packt Publishing
• Kategorie: Fachliteratur
• Sprache: Englisch

Seitenzahl: 1377

Veröffentlichungsjahr: 2024

Beschreibung

Updated for Access 2021 and based on bestselling editions, this book is a practical guide on Access programming, perfect for users proficient with the Access UI. It introduces programming concepts through illustrated hands-on exercises and custom projects. You will learn to write and test code with the Visual Basic Editor, use VBA structures like conditions, loops, arrays, collections, and dictionaries, and reprogram database characteristics.
The book covers building database solutions with Data Access Objects (DAO) and ActiveX Data Objects (ADO), defining database objects, and managing database security with SQL. It explores enhancing user interaction with Ribbon customizations and event programming in forms and reports. Additionally, you'll learn to use Access with XML and REST API.
From getting started with Access VBA to advanced event programming and macros, this book ensures a comprehensive understanding of Access programming. It transitions readers from basic to advanced topics, equipping them with the knowledge and tools to automate Access routine tasks effectively.

Leseprobe

Access® 2021/Microsoft 365Programming by Example

LICENSE, DISCLAIMER OF LIABILITY, AND LIMITED WARRANTY

By purchasing or using this book (the “Work”), you agree that this license grants permission to use the contents contained herein, but does not give you the right of ownership to any of the textual content in the book or ownership to any of the information or products contained in it. This license does not permit uploading of the Work onto the Internet or on a network (of any kind) without the written consent of the Publisher. Duplication or dissemination of any text, code, simulations, images, etc. contained herein is limited to and subject to licensing terms for the respective products, and permission must be obtained from the Publisher or the owner of the content, etc., in order to reproduce or network any portion of the textual material (in any media) that is contained in the Work.

Mercury Learning and Information (“MLI” or “the Publisher”) and anyone involved in the creation, writing, or production of the companion disc, accompanying algorithms, code, or computer programs (“the software”), and any accompanying Web site or software of the Work, cannot and do not warrant the performance or results that might be obtained by using the contents of the Work. The author, developers, and the Publisher have used their best efforts to insure the accuracy and functionality of the textual material and/or programs contained in this package; we, however, make no warranty of any kind, express or implied, regarding the performance of these contents or programs. The Work is sold “as is” without warranty (except for defective materials used in manufacturing the book or due to faulty workmanship).

The author, developers, and the publisher of any accompanying content, and anyone involved in the composition, production, and manufacturing of this work will not be liable for damages of any kind arising out of the use of (or the inability to use) the algorithms, source code, computer programs, or textual material contained in this publication. This includes, but is not limited to, loss of revenue or profit, or other incidental, physical, or consequential damages arising out of the use of this Work.

The sole remedy in the event of a claim of any kind is expressly limited to replacement of the book, and only at the discretion of the Publisher. The use of “implied warranty” and certain “exclusions” vary from state to state, and might not apply to the purchaser of this product.

The companion files are also available for downloading by writing to the publisher at [email protected].

Access® 2021/Microsoft 365Programming by Example

Julitta Korol

Copyright ©2022 by Mercury Learning and Information LLC. All rights reserved.

This publication, portions of it, or any accompanying software may not be reproduced in any way, stored in a retrieval system of any type, or transmitted by any means, media, electronic display or mechanical display, including, but not limited to, photocopy, recording, Internet postings, or scanning, without prior permission in writing from the publisher.

Publisher: David PallaiMercury Learning and Information 22841 Quicksilver Drive Dulles, VA 20166 [email protected] www.merclearning.com [phone]

Julitta Korol. Access 2021/Microsoft 365 Programming by Example. ISBN: 978-1-68392-841-6

This book is printed on acid-free paper in the United States of America.

The publisher recognizes and respects all marks used by companies, manufacturers, and developers as a means to distinguish their products. All brand names and product names mentioned in this book are trademarks or service marks of their respective companies. Any omission or misuse (of any kind) of service marks or trademarks, etc. is not an attempt to infringe on the property of others.

Library of Congress Control Number: 2022940572

Our titles are available for adoption, license, or bulk purchase by institutions, corporations, etc.For additional information, please contact the Customer Service Dept. at 800-232-0223(toll free).

All of our titles are available in digital format at academiccourseware.com and other digital vendors. Companion files for this title are also available by contacting [email protected].

The sole obligation of Mercury Learning and Information to the purchaser is to replace the disc, based on defective materials or faulty workmanship, but not based on the operation or functionality of the product.

To a new generation of Microsoft Access programmers

CONTENTS

Acknowledgments

Introduction

PART I    ACCESS VBA PRIMER

Chapter 1   Getting Started with Access VBA

Understanding VBA Modules and Procedure Types

Writing Procedures in a Standard Module

Executing Your Procedures

Understanding Class Modules

Events, Event Properties, and Event Procedures

Why Use Events?

Walking Through an Event Procedure

Compiling Your Procedures

Placing a Database in a Trusted Location

Summary

Chapter 2    Getting to Know Visual Basic Editor (VBE)

Understanding the Project Explorer Window

Understanding the Properties Window

Understanding the Code Window

Other Windows in the VBE

Assigning a Name to the VBA Project

Renaming a Module

Syntax and Programming Assistance

List Properties/Methods

Parameter Info

List Constants

Quick Info

Complete Word

Indent/Outdent

Comment Block/Uncomment Block

Using the Object Browser

Using the VBA Object Library

Using the Immediate Window

Summary

Chapter 3    Access VBA Fundamentals

Introduction to Data Types

Understanding and Using Variables

Declaring Variables

Specifying the Data Type of a Variable

Using Type Declaration Characters

Assigning Values to Variables

Forcing Declaration of Variables

Understanding the Scope of Variables

Procedure-Level (Local) Variables

Module-Level Variables

Project-Level Variables

Understanding the Lifetime of Variables

Using Temporary Variables

Creating a Temporary Variable with a    TempVars Collection Object

Retrieving Names and Values of TempVar Objects

Using Temporary Global Variables in Expressions

Removing a Temporary Variable from a    TempVars Collection Object

Using Static Variables

Using Object Variables

Disposing of Object Variables

Finding a Variable Definition

Determining the Data Type of a Variable

Using Constants in VBA Procedures

Intrinsic Constants

Summary

Chapter 4    Access VBA Built-In and Custom Functions

Writing Function Procedures

Running Function Procedures

Data Types and Functions

Passing Arguments By Reference and By Value

Using Optional Arguments

Using the IsMissing Function

VBA Built-In Functions for User Interaction

Using the MsgBox Function

Returning Values from the MsgBox Function

Using the InputBox Function

Converting Data Types

Summary

Chapter 5    Adding Decisions to Your Access VBA Programs

Relational and Logical Operators

If. . .Then Statement

Multiline If. . .Then Statement

Decisions Based on More than One Condition

If. . .Then. . .Else Statement

If. . .Then. . .ElseIf Statement

Nested If. . .Then Statements

Select Case Statement

Using Is with the Case Clause

Specifying a Range of Values in a Case Clause

Specifying Multiple Expressions in a Case Clause

Summary

Chapter 6    Adding Repeating Actions to Your Access VBA Programs

Using the Do. . .While Statement

Another Approach to the Do. . .While Statement

Using the Do. . .Until Statement

Another Approach to the Do. . .Until Statement

Using the For. . .Next Statement

Using the For Each. . .Next Statement

Exiting Loops Early

Nested Loops

Summary

Chapter 7    Keeping Track of Multiple Values Using Arrays

Understanding Arrays

Declaring Arrays

Array Upper and Lower Bounds

Initializing and Filling an Array

Filling an Array Using Individual Assignment Statements

Filling an Array Using the Array Function

Filling an Array Using the For. . .Next Loop

Using a One-Dimensional Array

Arrays and Looping Statements

Using a Two-Dimensional Array

Static and Dynamic Arrays

Array Functions

The Array Function

The IsArray Function

The Erase Function

The LBound and UBound Functions

Errors in Arrays

Parameter Arrays

Passing Arrays to Function Procedures

Sorting an Array

Summary

Chapter 8    Keeping Track of Multiple Values Using Collections

Creating Your Own Collection

Adding Items to Your Collection

Determine the Number of Items in Your Collection

Accessing Items in a Collection

Removing Items from a Collection

Updating Items in a Collection

Returning a Collection from a Function

Collections vs. Arrays

Watching the Execution of Your VBA Procedures

Summary

Chapter 9    Getting to Know Built-In Tools for Testing and Debugging

Syntax, Runtime, and Logic Errors

Stopping a Procedure

Using Breakpoints

Removing Breakpoints

Using the Immediate Window in Break Mode

Using the Stop Statement

Using the Assert Statement

Using the Add Watch Window

Removing Watch Expressions

Using Quick Watch

Using the Locals Window

Using the Call Stack Dialog Box

Stepping Through VBA Procedures

Stepping Over a Procedure

Stepping Out of a Procedure

Running a Procedure to Cursor

Setting the Next Statement

Showing the Next Statement

Navigating with Bookmarks

Stopping and Resetting VBA Procedures

Trapping Errors

Using the Err Object

Procedure Testing

Setting Error-Trapping Options

Summary

PART II     ACCESS VBA PROGRAMMING WITH DAO AND ADO

Chapter 10    Data Access Technologies in Microsoft Access

Understanding Database Engines: Jet/ACE

Understanding Access Versions and File Formats

Understanding Library References

Overview of Object Libraries in Microsoft Access

The Visual Basic for Applications Object Library (VBA)

The Microsoft Access 16.0 Object Library

OLE Automation

The Microsoft Office 16.0 Access Database Engine Object Library

The Microsoft DAO 3.6 Object Library

The Microsoft ActiveX Data Objects 6.1 Library (ADO)

Creating a Reference to the ADO Library

Understanding Connection Strings

Using ODBC Connection Strings

Creating and Using ODBC DSN Connections

Creating and Using DSN-Less ODBC Connections

Using OLE DB Connection Strings

Connection String via a Data Link File

Summary

Chapter 11    Creating and Manipulating Databases with DAO

Understanding the DBEngine and Workspace Objects

The DAO Errors Collection

Creating a Database with DAO

Copying a Database

Opening Microsoft Access Databases

Opening a Microsoft Jet Database in Read/Write Mode

Opening a Microsoft Access Database in Read-Only Mode

Opening a Microsoft Jet Database Secured with a Password

Creating and Accessing Database Tables and Fields

Creating a Microsoft Access Table and Setting Field Properties

Creating Calculated Fields

Creating Multivalue Lookup Fields

Creating Attachment Fields

Creating Append Only Memo Fields

Creating Rich Text Memo Fields

Removing a Field from a Table

Retrieving Table Properties

Linking a dBASE Table

Creating Indexes

Adding a Multiple-Field Index to a Table

Finding and Reading Records

Introduction to DAO Recordsets

Opening Various Types of Recordsets

Opening a Snapshot and Counting Records

Retrieving the Contents of a Specific Field in a Table

Moving Between Records in a Table

Finding Records in a Table-Type Recordset

Finding Records in Dynasets or Snapshots

Finding the nth Record in a Snapshot

Working with Records

Adding a New Record

Adding Attachments

Adding Values to Multivalue Lookup Fields

Modifying a Record

Deleting a Record

Deleting Attachments

Copying Records to an Excel Worksheet

Filtering Records Using the SQL WHERE Clause

Filtering Records Using the Filter Property

Creating and Running Queries

Creating a Select Query Manually

Creating a Select Query with DAO

Creating and Running a Parameter Query

Creating and Running a Make-Table Query

Creating and Running an Update Query

Running an Append Query

Running a Delete Query

Creating and Running a Pass-Through Query

Performing Other Operations with Queries

Retrieving Query Properties with DAO

Listing All Queries in a Database with DAO

Deleting a Query from a Database

Determining If a Query Is Updatable

Transaction Processing

Creating a Transaction

Summary

Chapter 12    Creating and Manipulating Databases with ADO

Creating an Access Database with ADO

Copying a Database

Copying a Database with FileSystemObject

Database Errors

Opening a Microsoft Jet Database in Read/Write Mode

Connecting to the Current Access Database

Opening Other Databases, Spreadsheets, and Text Files from Access

Connecting to an SQL Server Database

Opening a Microsoft Excel Workbook

Opening a Text File

Creating a Microsoft Access Table and Setting Field Properties

Copying a Table

Deleting a Database Table

Adding New Fields to an Existing Table

Removing a Field from a Table

Retrieving Table Properties

Retrieving Field Properties

Linking a Microsoft Access Table

Linking a Microsoft Excel Worksheet

Listing Database Tables

Listing Tables and Fields

Listing Data Types

Changing the AutoNumber

Creating a Primary Key Index

Creating Indexes Using ADO

Creating a Single-Field Index

Listing Indexes in a Table

Deleting Table Indexes

Creating Table Relationships

Introduction to ADO Recordsets

Cursor Types

Lock Types

Cursor Location

The Options Parameter

Opening a Recordset

Opening a Recordset Based on a Table or Query

Opening a Recordset Based on an SQL Statement

Opening a Recordset Based on Criteria

Opening a Recordset Directly with ADO

Moving Around in a Recordset

Finding the Record Position

Reading Data from a Field

Returning a Recordset as a String

Finding Records Using the Find Method

Finding Records Using the Seek Method

Finding a Record Based on Multiple Conditions

Using Bookmarks

Using Bookmarks to Filter a Recordset

Using the GetRows Method to Fill the Recordset

Working with Records in ADO

Adding a New Record

Modifying a Record

Editing Multiple Records

Deleting a Record

Copying Records to a Word Document

Copying Records to a Text File

Filtering Records

Sorting Records

Creating and Running Queries with ADO

Creating a Select Query with ADO

Executing an Existing Select Query with ADO

Modifying an Existing Query

Creating and Running a Parameter Query

Executing an Update Query

Creating and Executing a Pass-Through Query

Listing Queries in a Database

Deleting a Query

Using Advanced ADO Features

Fabricating a Recordset

Disconnected Recordsets

Saving a Recordset to Disk

Part 1: Saving a Recordset to Disk

Part 2: Creating an Unbound Access Form to    View and Modify Data

Part 3: Writing Procedures to Control the Form and Its Data

Part 4: Viewing and Editing Data Offline

Part 5: Connecting to a Database to Update the Original Data

Cloning a Recordset

Introduction to Data Shaping

Writing a Simple SHAPE Statement

Working with Data Shaping

Writing a Complex SHAPE Statement

Shaped Recordsets with Multiple Children

Shaped Recordsets with Grandchildren

Part 1: Creating a Form with a TreeView Control

Part 2: Writing an Event Procedure for the Form Load Event

Transaction Processing

Creating a Transaction

Examining the References Collection

Summary

PART III     ACCESS STRUCTURED QUERY LANGUAGE (SQL)

Chapter 13    Creating, Modifying, and Deleting Tables and Fields

Introduction to Access SQL

Creating Tables

Deleting Tables

Modifying Tables with DDL

Adding New Fields to a Table

Changing the Data Type of a Table Column

Changing the Size of a Text Column

Deleting a Column from a Table

Adding a Primary Key to a Table

Adding a Multiple-Field Index to a Table

Deleting an Indexed Column

Deleting an Index

Setting a Default Value for a Table Column

Changing the Seed and Increment Values of    AutoNumber Columns

Summary

Chapter 14    Enforcing Data Integrity and Relationships between Tables

Using CHECK Constraints

Establishing Relationships between Tables

Using the Data Definition Query Window

Summary

Chapter 15    Defining Indexes and Primary Keys

Creating Tables with Indexes

Adding an Index to an Existing Table

Creating a Table with a Primary Key

Creating Indexes with Restrictions

Deleting Indexes

Summary

Chapter 16    Views and Stored Procedures

Creating a View

Enumerating Views

Deleting a View

Creating a Stored Procedure

Creating a Parameterized Stored Procedure

Examining the Contents of a Stored Procedure

Executing a Parameterized Stored Procedure

Deleting a Stored Procedure

Changing Database Records with    Stored Procedures

Summary

PART IV     IMPLEMENTING DATABASE SECURITY

Chapter 17    Implementing Database Security with DDL

Two Types of Database Security

Setting the Database Password

Removing the Database Password

Creating a User Account

Changing a User Password

Creating a Group Account

Adding Users to Groups

Removing a User from a Group

Deleting a User Account

Granting Permissions for an Object

Revoking Security Permissions

Deleting a Group Account

Summary

Chapter 18    Implementing User-Level and Share-Level Security

Share-Level Security

User-Level Security

Understanding Workgroup Information Files

Creating and Joining Workgroup Information Files

Opening a Secured MDB Database

Creating and Managing Group and User Accounts

Deleting User and Group Accounts

Listing User and Group Accounts

Listing Users in Groups

Setting and Retrieving User and Group Permissions

Determining the Object Owner

Setting User Permissions for an Object

Setting User Permissions for a Database

Setting User Permissions for Containers

Checking Permissions for Objects

Setting a Database Password Using the    DBEngine.CompactDatabase Method

Setting a Database Password Using the NewPassword Method

Changing a User Password

Summary

PART V     VBA PROGRAMMING IN ACCESS FORMS AND REPORTS

Chapter 19    Enhancing Access Forms

Creating Access Forms

Grouping Controls Using Layouts

Rich Text Support in Forms

Using Built-In Formatting Tools

Using Images in Access Forms

Using the Attachments Control

Summary

Chapter 20    Using Form Events

Data Events

Current

BeforeInsert

AfterInsert

BeforeUpdate

AfterUpdate

Dirty

OnUndo

Delete

BeforeDelConfirm

AfterDelConfirm

Focus Events

Activate

Deactivate

GotFocus

LostFocus

Mouse Events

Click

DblClick

MouseDown

MouseMove

MouseUp

MouseWheel

Keyboard Events

KeyDown

KeyPress

KeyUp

Error Events

Error

Filter Events

Filter

ApplyFilter

Timing Events

Timer

Events Recognized by Form Sections

DblClick (Form Section Event)

Understanding and Using the OpenArgs Property

Summary

Chapter 21    Events Recognized by Form Controls

Enter (Control)

BeforeUpdate (Control)

AfterUpdate (Control)

NotInList (Control)

Click (Control)

DblClick (Control)

Chapter Summary

Chapter 22    Enhancing Access Reports and Using Report Events

Creating Access Reports

Using Report Events

Open

Close

Activate

Deactivate

NoData

Page

Error

Events Recognized by Report Sections

Format (Report Section Event)

Print (Report Section Event)

Retreat (Report Section Event)

Using the Report View

Sorting and Grouping Data

Saving Reports in .pdf or .xps File Format

Using the OpenArgs Property of the Report Object

Running Built-In Menu Commands from VBA

Creating a Report with VBA

Part I-Creating a Crosstab Query in the Query Design View

Part II-Creating a Query with VBA

Part III-Creating a Report with VBA

Part IV-Creating a Custom Form for the Query’s Parameters

Part V-Running the Form and Report

Summary

PART VI     ENHANCING THE USER EXPERIENCE

Chapter 23    Customizing the Menu System in Access

The Initial Access 2021 Window

Customizing the Navigation Pane

Using VBA to Customize the Navigation Pane

Locking the Navigation Pane

Controlling the Display of Database Objects

Setting Displayed Categories

Saving and Loading the Configuration of the Navigation Pane

A Quick Overview of the Access 2021 Ribbon Interface

Ribbon Programming with XML, VBA, and Macros

Creating the Ribbon Customization XML Markup

Loading Ribbon Customizations from an External XML Document

Part 1: Setting Access Options

Part 2: Setting Up the Programming Environment

Part 3: Writing VBA Code

Part 4: Calling the LoadRibbon Function from    an Autoexec Macro

Part 5: Applying the Customized Ribbon

Embedding Ribbon XML Markup in a VBA Procedure

Storing Ribbon Customization XML Markup in a Table

Assigning Ribbon Customizations to Forms and Reports

Part 1: Creating Ribbon Customization for a Report Using a Local System Table

Part 2: Making Access Aware of the New Customization

Part 3: Assigning a Ribbon Customization to a Report

Using Images in Ribbon Customizations

Requesting Images via the loadImage Callback

Part 1: Creating Ribbon Customization    for Loading Custom Images

Part 2: Setting Up the Programming Environment

Part 3: Writing the VBA Callback Procedures

Part 4: Making Access Aware of the New Customization

Requesting Images via the getImage Callback

Understanding Attributes and Callbacks

Using Various Controls in Ribbon Customizations

Creating Toggle Buttons

Creating Split Buttons, Menus, and Submenus

Creating Checkboxes

Creating Edit Boxes

Creating Combo Boxes and Drop Downs

Creating a Dialog Box Launcher

Disabling a Control

Repurposing a Built-in Control

Refreshing the Ribbon

The CommandBars Object and the Ribbon

Tab Activation and Group Auto-Scaling

Customizing the Backstage View

Customizing the Quick Access Toolbar (QAT)

Summary

PART VII     ADVANCED CONCEPTS IN ACCESS VBA

Chapter 24    Creating Classes in VBA

Important Terminology

Creating Custom Objects in Class Modules

Creating a Class

Variable Declarations

Defining the Properties for the Class

Creating the Property Get Procedures

Creating the Property Let Procedures

Creating the Class Methods

Creating an Instance of a Class

Event Procedures in Class Modules

Creating the User Interface

Running the Custom Application

Watching the Execution of Your Custom Object

Creating and Working with Collection Classes

The Collection Object

The Collection Class

Summary

Chapter 25    Advanced Event Programming

Sinking Events in Standalone Class Modules

Part 1: Database File Preparation

Part 2: Creating the cRecordLogger Class

Part 3: Creating an Instance of the Custom Class in the Form’s Class Module

Part 4: Testing the cRecordLogger Custom Class

Part 5: Using the cRecordLogger Custom    Class with another Form

Writing Event Procedure Code in Two Places

Responding to Control Events in a Class

Declaring and Raising Events

Summary

PART VIII     VBA AND MACROS

Chapter 26    Macros and Templates

Macros or VBA?

Access 2021 Macro Security

Using the AutoExec Macro

Understanding Macro Actions, Arguments, and Program Flow

Creating and Using Macros in Access 2021

Creating Standalone Macros

Running Standalone Macros

Creating and Using Submacros

Creating and Using Embedded Macros

Copying Embedded Macros

Examining Shadow Properties

Using Data Macros

Creating a Data Macro

Creating a Named Data Macro

Editing an Existing Named Macro

Calling a Named Macro from Another Macro

Using ReturnVars in Data Macros

Tracing Data Macro Execution Errors

Error Handling in Macros

Using Temporary Variables in Macros

Converting Macros to VBA Code

Converting a Standalone Macro to VBA

Converting Embedded Macros to VBA

Access Templates

Creating a Custom Blank Database Template

Understanding the .accdt File Format

Summary

PART IX     WORKING TOGETHER: VBA, XML, AND RESTAPI

Chapter 27    XML Features in Access 2021

XML and Access

What Is a Well-Formed XML Document?

Exporting XML Data

Understanding the XML Data File

Understanding the XML Schema File

Understanding the XSL Transformation Files

Viewing XML Documents Formatted with Stylesheets

Advanced XML Export Options

Data Export Options

Schema Export Options

Presentation Export Options

Applying XSLT Transforms to Exported Data

Import XML Data

Importing a Schema File

Importing an XML File

Part 1: Creating a Custom Transformation File to be Used After the XML Data Import

Part 2: Exporting the Customers and Related Orders Tables to an XML File

Part 3: Importing to an Access Database Only Two Columns from the Customers Table and Five Columns from the Orders Table

Programmatically Exporting to and Importing from XML

Exporting to XML Using the ExportXML Method

Transforming XML Data with the TransformXML Method

Part 1: Creating a Custom Stylesheet for Transforming an    XML Source File into Another XML Data File

Part 2: Writing a VBA Procedure to    Export and Transform Data

Part 3: Importing the Transformed XML Data File to Access

Part 4: Creating another transformation

Importing to XML Using the ImportXML Method

Manipulating XML Documents Programmatically

Loading and Retrieving the Contents of an XML File

Working with XML Document Nodes

Retrieving Information from Element Nodes

Retrieving Specific Information from Element Nodes

Retrieving the First Matching Node

Using ActiveX Data Objects with XML

Saving an ADO Recordset as XML to Disk

Attribute-Centric and Element-Centric XML

Changing the Type of an XML File

Applying an XSL Stylesheet

Transforming Attribute-Centric XML Data into an HTML Table

Loading an XML Document in Excel

Summary

Chapter 28    Access and REST API

Introduction to a VBA Dictionary Object

Accessing the VBA Dictionary

Adding a Reference to the Microsoft Scripting Runtime Library

Working with the Dictionary Object’s Properties and Methods

Dictionary versus Collection

Action Item 28.1

Introduction to Regular Expressions

Character Matching in RegExp Patterns

Quantifiers in RegExp Patterns

Using the RegExp Object in VBA

The RegExp Object Declaration

RegExp Properties

RegEx Methods

Writing VBA Programs Using the RegExp Object

Introduction to REST API

Accessing REST APIs with VBA

Methods and Properties of the XMLHTTPRequest Object

Making a Basic GET Request

Action Item 28.2

Overview of JSON

Loading JSON Data into Access

Parsing JSON with Third-Party Libraries

Summary

Appendix: Installing Internet Information Services (IIS)

Creating a Virtual Directory

Setting ASP Configuration Properties

Turning Off Friendly HTTP Error Messages

Index

Acknowledgments

First, I’d like to express my gratitude to everyone at Mercury Learning and Information. A sincere thank-you to my publisher, David Pallai, for offering me the opportunity to update this book to the new 2021 version and tirelessly keeping things on track during this long project.

A whole bunch of thanks go to the editorial team for working so hard to bring this book to print. In particular, I would like to thank Jennifer Blaney, for her production expertise. To the compositor, Swaradha Typesetters, for all the composition efforts that gave this book the right look and feel.

Special thanks to my husband, Paul, for his patience during this long project.

Finally, I’d like to acknowledge readers like you who cared enough to post reviews of the previous editions of this book online. Your invaluable feedback has helped me raise the quality of this work by including the material that matters to you most. Please continue to inspire me with your ideas and suggestions.

Julitta KorolJuly 2022

Introduction

Since its creation, Microsoft Access has allowed users to design and develop Windows-based database applications and has grown into the world’s most popular database. This book is for people who have already mastered the use of Microsoft Access databases and now are ready for the next step—programming. Access 2021/ Microsoft 365 Programming by Example takes nonprogrammers through detailed steps of creating Access databases from scratch and shows them how to retrieve and manage their data programmatically using various programming languages and techniques. With this book in hand, users can quickly build the toolset required for developing their own database solutions. With this book’s approach, programming an Access database from scratch and controlling it via programming code is as easy as designing and maintaining databases with the built-in tools of Access. This book gives a practical overview of many programming languages and techniques necessary in programming, maintaining, and retrieving data from today’s Access databases.

PREREQUISITES

You don’t need any programming experience to use Access 2021/Microsoft 365 Programming by Example. The only prerequisite is that you already know how to manually design an Access database and perform database tasks by creating and running various types of queries. This book also assumes that you know how to create more complex forms with embedded subforms, combo boxes, and other built-in controls. If you don’t have these skills, there are countless books on the market that can teach you step by step how to build simple databases. If you do meet these criteria, this book will take you to the Access programming level by example. You will gain working knowledge immediately by performing concrete tasks and without having to read long descriptions of concepts. True learning by example begins with the first step, followed by the next step, and the next one, and so on. By the time you complete all the steps in a hands-on exercise or a custom project, you should be able to effectively apply the same technique again and again in your own database projects.

HOW THIS BOOK IS ORGANIZED

This book is divided into nine parts (a total of 28 chapters) that progressively introduce you to programming Access databases.

PART I—ACCESS VBA PRIMER

Here you are introduced to Visual Basic for Applications (VBA)—the programming language for Microsoft Access. In this part of the book, you acquire the fundamentals of VBA that you will use repeatedly in building real-life Access database applications. Part I chapters are also the subject of a standalone book, Access 2021 Programming Pocket Primer, available from Mercury Learning and Information (ISBN: 9781683928898). If you already worked through the pocket primer book, you can skip chapters 1–9 and begin from Chapter 10.

Part I consists of the following nine chapters:

Chapter 1—Getting Started with Access VBAIn this chapter you learn about the types of Access procedures you can write and learn how and where they are written.

Chapter 2—Getting to Know Visual Basic Editor (VBE)In this chapter you learn almost everything you need to know about working with the Visual Basic Editor window, commonly referred to as VBE. Some of the programming tools that are not covered here are discussed and used in Chapter 9.

Chapter 3—Access VBA FundamentalsThis chapter introduces basic VBA concepts that allow you to store various pieces of information for later use.

Chapter 4—Access VBA Built-In and Custom FunctionsIn this chapter you find out how to provide additional information to your procedures and functions before they are run.

Chapter 5—Adding Decisions to Your Access VBA ProgramsIn this chapter you learn how to control your program flow with several different decision-making statements.

Chapter 6—Adding Repeating Actions to Your Access VBA ProgramsIn this chapter you learn how to repeat the same actions in your code by using looping structures.

Chapter 7—Keeping Track of Multiple Values Using ArraysIn this chapter you learn about static and dynamic arrays and how to use them for holding various values.

Chapter 8—Keeping Track of Multiple Values Using CollectionsIn this chapter you learn how you can maintain your items of data while your program is running by using a special type of object – the collection.

Chapter 9—Getting to Know Built-In Tools for Testing and DebuggingIn this chapter you begin using built-in debugging tools to test your programming code. You also learn how to add effective error-handling code to your procedures.

The above nine chapters will give you the fundamental techniques and concepts you will need to continue your Access VBA learning path. The skills obtained in Access VBA Primer are portable. They can be utilized in programming other Microsoft Office applications that also use VBA as their native programming language such as Excel, Word, PowerPoint, Outlook, and so on.

PART II—ACCESS VBA PROGRAMMING WITH DAO AND ADO

Here you are introduced to two sets of programming objects known as Data Access Objects (DAO) and ActiveX Data Objects (ADO) that enable Microsoft Access and other client applications to access and manipulate data. You learn how to use DAO and ADO objects in your VBA code to connect to a data source, as well as create, modify, and manipulate database objects.

Part II consists of the following three chapters:

Chapter 10—Data Access Technologies in Microsoft AccessIn this chapter you get acquainted with two database engines (Jet/ACE) that Access uses, as well as several object libraries that provide objects, properties, and methods for your VBA procedures.

Chapter 11—Creating and Manipulating Databases with DAOThis chapter demonstrates how to create, copy, link, and delete database tables programmatically by using objects from the DAO object library. Here you learn how to write code to add and delete fields as well as create listings of existing tables in a database and fields in a table. You add primary keys and indexes to your database tables and create relationships between your tables. Next you practice various methods of using programming code to open a set of database records, commonly referred to as a recordset. You learn how to move around in a recordset and find, filter, and sort the required records, as well as read their contents. Finally, you learn how to perform essential database operations such as adding, updating, and deleting records. You also learn how to render your database records in three popular file formats like Excel, Word, and a text file. Creating and running various types of database queries using VBA instead of the Query Design view is also covered in this chapter.

Chapter 12—Creating and Manipulating Database with ADO.This chapter demonstrates how to create, copy, link, and delete database tables programmatically by using objects from the ADO object library. Here you learn how to write code to add and delete fields as well as create listings of existing tables in a database and fields in a table. You add primary keys and indexes to your database tables. You also learn how to use objects from the ADOX library to create relationships between your tables. Next you practice various methods of using programming code to open a set of database records, commonly referred to as a recordset. You learn how to move around in a recordset and find, filter, and sort the required records, as well as read their contents. Finally, you learn how to perform essential database operations such as adding, updating, and deleting records. You also learn how to render your database records in three popular file formats like Excel, Word, and a text file. Creating and running various types of database queries using VBA instead of the Query Design view is also covered in this chapter. This chapter explains several advanced ADO features such as how to disconnect a recordset from a database, save it in a disk file, clone it, and shape it. You also learn about database transactions.

You will find the skills obtained in Part II of this book essential for accessing and manipulating Access databases.

PART III—ACCESS STRUCTURED QUERY LANGUAGE (SQL)

Here you are introduced to the Data Definition Language (DDL), an important component of the Structured Query Language (SQL). Like ADO and DAO, which were introduced in Part II, DDL is used for defining database objects (tables, views, stored procedures, primary keys, indexes, and constraints) and managing database security. In this part of the book, you learn how to use DDL statements with Access databases.

Part III consists of the following four chapters:

Chapter 13—Creating, Modifying, and Deleting Tables and FieldsIn this chapter you learn special Data Definition Language commands for creating a new Access database, as well as creating, modifying, and deleting tables. You also learn commands for adding, modifying, and deleting fields and indexes.

Chapter 14—Enforcing Data Integrity and Relationships between TablesHere you learn how to define rules regarding the values allowed in table fields to enforce data integrity and relationships between tables.

Chapter 15—Defining Indexes and Primary KeysHere you learn DDL commands for creating indexes and primary keys.

Chapter 16—Views and Stored Procedures   This chapter shows you how to work with two powerful database objects known as views and stored procedures. You learn how views are like SELECT queries, and how stored procedures can perform various actions like Access Action queries and Select queries with parameters.

The skills you learn in Part III of this book will allow you to create and manipulate your Access databases using SQL DDL statements. Numerous Access SQL DDL statements and concepts introduced here are important in laying the groundwork for moving into the client/server environment (porting your Microsoft Access database to SQL Server).

PART IV—IMPLEMENTING ACCESS DATABASE SECURITY

Here we focus on Securing Access databases in the .ACCDB and .MDB file formats.

Part IV consists of the following two chapters:

Chapter 17—Implementing Database Security with DDLIn this chapter you learn how to use DDL commands and ADO objects to manage share-level security in the Microsoft Access database. You learn how to quickly create, modify, and remove a database password, and how to manage user-level accounts.

Chapter 18—Implementing User-Level and Share-Level SecurityIn this chapter you learn about two types of security in Microsoft Access databases: share-level security that applies to both older (MDB) and new (ACCDB) Access databases, and user-level security that can only be used with .mdb files.

The skills learned in Part IV will allow you to build more secure multiuser Access database applications that preserve and protect both data and the application itself. With the understanding of elements of security available in both file formats, you can create robust database solutions that will be less vulnerable to the malicious attacks on your computers and entire networks. Be sure to work through both security chapters before deciding which method of security is best for your database application.

PART V—PROGRAMMING IN ACCESS FORMS AND REPORTS

Here you learn how to respond to events that occur in Access forms and reports. The behavior of Microsoft Access objects such as forms, reports, and controls can be modified by writing programming code known as an event procedure or an event handler. In this part of the book, you learn how you can make your forms, reports, and controls perform useful actions by writing event procedures in form and report class modules.

Part V consists of the following four chapters:

Chapter 19—Enhancing Access FormsThis chapter presents a quick overview of types of forms you can create with Access 2021 and types of formatting you can apply to make your forms more attractive. You learn how you can group form controls using the layouts, implement rich formatting in form controls, professionally format your forms using built-in themes, and enhance forms with images.

Chapter 20—Using Form EventsIn this chapter you learn the types of events that can occur on a Microsoft Access form and write event procedures to handle various form events.

Chapter 21—Events Recognized by Form ControlsIn this chapter you learn the types of events that can occur on a Microsoft Access form, and you learn how to write event procedures to handle various form events.

Chapter 22—Enhancing Access Reports and Using Report EventsIn this chapter you learn about many events that are triggered when an Access report is run. You write your own event procedures to specify what happens when the report is opened, activated/deactivated, or closed.

The skills acquired in Part V will help you create forms and reports that provide the desired functionality to your users thanks to the implementation of various events.

PART VI – ENHANCING THE USER EXPERIENCE

Since its 2007 release, Access like other Microsoft 365 applications, uses the Ribbon interface for its menu system. Knowing how the Ribbon works and how you can modify it to customize your Access databases will enhance the experience of your database users. We will cover this topic in one big chapter with numerous illustrated hands-on examples and programming code written in VBA and XML.

Chapter 23 – Customizing the Menu System in AccessThis chapter provides an overview of the programming elements available in the Ribbon and shows how you can customize the user interface (UI) in your Access database applications. You learn how to create XML Ribbon customization markup and load it in your database. You also learn how Ribbon customizations can be assigned to forms or reports.

The skills acquired in Part VI of this book will allow you to enhance and alter the way users interact with your database application.

PART VII—ADVANCED CONCEPTS IN ACCESS VBA

Microsoft Access offers numerous built-in objects that you can access from your VBA procedures to automate many aspects of your databases. You are not limited to using these built-in objects, however. VBA allows you to create your own objects and collections of objects, complete with their own methods and properties. In this part of the book, you learn how thinking in terms of objects can help you write reusable code that’s easy to maintain. In the next two chapters you’ll be working in a new type of module, known as a class module, creating, and using classes and responding to class events.

Chapter 24—Creating ClassesIn this chapter you will work with advanced VBA concepts: VBA classes, class objects, and collection classes.

Chapter 25—Advanced Event ProgrammingThis chapter teaches advanced concepts in event programming. You learn how to respond to events in standalone class modules to make your code more manageable and portable to other objects. You also learn how to create and raise your own events.

The skills acquired in Part VII of this book will allow you write VBA code that is more efficient, much easier to read and maintain, and can be reused in many places.

PART VIII—MACROS AND TEMPLATES

Here you are introduced to three types of macros that you can create in Access 2021. In addition, you learn how to convert macros to VBA and get started with built-in templates that extensively use macros. Writing VBA code is not the only way to provide rich functionality to your Access database users. Macros have long been used to enhance the user experience without users having to write any VBA code. Access 2021 Macro Builder allows you to include complex logic, business rules, and error handling in your macros.

Part VIII contains the following chapter:

Chapter 26—Macros and TemplatesThis chapter introduces you to using macros. We take a detailed look at macro security, work with three types of macros (standalone, embedded, and data macros), see examples of using variables in macros, and examine error-handling actions in macros. We also discuss working with the template format in Access 2021.

The skills acquired in Part VIII will allow you to correctly utilize many of the macros available in Microsoft provided database templates in your own custom Access applications.

PART IX—WORKING TOGETHER: VBA, XML AND REST API

Extensible Markup Language (XML) has long been the standard format for sharing data without regard for the originating application or the operating system. In this part of the book, you learn how XML is used in Access to bring external data to your database as well as provide your data to other applications. You also learn about the Rest APIs, the newest and the most flexible method of integrating applications. You will use your Access VBA and XML skills to make HTTP requests to a Webserver to retrieve data and integrate it with Access. In this process you are introduced to using JSON (JavaScript Object Notation), the most popular file format for storing and transporting data.

Part IX consists of the following two chapters:

Chapter 27—XML Features in Access 2021In this chapter you learn how to use the Extensible Markup Language (XML) with Access. You learn how to export Access data manually and programmatically to XML files, as well as import an XML file to Access and display its data in a table. You also learn how to use stylesheets and transformations to present Access data to users in a desired format.

Chapter 28—Access and REST APIThis chapter focuses on expanding your VBA skillset by covering topics such as working with a VBA Dictionary Object, using regular expressions, and calling a new type of a web service, known as REST API.

The skills acquired in Part IX will make your Access applications ready to integrate with any operating system or web-based platform.

APPENDIX—INSTALLING INTERNET INFORMATION SERVICES (IIS)

This Appendix walks you through the installation of a Web server application that is used in Chapter 27 Hands-On projects.

HOW TO WORK WITH THIS BOOK

This book has been designed as a tutorial and should be followed chapter by chapter.

As you read each chapter, perform the tasks that are described. Be an active learner by getting involved in the book’s hands-on exercises and custom projects. When you are completely involved, you learn things by doing rather than studying, and you learn faster. Do not move on to new information until you’ve fully grasped the current topic. Allow your brain to sort things out and put them in proper perspective before you move on. Take frequent breaks between your learning sessions, as some chapters in this book cover lots of material. Do not try to do everything in one sitting. It’s always better to divide the material into smaller units than attempt to master all there is to learn at once. However, never stop in the middle of a hands-on exercise; finish it before taking a break. After learning a particular technique or command, try to think of ways to apply it to your own work. As you work with this book, create small sample procedures for yourself based on what you’ve learned up to a particular point. These procedures will come in handy when you need to review the subject in the future or simply need to steal some ready-made code.

THE COMPANION FILES

The example files for all the hands-on activities in this book are available in the companion files included with this book and may also be downloaded by contacting the publisher at [email protected]. Digital versions of this title are available at academiccourseware.com and other digital vendors.

Access VBA Primer

P a r t   I

The Access VBA Primer is divided into nine chapters that progressively introduce you to programming Microsoft Access using the 2021 version of the product. These chapters present the fundamental techniques and concepts that you need to master before you can take further steps in Access programming.

Chapter 1   Getting Started with Access VBA

Chapter 2   Getting to Know Visual Basic Editor (VBE)

Chapter 3   Access VBA Fundamentals

Chapter 4   Access VBA Built-In and Custom Functions

Chapter 5   Adding Decisions to Your Access VBA Programs

Chapter 6   Adding Repeating Actions to Your Access VBA Programs

Chapter 7   Keeping Track of Multiple Values Using Arrays

Chapter 8   Keeping Track of Multiple Values Using Collections

Chapter 9   Getting to Know Built-in Tools for Testing and Debugging

Getting Startedwith Access VBA

C h a p t e r   1

Visual Basic for Applications (VBA) is the programming language built into all Microsoft® 365 applications, including Access®. In this chapter, you acquire the fundamentals of VBA that you will use over and over again in building real-life Access database applications.

WRITING PROCEDURES IN A STANDARD MODULE

As mentioned earlier, procedures are created and stored in modules. Access has two types of modules: standard module and class module. Standard modules are used to hold subprocedures and function procedures that can be run from anywhere in the application because they are not associated with any form or report.

Because we already have a couple of procedures to try out, let’s do a quick hands-on exercise to learn how to open standard modules, write procedures, and execute them.

NOTE

All code files and figures for the hands-on projects may be found in the companion files.

 Hands-On 1.1   Working in a Standard Module

Create a folder on your hard drive named C:\VBAAccess2021_ByExample_Primer.

Open Access and click Blank database. Type Chap01 in the File Name box and click the folder button to set the location for the database to the C:\VBAAccess2021_ByExample_Primer folder. Finally, click the Create button to create the specified database (see Figure 1.1). Access will create the database in its default .ACCDB format.

Figure 1.1.

Creating a blank desktop Access database.

To launch the programming environment, select the Database Tools tab and click Visual Basic (see Figure 1.2). You can also press Alt+F11 to get to this screen.

Figure 1.2.

Activating a Visual Basic development environment.

The screen that opens is your Visual Basic Environment, often referred to as VBE. All your coding will be performed in this screen. Before you can do your work here, you need to determine which module you need to work with. As mentioned earlier, we use a standard module for most general programming tasks. Initially nothing is open, so let’s add the first module.

Insert a standard module by choosing Module from the Insert menu (see Figure 1.3).

Figure 1.3.

Inserting a standard module.

Each module begins with a declaration section that lists various settings and declarations that apply to every procedure in the module. Figure 1.4 shows the default declaration. Option Compare Database specifies how string comparisons are evaluated in the module—whether the comparison is case-sensitive or insensitive. This is a case-insensitive comparison that respects the sort order of the database. This means that “a” is the same as “A.” If you delete the Option Compare Database statement, the default string comparison setting for the module is Option Compare Binary (used for case-sensitive comparisons where “a” is not the same as “A”).

Figure 1.4.

Standard module.

Another declaration (not shown here) called the Option Explicit statement is often used to ensure that all variables used within this module are formally declared. You will learn about this statement and variables in Chapter 4.

   Following the declaration section is the procedure section, which holds the module’s procedures. You can begin writing your procedures at the cursor position within the Module1 (Code) window.

In the Module1 (Code) window, enter the code of subroutines and function procedures as shown in Figure 1.5. These are the same sub procedures and functions that we’ve discussed so far in this chapter. You can write procedures and functions in the same module, or you can keep them separate by adding another standard module to your VBA project.

   Notice that Access inserts a horizontal line after each End Sub or End Function keyword to make it easier to identify each procedure. The Procedure drop-down box at the top-right corner of the Module1 (Code) window displays the name of the procedure in which the insertion point is currently located.

Figure 1.5.

Standard module with subprocedures and functions.

EXECUTING YOUR PROCEDURES

Now that you’ve filled the standard module with some procedures and functions, let’s see how you can run them. There are many ways of running (executing) your code. In the next hands-on exercise, you will execute your code in four different ways using:

Run menu (Run Sub/UserForm)

Toolbar button (Run Sub/UserForm)

Keyboard (F5)

Immediate window

 Hands-On 1.2   Running Procedures and Functions

Place the insertion point anywhere within the ShowMessage procedure. The Procedure box in the top-right corner of the Module1 (Code) window should display ShowMessage. Choose Run Sub/UserForm from the Run menu.

Access runs the selected procedure and displays the message box with the text “This is a message box in VBA.”

Click OK to close the message box. Try running this procedure again, this time by pressing the F5 key on the keyboard. Click OK to close the message box. If the Access window seems stuck and you can’t activate any menu option, this is often an indication that there is a message box open in the background. Access will not permit you to do any operation until you close the pop-up window.

Now, run this procedure for the third time by clicking the Run Sub/UserForm button () on the toolbar. This button has the same tooltip as the Run Sub/UserForm (F5) option on the Run menu

.

NOTE

Procedures that require arguments cannot be executed directly using the methods you just learned. You need to type some input values for these procedures to run. A perfect place to do this is the Immediate window, which is covered in detail in Chapter 2, “Getting to Know Visual Basic Editor (VBE).” For now, let’s open this window and see how you can use it to run VBA procedures.

Select Immediate Window from the View menu.

Access opens a small window and places it just below the Module1 (Code) window. You can size and reposition this window as needed. Figure 1.6 shows statements that you will run from the Immediate window in Steps 5–8.

Type the following in the Immediate window and press Enter to execute.

ShowMessage2 "I'm learning VBA."

Access executes the procedure and displays the message in a message box. Click OK to close the message box. Notice that to execute the ShowMessage2 procedure, you need to type the procedure name, a space, and the text you want to display. The text string must be surrounded by double quotation marks. In a similar way you can execute the ShowMessage3 procedure by providing two required text strings. For example, on a new line in the Immediate window, type the following statement and press Enter to execute:

ShowMessage3 "Keep on learning.", "John"

When you press the Enter key, Access executes the ShowMessage3 procedure and displays the text “John, your message is: Keep on learning.” Click OK to close this message box.

NOTE

You can also use the Call statement to run a procedure in the Immediate window. When using this statement, you must place the values of arguments within parentheses, as shown here:

Call ShowMessage3("Keep on learning.", "John")

Function procedures are executed using different methods. Step 6 demonstrates how to call the addTwoNumbers function.

On a new line in the Immediate window, type a question mark followed by the name of the function procedure and press Enter:

?addTwoNumbers

Access should display the result of this function (the number 5) on the next line in the Immediate window.

Now run the addTwoNumbers2 procedure. Type the following instruction in the Immediate window and press Enter:

?addTwoNumbers2(56, 24)

Access displays the result of adding these two numbers on the next line.

If you’d rather see the function result in a message box, type the following instruction in the Immediate window and press Enter:

MsgBox("Total=" & addTwoNumbers2(34,80))

Access displays a message box with the text “Total=114”.

NOTE

See Chapter 2 for more information on running your procedures and functions from the Immediate window.

Figure 1.6.

  Running procedures and functions in the Immediate window.

Now that you’ve familiarized yourself a bit with standard modules, let’s move on to another type of module known as the class module.

UNDERSTANDING CLASS MODULES

Class modules come in three varieties: standalone class modules, form modules, and report modules.

Standalone class modules—These modules are used to create your own custom objects with their own properties and methods. You create a standalone class module by choosing Insert | Class Module in the Microsoft Visual Basic for Applications window. Access will create a default class module named Class1 and will list it in the Class modules folder in the Project Explorer window. You will work with standalone class modules in Chapter 8.

Form modules—Each Access form can contain a form module, which is a special type of a class module.

Report modules—Each Access report can contain a report module, which is a special type of class module.

All newly created forms and reports are lightweight by design because they don’t have modules associated with them when they’re first created. Therefore, they load and display faster than forms and reports with modules. These lightweight forms and reports have their Has Module property set to No (see Figure 1.7). When you open a form or report in Design view and click the View Code button (