
Access 2021 / Microsoft 365 Programming by Example
Description
Alles über E-Books | Antworten auf Fragen rund um E-Books, Kopierschutz und Dateiformate finden Sie in unserem Info- & Hilfebereich.
- Contains 28 chapters loaded with illustrated "Hands-On" exercises and projects that guide you through the VBA programming language. Each example tells you exactly where to enter code, how to test it and run it.
- Explains how to store data for further manipulation in variables, arrays, collections, and in a VBA Dictionary object while teaching you to write both simple and complex VBA programming routines and functions.
- Teaches you how to programmatically create and access database tables and fields, enforce data integrity and relationships between tables.
- Includes a comprehensive disc with source code, supplemental files, and color screen captures (also available from the publisher for download).
More details
Other editions
Additional editions


Content
- 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
System requirements
File format: PDF
Copy-Protection: Adobe-DRM (Digital Rights Management)
System requirements:
- Computer (Windows; MacOS X; Linux): Install the free reader Adobe Digital Editions prior to download (see eBook Help).
- Tablet/smartphone (Android; iOS): Install the free app Adobe Digital Editions or the app PocketBook before downloading (see eBook Help).
- E-reader: Bookeen, Kobo, Pocketbook, Sony, Tolino and many more (only limited: Kindle).
The file format PDF always displays a book page identically on any hardware. This makes PDF suitable for complex layouts such as those used in textbooks and reference books (images, tables, columns, footnotes). Unfortunately, on the small screens of e-readers or smartphones, PDFs are rather annoying, requiring too much scrolling.
This eBook uses Adobe-DRM, a „hard” copy protection. If the necessary requirements are not met, unfortunately you will not be able to open the eBook. You will therefore need to prepare your reading hardware before downloading.
Please note: We strongly recommend that you authorise using your personal Adobe ID after installation of any reading software.
For more information, see our eBook Help page.