Cover
Title
Copyright Page
Dedication
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 of Functions
Passing Arguments by Reference and by Value
Using Optional Arguments
Using the IsMissing Function
Using 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 Object 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)
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
Timing
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