
Access 2024 / 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.
More details
Other editions
Additional editions

Person
Content
- Intro
- Title Page
- Copyrightpage
- Contents
- Acknowledgments
- Introduction
- List of Figures
- List of Tables
- Part I Access VBA Primer
- Chapter 1 Introduction to VBA Programming
- Statements, Commands, and Instructions
- Procedures and Modules
- Module Types
- Procedure Types
- Writing Procedures in a Module
- Executing Your Procedures
- Compiling and Saving Your Procedures
- Placing a Database in a Trusted Location
- VBA Data Types
- Understanding and Using Variables
- Declaring Variables
- Specifying the Data Type of a Variable
- Using Data 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
- Using Static Variables
- Using Object Variables
- Disposing of Object Variables
- Finding a Variable Definition
- Determining the Data Type of a Variable
- Using Constants
- Using ChatGPT with Access
- 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 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
- Using ChatGPT with Access
- Summary
- Chapter 3 Access VBA Procedures and Functions
- Writing Function Procedures
- Running Function Procedures
- Data Types and Functions
- Passing Arguments (ByRef and ByVal)
- Using Optional Arguments
- VBA Built-In Functions for User Interaction
- Using the MsgBox Function
- Returning Values from the MsgBox Function
- Using the InputBox Function
- Converting Data Types
- Using ChatGPT with Access
- Summary
- Chapter 4 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
- Using ChatGPT with Access
- Summary
- Chapter 5 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
- Using ChatGPT with Access
- Summary
- Chapter 6 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
- Using ChatGPT with Access
- Summary
- Chapter 7 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
- Using ChatGPT with Access
- Summary
- Chapter 8 Ge tting 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
- Working in a Code 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
- Using Toolbars in the VBE Window
- Using ChatGPT with Access
- Summary
- Part II Access VBA Programming with DAO and ADO
- Chapter 9 Data Access Technologies in Microsoft Access
- Introduction to Database Engines
- Types of Database Engines
- Understanding Access Versions and File Formats
- Understanding Library References
- Overview of Object Libraries in Microsoft Access
- The VBA Object Library
- 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 ADO 6.1 Library
- 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
- ODBC Connection Strings for Common Data Sources
- Using OLE DB Connection Strings
- Connection Strings via a Data Link File
- Using ChatGPT with Access
- Summary
- Chapter 10 Creating and Manipulating Databases with DAO
- Setting Up Your Environment for DAO Programming
- Exploring the DAO Object Model
- 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
- Opening Other Files with DAO
- Accessing Database Tables and Fields
- Creating an Access Table and Setting Field Properties
- Defining Various Types of Fields in an Access Table
- Removing a Field from a Table
- Retrieving Table Properties
- Linking a dBASE Table
- Creating Indexes
- Adding a Multiple-Field Index to a Table
- Introduction to DAO Recordsets
- Opening a Recordset and Transferring Data
- Finding and Reading Records with DAO
- Counting Records and Retrieving Field Values
- Using the Seek Method to Find Records in a Table-Type Recordset
- Using the Find Methods to Find Records in Snapshots and Dynasets
- Adding a New Record to a Table
- Adding and Deleting Attachments
- Adding Values to Multvalue Lookup Fields
- Modifying a Record
- Deleting a Record
- Filtering Records
- Copying Records to an Excel Worksheet
- Introduction to Queries
- Operators, Wildcards, and Predicates Used in Queries
- Creating a Select Query with DAO
- Creating and Running a Parameter Query
- Creating and Running a Make-Table Query
- Creating and Running Update, Append, and Delete Queries
- Creating and Running a Pass-Through Query
- Performing Other Operations with Queries
- Transaction Processing
- Creating a Transaction with DAO
- Using ChatGPT with Access
- Summary
- Chapter 11 Creating and Manipulating Databases with ADO
- Setting Up Your Environment for ADO Programming
- Creating an Access Database with ADO
- Copying a Database
- Copying a Database with FileSystemObject
- More About Database Errors
- Opening a Microsoft Jet Database in Read/Write Mode
- Connecting to the Current Access Database
- Opening Other Databases, Spreadsheets, and Text Files
- 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 Table
- Adding New Fields to an Existing Table
- Removing a Field from a Table
- Retrieving Table and Field Properties
- Linking a Microsoft Access Table
- Linking a Microsoft Excel Worksheet
- Listing Database Tables Using the Catalog Object
- Listing Tables and Fields Using the OpenSchema Method
- Listing Data Types
- Retrieving the Value and the Increment of the AutoNumber Field
- Creating a Primary Key Index
- 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
- Moving Around in a Recordset
- Finding the Record Position
- 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 the GetRows Method to Fill the Recordset
- Working with Records in ADO
- Adding and Modifying Records
- Editing Multiple Records
- Deleting a Record
- Copying Records to a Word Document
- Copying Records to a Text File
- Filtering and Sorting Records
- Creating and Running Queries with ADO
- Creating a Select Query with ADO
- Executing an Existing Select Query with ADO
- Modifying a Select Query
- Creating and Running a Parameter Query
- Executing an Update Query
- Creating and Running a Pass-Through Query
- Listing Database Queries
- Deleting a Query
- Using Advanced ADO Features
- Fabricating a Recordset
- Disconnected Recordsets
- Saving a Recordset to Disk
- 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 and Grandchildren
- Connection Pooling
- Transaction Processing
- Creating a Transaction in ADO
- Examining the References Collection
- Using ChatGPT with Access
- Summary
- Part III Access Structured Query Language (SQL)
- Chapter 12 Understanding and Using SQL Within Microsoft Access
- Overview of SQL and the Access SQL Dialect
- Key Differences Between SQL and Access SQL
- SQL Specification and Access SQL
- The Categories of SQL
- Using Joins in Access SQL
- Types of Joins
- Creating Tables
- Deleting Tables
- Using SQL Statements in the Query Design
- Using DDL Statements with 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
- Setting a Default Value for a Table Column
- Changing the Seed and Increment Values of AutoNumber Columns
- Working with Primary Keys and Indexes
- Adding a Primary Key to a Table
- Adding a Multiple-Field Index to a Table
- Deleting an Index and an Indexed Field
- Creating Indexes with Restrictions
- Understanding Table Constraints
- Using CHECK Constraints
- Establishing Relationships Between Tables
- Using the Data Definition Query Window
- Creating and Using 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
- Using ChatGPT with Access
- Summary
- Part IV Implementing Database Security in Microsoft Access
- Chapter 13 Security Measures in Access .accdb File Format Databases
- Database Password Protection in Access .accdb Databases
- Setting a Database Password Manually
- Using DAO to Set or Reset a Database Password
- Using ADO to Set or Reset a Database Password
- Removing a Database Password
- Implementing Custom User Authentication with VBA
- Other Features for Enhanced Database Security
- Using ChatGPT with Access
- Summary
- Chapter 14 Security Measures in Access .mdb File Format Databases
- Setting a Database Password
- Managing the Security and Permissions of Your Access MDB Databases
- Implementing User-Level Security
- Understanding WIFs
- Creating and Joining a WIF
- Opening a Secured MDB Database
- Managing User-Level Security with VBA
- Managing User-Level Security with DAO
- Creating a New User Account with DAO
- Creating a New Group Account with DAO
- Adding a User to a Group with DAO
- Listing All Groups and Users with DAO
- Deleting a User or Group Account or Users from Groups
- Working with Object Permissions Using DAO
- Setting Permissions for an Object Using DAO
- Checking Object Permissions Using DAO
- Denying Object Permissions Using DAO
- Managing User-Level Security with ADO (ADOX)
- Creating User and Group Accounts with ADO
- Deleting User and Group Accounts
- Listing User and Group Accounts Using ADO
- Listing Users in Groups Using ADO
- Setting and Retrieving User and Group Permissions Using ADOX
- Determining the Object Owner Using ADOX
- Setting User Permissions for an Object Using ADOX
- Setting User Permissions for a Database Using ADOX
- Setting User Permissions for a Container Using ADOX
- Checking Permissions for Objects Using ADOX
- Changing a User Password Using ADOX
- Managing User-Level Security with SQL Commands
- SQL Commands for Managing User-Level Security
- Creating a User and Granting and Revoking Permissions to Objects
- 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
- Correctly Opening a Secured Access MDB Database
- Using ChatGPT with Access
- Summary
- Part V VBA Programming in Access Forms and Reports
- Chapter 15 Using VBA to Interact with Forms and Form Controls
- Controlling Forms with Form Properties
- Referring to Forms and Their Controls
- Form Modules and Event Programming in Access
- Data Events
- The Current Event
- The BeforeInsert Event
- The AfterInsert Event
- The BeforeUpdate Event
- The AfterUpdate Event
- The Dirty Event
- The OnUndo Event
- The Delete Event
- The BeforeDelConfirm Event
- The AfterDelConfirm Event
- Focus Events
- The Activate Event
- The Deactivate Event
- The GotFocus Event
- The LostFocus Event
- Mouse Events
- The Click Event
- The DblClick Event
- The MouseDown Event
- The MouseMove Event
- The MouseUp Event
- The MouseWheel Event
- Keyboard Events
- The KeyDown Event
- The KeyPress Event
- The KeyUp Event
- Error Events
- The Error Event
- Filter Events
- The Filter Event
- The ApplyFilter Event
- Timing Events
- The Timer Event
- Events Recognized by Form Sections
- The DblClick (Form Section) Event
- Understanding and Using the OpenArgs Property
- Using Images in Access Forms
- Using the Attachments Control
- Using the DoCmd Object with Access Forms
- Opening Forms
- Closing Forms
- Moving Between Records
- Saving Forms
- Using Requery with Forms and Controls
- Forcing the Combo Box Dropdown on Form_Load
- Using ChatGPT with Access
- Summary
- Chapter 16 Using VBA to Interact with Reports and Report Control
- Creating Access Reports
- Report Modules, Properties, and Event Programming in Access Reports
- Report Properties
- 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
- Report Troubleshooting
- Using the OpenArgs Property of the Report Object
- Running Built-In Menu Commands from VBA
- Creating a Report with VBA
- Using ChatGPT with Access
- Summary
- Part VI Enhancing the User Experience
- Chapter 17 Programming the Ribbon Interface in Access
- Understanding and Exploring the Ribbon Interface
- Working with 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 2024 Ribbon Interface
- Ribbon Programming with XML, VBA, and Macros
- Tools for Ribbon Programming
- Creating the Ribbon Customization XML Markup
- Callback Procedures in Ribbon Programming
- Loading Ribbon Customizations from an External XML Document
- Embedding Ribbon XML Markup in a VBA Procedure
- Storing Ribbon Customization XML Markup in a System Table
- Assigning Ribbon Customizations to Forms and Reports
- Using Images in Ribbon Customizations
- Requesting Images via the loadImage Callback
- 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 Dropdowns
- Creating a Gallery Control
- 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)
- Using ChatGPT with Access
- Summary
- Part VII Advanced VBA Programming Concepts
- Chapter 18 Creating Classes, Objects, Properties, Methods, and Events
- Important Terminology
- Creating Custom Objects in Class Modules
- Creating a Class
- Class Variables
- Creating and Using Property Procedures
- Immediate Exit from Property Procedures
- Creating the Property Get and Property Let Procedures
- Defining the Scope of Property Procedures
- Creating the Class Methods
- Writing Event Procedures
- Writing the Form_Load Event Procedure
- Writing the lstMovies_AfterUpdate Event Procedure
- Writing the btnClose_Click Event Procedure
- Writing the btnUpdate_Click Event Procedure
- Writing the btnRemove_Click Event Procedure
- Writing the btnAdd_Click and btnSave_Click Event Procedures
- Effective Code Analysis
- Creating an Instance of a Class
- Creating and Working with Collection Classes
- The Collection Object
- The Collection Class
- Advanced Event Programming
- Sinking Events in Standalone Class Modules
- Writing Event Procedure Code in Two Places
- Responding to Control Events in a Class Module
- Declaring and Raising Events
- Using ChatGPT with Access
- Summary
- Part VIII VBA and Macros
- Chapter 19 Getting Comfortable with Access Macros and Templates
- Macros or VBA?
- Access 2024 Macro Security
- Using the AutoExec Macro
- Understanding Macro Actions, Arguments, and Program Flow
- Creating and Using Macros in Access 2024
- Creating Standalone Macros
- Running Standalone Macros
- Creating and Using Submacros
- Creating and Using Embedded Macros
- Copying Embedded Macros
- Examining Shadow Properties
- Working in Sandbox Mode
- Generating Macros Using the Command Button Wizard
- Understanding Data Macros
- Creating a Data Macro
- Creating a Named Data Macro
- Editing an Existing Named Data Macro
- Calling a Named Data Macro from Another Macro
- Using ReturnVars in Data Macros
- Tracing Data Macro Execution Errors
- Copying Macros
- 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 Template File Format
- Using ChatGPT with Access
- Summary
- Part IX Working Together: VBA, XML, and REST API
- Chapter 20 Using XML in Access
- XML and Access
- Structure of XML Documents
- Exporting XML Data from Access
- 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
- Importing XML Data to Access
- Importing a Schema File
- Importing an XML File
- Using VBA to Export and Import XML Documents
- Exporting to XML Using the ExportXML Method
- Transforming XML Data with the TransformXML Method
- 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 ADO 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
- Using ChatGPT with Access
- Summary
- Chapter 21 Access and REST API
- Introduction to VBA Dictionary Objects
- 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
- 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
- RegExp 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
- Overview of JSON
- Loading JSON Data into Access
- Parsing JSON with Third-Party Libraries
- Using ChatGPT with Access
- Summary
- Appendix A VBA Data Types
- Appendix B Access 2007-2024 File Formats
- Appendix C 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: Watermark-DRM (Digital Rights Management)
System requirements:
- Computer (Windows; MacOS X; Linux): Use the free software Adobe Reader, Adobe Digital Editions, or any other PDF viewer of your choice (see eBook Help).
- Tablet/Smartphone (Android; iOS): Install the free app Adobe Digital Editions or another reading app for eBooks, e.g., PocketBook (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 Watermark-DRM, a „soft” copy protection. This means that there are no technical restrictions to prevent illegal distribution. However, there is a personalised watermark embedded in the eBook that can be used to identify the purchaser of the eBook in the event of misuse and to provide evidence for legal purposes.
For more information, see our eBook Help page.