Access 2021 / Microsoft 365 Programming by Example: with VBA, XML, and ASP

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