Korol Julitta. Microsoft Access 2019 Programming by Example with VBA, XML, and ASP

Mercury Learning & Information, 2019-07-23. — 1106 p. — ISBN-13: 978-1-68392-403-6.Updated for Access 2019 and based on the bestselling editions from previous versions, Microsoft Access 2019 Programming by Example with VBA, XML and ASP 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 straightforward explanations, 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, and collections; 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 program Microsoft Access databases for Internet access with Active Server Pages (Classic ASP), HTML, and XML.Access VBA PRIMER
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 LocationGetting 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 the 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 WindowAccess 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 ConstantsAccess VBA Built-In and Custom Functions
Writing Function Procedures
Various Methods of Running Function Procedures
Specifying the Data Type for a Function’s Result
Passing Arguments to 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 TypesAdding 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 ClauseAdding 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 LoopsKeeping 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 ArrayKeeping Track of Multiple Values Using Object Collections
Working with Collections of Objects
Declaring a Custom Collection
Adding Objects to a Custom Collection
Removing Objects from a Custom Collection
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 the Class Module
Creating the User Interface
Running the Custom Application
Watching the Execution of Your VBA ProceduresGetting to Know Built-In Tools for Testing and Debugging
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 OptionsAccess VBA PROGRAMMING WITH DAO AND ADO
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
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
Opening Microsoft Access Databases
Opening a Microsoft Jet Database in Read/Write Mode with DAO
Opening a Microsoft Jet Database in Read/Write Mode with ADO
Opening a Microsoft Access Database in Read-Only Mode with DAO
Opening a Microsoft Jet Database in Read-Only Mode with ADO
Opening a Microsoft Jet Database Secured with a Password
Opening a Microsoft Jet Database with User-Level Security
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 Using ADO
Creating a New Access Database
Creating a Database with DAO
Creating a Database with ADO
Copying a Database
Copying a Database with DAO
Copying a Database with FileSystemObject
Database Errors
Compacting a DatabaseCreating and Accessing Database Tables and Fields
Creating a Microsoft Access Table and Setting Field Properties (DAO Method)
Creating a Microsoft Access Table and Setting Field Properties (ADO Method)
Copying a Table
Deleting a Database Table
Adding New Fields to an Existing Table
Creating Calculated Fields
Creating Multivalue Lookup Fields with DAO
Creating Attachment Fields with DAO
Creating Append Only Memo Fields with DAO
Creating Rich Text Memo Fields with DAO
Removing a Field from a Table
Retrieving Table Properties
Retrieving Field Properties
Linking a Microsoft Access Table
Linking a dBASE Table
Linking a Microsoft Excel Worksheet
Listing Database Tables
Listing Tables and Fields
Listing Data Types
Changing the AutoNumberSetting Up Primary Keys, Indexes, and Table Relationships
Creating a Primary Key Index
Creating Indexes Using ADO
Creating Indexes Using DAO
Creating a Single-Field Index Using ADO
Adding a Multiple-Field Index to a Table Using DAO
Listing Indexes in a Table
Deleting Table Indexes
Creating Table Relationships Using ADOFinding 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
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
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 RecordsetWorking with Records
Adding a New Record with DAO
Adding a New Record with ADO
Adding Attachments
Adding Values to Multivalue Lookup Fields
Modifying a Record with DAO
Modifying a Record with ADO
Editing Multiple Records with ADO
Deleting a Record with DAO
Deleting a Record with ADO
Deleting Attachments
Copying Records to an Excel Worksheet
Copying Records to a Word Document
Copying Records to a Text File
Filtering Records Using the SQL WHERE Clause
Filtering Records Using the Filter Property
Sorting RecordsCreating and Running Queries with DAO/ADO
Creating a Select Query Manually
Creating a Select Query with DAO
Creating a Select Query with ADO
Executing an Existing Select Query with ADO
Modifying an Existing Query with ADO
Creating and Running a Parameter Query with DAO
Creating and Running a Parameter Query with ADO
Creating and Running a Make-Table Query with DAO
Creating and Running an Update Query with DAO
Executing an Update Query with ADO
Running an Append Query with DAO/ADO
Running a Delete Query with DAO
Creating and Running a Pass-Through Query with DAO
Creating and Executing a Pass-Through Query with ADO
Performing Other Operations with Queries
Retrieving Query Properties with DAO
Listing All Queries in a Database with DAO/ADO
Deleting a Query from a Database with DAO/ADO
Determining If a Query Is UpdatableUsing Advanced ADO/DAO 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
Shaped Recordsets with Grandchildren
Transaction Processing
Creating a Transaction with ADO
Creating a Transaction with DAOImplementing Database Security
Two Types of Security in Microsoft Access
Share-Level Security (in Access .accdb and .mdb File Formats)
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 CompactDatabase Method
Setting a Database Password Using the NewPassword Method
Changing a User Password
Encrypting a Secured MDB DatabasePROGRAMMING WITH THE JET DATA DEFINITION LANGUAGE
Creating, Modifying, and Deleting Tables and Fields
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 ColumnsEnforcing Data Integrity and Relationships between Tables
Using CHECK Constraints
Establishing Relationships between Tables
Using the Data Definition Query WindowDefining 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 IndexesDatabase 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 AccountViews 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 ProceduresENHANCING THE USER EXPERIENCE
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 ControlUsing 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
Timer
Events Recognized by Form Sections
DblClick (Form Section Event)
Understanding and Using the OpenArgs PropertyEvents Recognized by Controls
Enter (Control)
BeforeUpdate (Control)
AfterUpdate (Control)
NotInList (Control)
Click (Control)
DblClick (Control)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 ObjectAdvanced Event Programming
Sinking Events in Standalone Class Modules
Writing Event Procedure Code in Two Places
Responding to Control Events in a Class
Declaring and Raising EventsProgramming the User Interface
The Initial Microsoft Access 2019 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 2019 Ribbon Interface
Ribbon Programming with XML, VBA, and Macros
Creating the Ribbon Customization XML Markup
Loading Ribbon Customizations from an External XML Document
Embedding Ribbon XML Markup in a VBA Procedure
Storing Ribbon Customization XML Markup in a 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 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)VBA AND MACROS
Macros and Templates
Macros or VBA
Access 2019 Macro Security
Using the AutoExec Macro
Understanding Macro Actions, Arguments, and Program Flow
Creating and Using Macros in Access
Creating Standalone Macros
Running Standalone Macros
Creating and Using Submacros
Creating and Using Embedded Macros
Copying Embedded Macros
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 FormatTAKING YOUR VBA PROGRAMMING SKILLS TO THE WEB
Access and Active Server Pages
Introduction to Classic ASP
Creating an ASP Page
The ASP Object Model
Installing Internet Information Services (IIS)
Creating a Virtual Directory
Setting ASP Configuration Properties
Turning off Friendly HTTP Error Messages
Running Your First ASP Script
Retrieving Records
Breaking up a Recordset When Retrieving Records
Retrieving Records with the GetRows Method
Database Lookup Using Drop-Down Lists
Database Lookup Using a Multiple-Selection Listbox
Adding Data to a Table
Modifying a Record
Deleting a Record
Creating a Summary PageXML Features in Access
What Is XML
What Is a Well-Formed XML Document
XML Support in Access
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
Importing XML Data
Programmatically Exporting to and Importing from XML
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 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