Access 2021 / Microsoft 365 Programming by Example: with VBA, XML, and ASP
- Length: 1132 pages
- Edition: 1
- Language: English
- Publisher: Mercury Learning and Information
- Publication Date: 2022-10-30
- ISBN-10: 1683928415
- ISBN-13: 9781683928416
- Sales Rank: #0 (See Top 100 Books)
Updated for Access 2021 and based on the bestselling editions from previous versions, Access 2021 / Microsoft 365 Programming by Example is a practical, how-to book on Access programming, suitable for readers already proficient with the Access user interface (UI). If you are looking to automate Access routine tasks, this book will progressively introduce you to programming concepts via numerous illustrated hands-on exercises. More advanced topics are demonstrated via custom projects. With concise and straightforwardexplanations, you learn how to write and test your programming code with the built-in Visual Basic Editor; understand and use common VBA programming structures such as conditions, loops, arrays, collections and dictionaries; code a “message box”; reprogram characteristics of a database; and use various techniques to query and manipulate your Access .mdb and .accdb databases. The book shows you how you can build database solutions with Data Access Objects (DAO) and ActiveX Data Objects (ADO); define database objects and manage database security with SQL; enhance and alter the way users interact with database applications with Ribbon customizations and event programming in forms and reports. You also learn how to use Access with XML and REST API. The book includes a comprehensive disc with source code, supplemental files, and color screen captures (also available from the publisher for download).
FEATURES:
- 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).
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
Donate to keep this site alive
To access the Link, solve the captcha.
1. Disable the AdBlock plugin. Otherwise, you may not get any links.
2. Solve the CAPTCHA.
3. Click download link.
4. Lead to download server to download.
Python for Data Science, 2nd Edition
2024-06-03
