Expert Access™ 2007 Programming

Expert Access™ 2007 Programming

Released November 2007
Publisher(s): Wrox
ISBN: 9780470174029

Read it now on the O’Reilly learning platform with a 10-day free trial.

O’Reilly members get unlimited access to books, live events, courses curated by job role, and more from O’Reilly and nearly 200 top publishers.

Start your free trial

Book description

Expert Access 2007 Programming shows experienced developers how to create professional-level Access database applications. The authors—software engineers on the Access development team at Microsoft—show how to apply software engineering methodologies to Access application development. The book is organized to cover all phases of Access development.

The authors demonstrate techniques for creating Access controls, forms, and reports that help streamline development and produce more user-friendly applications. They also cover such overlooked areas as custom deployment and documentation. The book contains many useful code examples designed so they can be used with minimal modification.

Table of contents

  1. Copyright
  2. About the Authors
  3. Credits
  4. Acknowledgments
  5. Introduction
    1. Building Off-the-Shelf Applications
    2. A Little Background
      1. Whom This Book Is For
    3. What This Book Covers
    4. How This Book Is Structured
      1. Part I: Programming Access Applications
      2. Part II: Data Manipulation
      3. Part III: Interacting with the Application
      4. Part IV: Finalizing the Application
    5. What You Need to Use This Book
    6. Conventions
      1. Source
        1. This section includes the source code.
      2. Output
        1. This section lists the output:
    7. Source Code
    8. Errata
    9. p2p.wrox.com
  6. I. Programming Access Applications
    1. 1. Overview of Programming for Access
      1. 1.1. Writing Code for Access
        1. 1.1.1. The Access Object Model
        2. 1.1.2. The DAO Object Model
        3. 1.1.3. The ADO Object Model
        4. 1.1.4. Object-Oriented Thinking
      2. 1.2. Windows API Programming
      3. 1.3. Working with Managed Code
        1. 1.3.1. What Is Managed Code?
        2. 1.3.2. Versions of Visual Studio
        3. 1.3.3. Writing Managed Code Libraries to Use with Access
        4. 1.3.4. Referencing the Access Object Model from Managed Code
      4. 1.4. Referencing Other Applications
        1. 1.4.1. Discovering References
        2. 1.4.2. Adding References to Office Applications
      5. 1.5. Summary
    2. 2. Extending Applications Using the Windows API
      1. 2.1. Windows API Overview
        1. 2.1.1. Why Use API Functions
        2. 2.1.2. API Resources
        3. 2.1.3. Writing Declare Statements
          1. 2.1.3.1. How to Find API Functions
          2. 2.1.3.2. C to VBA Data Type Conversions
            1. 2.1.3.2.1. ByRef vs. ByVal
            2. 2.1.3.2.2. Strings
            3. 2.1.3.2.3. ANSI Strings
            4. 2.1.3.2.4. Wide Strings
            5. 2.1.3.2.5. Structures
            6. 2.1.3.2.6. Unions
        4. 2.1.4. Example Conversion
        5. 2.1.5. Error Messages from API Functions
      2. 2.2. Retrieving System Information
        1. 2.2.1. Return the User Name in Different Formats
        2. 2.2.2. Return the Computer Name in Different Formats
        3. 2.2.3. Retrieve the Command Line
      3. 2.3. Windows Shell Functions
        1. 2.3.1. Get the Path to a Special Folder
        2. 2.3.2. Show the Property Dialog Box for a File
        3. 2.3.3. Determine Whether a Computer Is Connected to the Internet
        4. 2.3.4. Determine Whether a User Is an Administrator
      4. 2.4. Controlling Processes and Threads
        1. 2.4.1. Determining the Current Process ID
        2. 2.4.2. Pausing Code Execution
        3. 2.4.3. Quitting a Process
        4. 2.4.4. Waiting for a Process to Complete
          1. 2.4.4.1. Create the Process
          2. 2.4.4.2. Waiting for the Process
          3. 2.4.4.3. Closing the Process
          4. 2.4.4.4. Putting It Together
          5. 2.4.4.5. Testing the Routine
      5. 2.5. Windows Vista Task Dialogs
        1. 2.5.1. Introduction to Task Dialogs
          1. 2.5.1.1. Modify the Access Manifest File
          2. 2.5.1.2. StrPtr
        2. 2.5.2. A Simple Message Box Replacement
        3. 2.5.3. More Complex Task Dialogs
          1. 2.5.3.1. Adding an Expanded Section
          2. 2.5.3.2. Adding a Footer Section
          3. 2.5.3.3. Setting Custom Button Text
          4. 2.5.3.4. Using Command Links
          5. 2.5.3.5. Adding Verification Text
          6. 2.5.3.6. Adding Radio Buttons
          7. 2.5.3.7. Adding Hyperlinks
        4. 2.5.4. Icons
      6. 2.6. Miscellaneous API Functions
        1. 2.6.1. Creating a GUID
        2. 2.6.2. ShellExecute Tricks
          1. 2.6.2.1. Sending E-mail Using the Default E-mail Client
          2. 2.6.2.2. Opening the Default Web Browser
      7. 2.7. Summary
    3. 3. Programming Class Modules
      1. 3.1. Overview of Class Modules
        1. 3.1.1. Why Use Class Modules?
          1. 3.1.1.1. Making Complex Tasks Easier
          2. 3.1.1.2. Code Reuse
          3. 3.1.1.3. Controlling Visibility
        2. 3.1.2. Class Module Members
          1. 3.1.2.1. Enumerations
            1. 3.1.2.1.1. Flags
            2. 3.1.2.1.2. Flag-Related Functions
          2. 3.1.2.2. Properties
            1. 3.1.2.2.1. Read-Only Properties
            2. 3.1.2.2.2. Write-Only Properties
            3. 3.1.2.2.3. Metadata Properties
          3. 3.1.2.3. Methods
          4. 3.1.2.4. Events
            1. 3.1.2.4.1. Initialize Event
            2. 3.1.2.4.2. Terminate Event
        3. 3.1.3. Code Reuse in VBA
          1. 3.1.3.1. Instancing Property
          2. 3.1.3.2. Wrapper Method
          3. 3.1.3.3. Instancing Property (Revisited)
      2. 3.2. Subclassing
        1. 3.2.1. Subclassing Access Forms and Reports
        2. 3.2.2. Sinking Form Events
          1. 3.2.2.1. Subclassing the Form_Error event
          2. 3.2.2.2. Subclassing the Form_Load Event
      3. 3.3. Interfaces
        1. 3.3.1. Inheritance in VBA
          1. 3.3.1.1. Writing an Interface
          2. 3.3.1.2. Implementing an Interface
          3. 3.3.1.3. Using an Interface
        2. 3.3.2. Specialization
        3. 3.3.3. Testing the Type of an Object
          1. 3.3.3.1. TypeOf Operator
          2. 3.3.3.2. TypeName Function
          3. 3.3.3.3. Determining Whether an Object Has Been Set
      4. 3.4. Collections
        1. 3.4.1. Writing the Standard Collection Class
          1. 3.4.1.1. Storage
          2. 3.4.1.2. Add Method
          3. 3.4.1.3. Item Method
          4. 3.4.1.4. Count Property
          5. 3.4.1.5. Remove Method
          6. 3.4.1.6. Initialization and Cleanup
          7. 3.4.1.7. Using the Collection Class
        2. 3.4.2. An Alternate Implementation for the Add Method
        3. 3.4.3. Setting a Default Member
        4. 3.4.4. For Each Enumeration
        5. 3.4.5. The ICollectionEx Interface
          1. 3.4.5.1. Clear Method
          2. 3.4.5.2. Contains Method
          3. 3.4.5.3. Find Method
          4. 3.4.5.4. ForEach Method
          5. 3.4.5.5. Testing the ICollectionEx Interface
      5. 3.5. Events
        1. 3.5.1. Why Write Custom Events?
        2. 3.5.2. Creating Custom Events
          1. 3.5.2.1. Event Keyword
          2. 3.5.2.2. RaiseEvent Keyword
          3. 3.5.2.3. Creating an Event That Can Be Cancelled
        3. 3.5.3. Listening for Custom Events
      6. 3.6. Design Considerations
        1. 3.6.1. Copying a Class Instance
        2. 3.6.2. Exposing the Interface of a Derived Type
        3. 3.6.3. Raising Errors
          1. 3.6.3.1. Using Err.Raise
          2. 3.6.3.2. Defining an Error Event
      7. 3.7. Summary
    4. 4. Debugging, Error Handling, and Coding Practices
      1. 4.1. Debugging
        1. 4.1.1. Using Watches
          1. 4.1.1.1. Adding a Quick Watch
          2. 4.1.1.2. Adding a Watch
          3. 4.1.1.3. Watch Types
            1. 4.1.1.3.1. Watch Expression
            2. 4.1.1.3.2. Break When Value Is True
            3. 4.1.1.3.3. Break When Value Changes
        2. 4.1.2. Using the Watches Window
        3. 4.1.3. Using the Immediate Window
        4. 4.1.4. Using the Locals Window
        5. 4.1.5. Viewing the Call Stack
        6. 4.1.6. Building a Call Stack Using Code
          1. 4.1.6.1. Creating the CallStack Class
          2. 4.1.6.2. Instrumenting Your Code
          3. 4.1.6.3. Programmatically Generating a Call Stack Using Error Handling
        7. 4.1.7. Assertions
          1. 4.1.7.1. Using Debug.Assert
          2. 4.1.7.2. Conditional Compilation
            1. 4.1.7.2.1. Private Conditional Compilation Constants
            2. 4.1.7.2.2. Public Conditional Compilation Constants
          3. 4.1.7.3. Writing a Better Assert Method
          4. 4.1.7.4. Using the DebugEx.Assert Method
        8. 4.1.8. Creating a Debug Build
          1. 4.1.8.1. Using the Command Line to Specify a Debug Build
          2. 4.1.8.2. Using a Table to Create a Debug Build
          3. 4.1.8.3. Using Conditional Compilation to Create a Debug Build
          4. 4.1.8.4. Using a Database Property to Create a Debug Build
      2. 4.2. Error Handling
        1. 4.2.1. Handling Errors Inline
        2. 4.2.2. Categorizing Errors
        3. 4.2.3. System Error Codes and HRESULTS
          1. 4.2.3.1. System Error Codes
          2. 4.2.3.2. HRESULT Error Codes
        4. 4.2.4. Creating an Error Handler Class
          1. 4.2.4.1. Enumerations
          2. 4.2.4.2. Private Data
          3. 4.2.4.3. Properties
          4. 4.2.4.4. Methods
        5. 4.2.5. Logging Errors
          1. 4.2.5.1. The ILog Interface
          2. 4.2.5.2. Logging to a Database Table
          3. 4.2.5.3. Logging to a Text File
          4. 4.2.5.4. Creating an Instance of the ILog Class
        6. 4.2.6. Using the Error Handling Class and Logging
      3. 4.3. Coding Practices
        1. 4.3.1. Readability
          1. 4.3.1.1. Comments
            1. 4.3.1.1.1. Module Headers
            2. 4.3.1.1.2. Procedure Headers
            3. 4.3.1.1.3. Other Comments
          2. 4.3.1.2. Formatting
          3. 4.3.1.3. Naming Conventions
        2. 4.3.2. Version Control
        3. 4.3.3. Refactoring
          1. 4.3.3.1. Modules
          2. 4.3.3.2. Procedures
        4. 4.3.4. Code Reviews
      4. 4.4. Summary
  7. II. Data Manipulation
    1. 5. Parsing Data
      1. 5.1. File Input/Output
        1. 5.1.1. Using VBA I/O statements
          1. 5.1.1.1. The Importance of Error Handling
          2. 5.1.1.2. Reading an Existing Text File
          3. 5.1.1.3. Opening a File for Random Access
          4. 5.1.1.4. Writing Data
        2. 5.1.2. Using FileSystemObject
        3. 5.1.3. Determining Whether a File Is ANSI, Unicode, or UTF-8
      2. 5.2. Splitting Strings
        1. 5.2.1. The VBA String Handling Functions
          1. 5.2.1.1. PeekToken
          2. 5.2.1.2. PeelToken
        2. 5.2.2. Replacing Tokens in Strings
        3. 5.2.3. Formatted Token Replacement
        4. 5.2.4. Parsing into Data Types
        5. 5.2.5. An End-to-End Example
        6. 5.2.6. Splitting Names
        7. 5.2.7. Address Element Granularity
        8. 5.2.8. Phone Number Granularity
      3. 5.3. Creating HTML
        1. 5.3.1. Exporting HTML Files
        2. 5.3.2. Why Create HTML Using Code?
        3. 5.3.3. Creating HTML Files
      4. 5.4. Summary
    2. 6. Using SQL to Retrieve Data and Manipulate Objects
      1. 6.1. Where SQL Can Be Used in Access
        1. 6.1.1. Form and Report Recordsets
        2. 6.1.2. Partial SQL Properties
      2. 6.2. The Various Types of SQL Queries
        1. 6.2.1. The SELECT Query
        2. 6.2.2. Project Your Columns: the Field List
          1. 6.2.2.1. Add a Field Using Field Names
            1. 6.2.2.1.1. Fully Qualified Field Names
          2. 6.2.2.2. Add All Fields Using the Wildcard Character
          3. 6.2.2.3. Add Expressions
          4. 6.2.2.4. Field Name Aliases
        3. 6.2.3. Choose Your Tables: The FROM Clause
          1. 6.2.3.1. Table Name Aliases
        4. 6.2.4. Filter Your Data: The WHERE Clause
          1. 6.2.4.1. Comparison Operators
          2. 6.2.4.2. Matching on Strings
          3. 6.2.4.3. Matching on String Patterns Using Wildcard Characters
          4. 6.2.4.4. Matching on Data in a Range Using the BETWEEN ... AND Operator
          5. 6.2.4.5. Null vs. vbNullString
          6. 6.2.4.6. Matching on Null Values and Empty String Values
          7. 6.2.4.7. Matching on Dates
          8. 6.2.4.8. Matching on GUID Values
          9. 6.2.4.9. Matching to Items in a List (IN Operator)
        5. 6.2.5. Sort Your Results: The ORDER BY Clause
          1. 6.2.5.1. Setting the Sort Direction
          2. 6.2.5.2. Sorting on Binary or Memo Fields
      3. 6.3. Add Data from More Than One Table Using Table Joins
        1. 6.3.1. Inner Joins
        2. 6.3.2. Outer Joins
        3. 6.3.3. Joining More Than Two Tables Using Nested Joins
        4. 6.3.4. Self Joins
        5. 6.3.5. Cartesian Products
      4. 6.4. Prompt Users for Data with Parameters
        1. 6.4.1.
          1. 6.4.1.1. Form and Report Parameters
          2. 6.4.1.2. Improve Performance and Readability with the Parameters Declaration
          3. 6.4.1.3. Accidental Parameters
      5. 6.5. Add Data from Other Databases Using the IN Clause
      6. 6.6. Selection Predicates
        1. 6.6.1. ALL Predicate
        2. 6.6.2. TOP Predicate
        3. 6.6.3. DISTINCT Predicate
      7. 6.7. Aggregating Data
        1. 6.7.1. Bucket Your Data: The GROUP BY Clause
          1. 6.7.1.1. Aggregate Functions
        2. 6.7.2. Filter Your Data Based on Bucketed Data: the HAVING Clause
          1. 6.7.2.1. Comparing the WHERE Clause and the HAVING Clause
          2. 6.7.2.2. Domain Aggregate Functions
      8. 6.8. Action Queries
        1. 6.8.1. Make Table Query
        2. 6.8.2. Append Query
          1. 6.8.2.1. Append Multiple Records Using a SELECT Query as the Data Source
          2. 6.8.2.2. Creating a Multi-Record APPEND Query Using the Access Query Designer
          3. 6.8.2.3. Append a Single Record Using a Value List as the Data Source
          4. 6.8.2.4. Creating a Single-Record Append Query Using the Access Query Designer
        3. 6.8.3. UPDATE Query
          1. 6.8.3.1. Creating an Update Query Using the Access Query Designer
        4. 6.8.4. DELETE Query
      9. 6.9. Crosstab Queries
        1. 6.9.1. Ordering Column Headings
        2. 6.9.2. Common Expressions Used with Crosstab Queries
          1. 6.9.2.1. Partition Function
      10. 6.10. UNION Queries
        1. 6.10.1. Sorting a UNION Query
        2. 6.10.2. Using the Designer to Create Union Queries
      11. 6.11. Subqueries
        1. 6.11.1.
          1. 6.11.1.1. IN Predicate
      12. 6.12. Data Definition Queries
        1. 6.12.1. Create Table
          1. 6.12.1.1. Data Types Used
          2. 6.12.1.2. Working with Primary Keys
        2. 6.12.2. Alter Table
        3. 6.12.3. Drop Table
        4. 6.12.4. Create Index
        5. 6.12.5. Drop Index
        6. 6.12.6. Alter Database Password
      13. 6.13. SQL Pass-Through Queries
        1. 6.13.1. Running Ad Hoc SQL Pass-Through Queries
      14. 6.14. ANSI Mode
        1. 6.14.1. String Pattern Matching Using ANSI-92 Syntax
      15. 6.15. Summary
    3. 7. Managing Data
      1. 7.1. Finding Data
        1. 7.1.1. Find Methods
        2. 7.1.2. Seek Method
          1. 7.1.2.1. Seek Pattern
        3. 7.1.3. Move Methods
        4. 7.1.4. Search Optimization Tips
      2. 7.2. Categorization and Sorting
        1. 7.2.1. Categorization Using the Choose Function
        2. 7.2.2. Using a Custom Sort Order Field
      3. 7.3. Backup and Restore
      4. 7.4. Using Checksum Functions
        1. 7.4.1. MD5 Algorithm
        2. 7.4.2. Mod-10 Checksum
          1. 7.4.2.1. How the Mod-10 Checksum Algorithm Works
          2. 7.4.2.2. Using VBA to Calculate Mod-10 Checksums
      5. 7.5. Access 2007 Specifics
        1. 7.5.1. Locale-Agnostic Parser for ColumnHistory Function
        2. 7.5.2. Getting a List of Attachments in an Attachment Field
        3. 7.5.3. Searching for Records with Attachments
        4. 7.5.4. Searching for Specific Attachments
      6. 7.6. Summary
  8. III. Interacting with the Application
    1. 8. Using Code to Add Advanced Functionality to Forms
      1. 8.1. Working with Form Events
        1. 8.1.1. How to Determine When Data Is Added
        2. 8.1.2. How to Determine When Data Is Changed
        3. 8.1.3. How to Determine When Data Is Deleted
        4. 8.1.4. Validating Form Data
        5. 8.1.5. Suppressing Access Error Messages
        6. 8.1.6. How to Determine If a Modifier Key Is Pressed
        7. 8.1.7. Periodic Backups Using the Timer Event
        8. 8.1.8. Moving a Form Without a Border
        9. 8.1.9. Customizing ControlTipText
      2. 8.2. Working with Controls
        1. 8.2.1. Validating Control Data
        2. 8.2.2. Disabling all Controls
        3. 8.2.3. Reusing a Subform Control
        4. 8.2.4. Extended List Box Functionality
          1. 8.2.4.1. Moving Items Up and Down in a List Box
            1. 8.2.4.1.1. Create the Table
            2. 8.2.4.1.2. Create the Form
          2. 8.2.4.2. Deleting Items in a List Box with the Delete Key
          3. 8.2.4.3. Using a Multi-Select List Box for Filter Criteria
          4. 8.2.4.4. Selecting All Items in a Multi-Select List Box Using the Keyboard
        5. 8.2.5. Custom Progress Bars
          1. 8.2.5.1. Create the Form
          2. 8.2.5.2. Run the Process
        6. 8.2.6. Custom Up/Down Buttons
        7. 8.2.7. Displaying Multiple Attachments Onscreen
      3. 8.3. Common Forms for Your Applications
        1. 8.3.1. Dynamic Menu Forms and Dashboards
          1. 8.3.1.1. Defining the Dashboard Layouts
            1. 8.3.1.1.1. USysFrmDashboard1
            2. 8.3.1.1.2. USysFrmDashboard2
            3. 8.3.1.1.3. USysFrmDashboard3
            4. 8.3.1.1.4. USysFrmDashboard4
          2. 8.3.1.2. Creating the Tables
            1. 8.3.1.2.1. Create the Dashboard Table
            2. 8.3.1.2.2. Create the Component Table
            3. 8.3.1.2.3. Create the Dashboard Components Table
            4. 8.3.1.2.4. Creating Relationships for the Tables
          3. 8.3.1.3. Choosing a Dashboard
          4. 8.3.1.4. Opening the Selected Dashboard
          5. 8.3.1.5. Choosing Components
          6. 8.3.1.6. Filling the Dashboard
        2. 8.3.2. Splash Screens
        3. 8.3.3. About Dialog Boxes
        4. 8.3.4. Kiosk Forms
      4. 8.4. Custom Form Navigation
        1. 8.4.1. Navigation Bars
        2. 8.4.2. "I Need To" Drop-Down
        3. 8.4.3. Keyboard-Driven Navigation
      5. 8.5. Summary
    2. 9. Using Code to Add Advanced Functionality to Reports
      1. 9.1. Interactive Reports Using Access 2007
        1. 9.1.1. Sorting a Report Using Controls on a Report
        2. 9.1.2. Filtering a Report Using Controls
        3. 9.1.3. Drill-Down
        4. 9.1.4. Navigating to a Map Dynamically
      2. 9.2. Report Scenarios
        1. 9.2.1. Creating a Report with Data Bars
        2. 9.2.2. Creating a Calendar Report
          1. 9.2.2.1. Creating the Report
          2. 9.2.2.2. Filling the Report with Data
        3. 9.2.3. Displaying Images Using an Attachment Field
        4. 9.2.4. Displaying Images Dynamically Using a Path
      3. 9.3. Creating a Report Manager
        1. 9.3.1. Creating the Reports Table
        2. 9.3.2. Implementing Report Manager Features
          1. 9.3.2.1. Categorization
          2. 9.3.2.2. Print, Preview, and E-mail
          3. 9.3.2.3. Tracking Report Usage
          4. 9.3.2.4. Bulk Print
          5. 9.3.2.5. Allowing Users to Add Their Own Reports
      4. 9.4. Summary
    3. 10. Using Automation to Add Functionality
      1. 10.1. Automation Basics
        1. 10.1.1. When to Automate
        2. 10.1.2. Shell Function
        3. 10.1.3. CreateObject vs. GetObject
        4. 10.1.4. Early Binding vs. Late Binding
        5. 10.1.5. Discovering Object Models Using Built-in Tools
      2. 10.2. Automating Windows
        1. 10.2.1. The Shell Object
          1. 10.2.1.1. Opening Folders
          2. 10.2.1.2. Running a Command
          3. 10.2.1.3. Launching an Item in the Control Panel
          4. 10.2.1.4. Sending Mail Using the Default Mail Program
        2. 10.2.2. Windows Scripting Host
          1. 10.2.2.1. Reading from and Writing to the Registry
            1. 10.2.2.1.1. RegWrite Method
            2. 10.2.2.1.2. RegRead Method
            3. 10.2.2.1.3. RegDelete Method
            4. 10.2.2.1.4. Expanding REG_EXPAND_SZ Values
            5. 10.2.2.1.5. Working with REG_MULTI_SZ Values
          2. 10.2.2.2. Determining Special Folders
          3. 10.2.2.3. Creating a Temporary File in the Temp Folder
          4. 10.2.2.4. Creating Desktop Shortcuts
          5. 10.2.2.5. Using the WshShell Object to Wait for a Process to Complete
          6. 10.2.2.6. Working with Printers
            1. 10.2.2.6.1. Adding Printers
            2. 10.2.2.6.2. Removing Printers
            3. 10.2.2.6.3. Enumerating Printers
      3. 10.3. Automating Office Applications
        1. 10.3.1. Determining If an Office Application Is Installed
        2. 10.3.2. Word: Creating a Formatted Letter with Data
        3. 10.3.3. Excel: Creating an Excel Chart with Data
        4. 10.3.4. Outlook: Create Appointments from an Events Database
      4. 10.4. Automating Internet Explorer
        1. 10.4.1. Opening a URL in a New Tab in IE7
        2. 10.4.2. Common Web Queries
        3. 10.4.3. Parsing HTML from Internet Explorer
        4. 10.4.4. Summary
    4. 11. Creating Dynamic Ribbon Customizations
      1. 11.1. Overview of Ribbon Customizations in Access
        1. 11.1.1. Development Tips
          1. 11.1.1.1. Discovering Errors
          2. 11.1.1.2. Using IntelliSense
          3. 11.1.1.3. Prevent Loading at Startup
          4. 11.1.1.4. Finding Controls and Images
          5. 11.1.1.5. More Resources
        2. 11.1.2. How to Write Ribbon Customizations
          1. 11.1.2.1. Getting Started
          2. 11.1.2.2. Common Attributes
      2. 11.2. Loading Ribbons
        1. 11.2.1. Using the USysRibbons Table
        2. 11.2.2. Using the LoadCustomUI Method
      3. 11.3. Programming the Ribbon
        1. 11.3.1. Ribbon Objects
        2. 11.3.2. Using Callback Routines
          1. 11.3.2.1. Common Callback Routines
          2. 11.3.2.2. Similar Properties, Methods, or Events in Access
        3. 11.3.3. Refreshing Controls
      4. 11.4. Organizing Ribbon Items
        1. 11.4.1. Tabs
        2. 11.4.2. Contextual Tabs
        3. 11.4.3. Groups
      5. 11.5. Ribbon Controls
        1. 11.5.1. Buttons
        2. 11.5.2. Toggle Buttons
        3. 11.5.3. Check Boxes
        4. 11.5.4. Combo Boxes and Drop-Downs
        5. 11.5.5. Labels and Edit Boxes
        6. 11.5.6. Menus
          1. 11.5.6.1. itemSize Attribute
          2. 11.5.6.2. description Attribute
          3. 11.5.6.3. Menu Separators
      6. 11.6. New Types of Controls
        1. 11.6.1. Dialog Box Launcher
        2. 11.6.2. Gallery
        3. 11.6.3. Split Button
        4. 11.6.4. Dynamic Menu
      7. 11.7. Images
        1. 11.7.1. Images Included with Office
        2. 11.7.2. Loading Images from External Files
          1. 11.7.2.1. Creating a Global Image Handler
          2. 11.7.2.2. getImage and getItemImage Callbacks
        3. 11.7.3. Loading Images from an Attachment Field
          1. 11.7.3.1. Create the Table
          2. 11.7.3.2. Create the Form
          3. 11.7.3.3. Create the Customization
          4. 11.7.3.4. Add the Callback
      8. 11.8. Moving Functionality into the Ribbon
        1. 11.8.1. The NotInList Event — Ribbon Style
        2. 11.8.2. Form Navigation
        3. 11.8.3. Managing Filters Using a Dynamic Menu
        4. 11.8.4. Creating a Split Button That Sticks
      9. 11.9. Other Ribbon Modifications
        1. 11.9.1. Modifying the Office Menu
        2. 11.9.2. Overriding Commands and Repurposing Controls
      10. 11.10. Summary
  9. IV. Finalizing the Application
    1. 12. Configuration and Extensibility
      1. 12.1. Localization
        1. 12.1.1. Locale Settings
          1. 12.1.1.1. Using the API to Determine the Locale
          2. 12.1.1.2. Changing the User Locale
        2. 12.1.2. Determining the Current Language of Office
        3. 12.1.3. How to Create Localized Access Applications
          1. 12.1.3.1. VBA Intrinsic Functions
          2. 12.1.3.2. Localized Text Using Tables
            1. 12.1.3.2.1. Creating the String Table
            2. 12.1.3.2.2. Retrieving Strings from the String Table
          3. 12.1.3.3. Creating a Resource DLL
          4. 12.1.3.4. Deploying a Resource DLL
          5. 12.1.3.5. Reading Resources Using the LoadString API
      2. 12.2. Application Options
        1. 12.2.1. Storing Options in Tables
        2. 12.2.2. Storing Options in the Registry
        3. 12.2.3. Creating a Class to Work with Options
          1. 12.2.3.1. Creating the Option Constants
          2. 12.2.3.2. Managing Options from Tables
          3. 12.2.3.3. Managing Options in the Registry
        4. 12.2.4. Creating Options Forms
          1. 12.2.4.1. Creating the Form
          2. 12.2.4.2. Adding Events
        5. 12.2.5. Displaying Options in the Ribbon
          1. 12.2.5.1. Creating the Customization
          2. 12.2.5.2. Adding the Callbacks
          3. 12.2.5.3. Modifying the Options Form
      3. 12.3. Creating Form Themes
        1. 12.3.1. Theming Forms
          1. 12.3.1.1. Creating a Table to Store Theme Data
          2. 12.3.1.2. Applying Themes at Runtime
      4. 12.4. Summary
    2. 13. Personalization and Security
      1. 13.1. What Is Security?
        1. 13.1.1. Authentication
        2. 13.1.2. Authorization
      2. 13.2. What Is Personalization?
      3. 13.3. Overview of Security in Access 2007
        1. 13.3.1. Database Encryption
        2. 13.3.2. Disabled Mode
        3. 13.3.3. Office Trust Center
          1. 13.3.3.1. Macro Settings
          2. 13.3.3.2. Trusted Locations
        4. 13.3.4. Digital Signatures
        5. 13.3.5. Signed Packages
          1. 13.3.5.1. Creating a Signed Package
            1. 13.3.5.1.1. Creating the Self-Signed Certificate
            2. 13.3.5.1.2. Creating the Package File
          2. 13.3.5.2. Extracting the Signed Package
      4. 13.4. Access 2007 Navigation Pane
        1. 13.4.1. Customizing the Navigation Pane
        2. 13.4.2. Restricting the View of the Navigation Pane
          1. 13.4.2.1. Modify the Employees Table
          2. 13.4.2.2. Create a Login Form
        3. 13.4.3. Hiding the Design Surface of Objects
      5. 13.5. Password-Protecting Objects
        1. 13.5.1. Creating the User Table
        2. 13.5.2. Creating the Login Form
        3. 13.5.3. Prompting for Password with Forms and Reports
        4. 13.5.4. Creating a Password Protected Form Object
      6. 13.6. Windows Integration
        1. 13.6.1. Determining the Logon Name
        2. 13.6.2. Locking the Computer
        3. 13.6.3. Receiving Notifications When the Computer Is Locked or Unlocked
      7. 13.7. Simulating Record Level Security
        1. 13.7.1. Limiting the View to a Particular User
        2. 13.7.2. Locking Records Based on Logon
      8. 13.8. Database Encryption with DAO and ADO
        1. 13.8.1. Creating an Encrypted Database with DAO
        2. 13.8.2. Changing the Database Password with DAO
        3. 13.8.3. Adding a Database Password to a Database with DAO
        4. 13.8.4. Changing the Database Password with ADO
      9. 13.9. Best Practices
        1. 13.9.1. Using a Client-Server Database to Protect Data
        2. 13.9.2. AllowBypassKey Property
        3. 13.9.3. Using an ACCDE or MDE File
        4. 13.9.4. Using an ACCDR File
        5. 13.9.5. File Format Selection
      10. 13.10. Summary
    3. 14. Deployment
      1. 14.1. Creating an Automated Build
        1. 14.1.1. Creating the Visual Studio Project
          1. 14.1.1.1. Adding References
          2. 14.1.1.2. Private Data
          3. 14.1.1.3. Helper Functions
          4. 14.1.1.4. Design the Form
            1. 14.1.1.4.1. Adding Controls
            2. 14.1.1.4.2. Setting Form Properties
            3. 14.1.1.4.3. Control Events
          5. 14.1.1.5. Retrieving Information from the Source Database
        2. 14.1.2. Building the Application
          1. 14.1.2.1. Creating the Target Database
          2. 14.1.2.2. Set Build Properties on the Target Database
          3. 14.1.2.3. Setting Build Properties on the Source Database
          4. 14.1.2.4. Deleting Data from Tables
          5. 14.1.2.5. Calculating the Version Number
      2. 14.2. Handling Application Dependencies
        1. 14.2.1. Installing Files from an Attachment Field
        2. 14.2.2. Updating References
        3. 14.2.3. Testing Reference Fix-Up
          1. 14.2.3.1. Create the Library Database
          2. 14.2.3.2. Call Code in the Library Database
          3. 14.2.3.3. Break the Reference
        4. 14.2.4. Late Binding
      3. 14.3. Licensing Your Applications
        1. 14.3.1. Creating a Limited-Use Application
          1. 14.3.1.1. Expiration Date
          2. 14.3.1.2. Number of Records
          3. 14.3.1.3. Limited Functionality
          4. 14.3.1.4. Restricting the Number of Times an Application is Launched
        2. 14.3.2. Registering an Application
          1. 14.3.2.1. Generating Registration Keys
            1. 14.3.2.1.1. Numeric Registration Keys
            2. 14.3.2.1.2. Alpha-Numeric Registration Keys
            3. 14.3.2.1.3. Testing the Key Generation
          2. 14.3.2.2. Creating a Registration Web Service
            1. 14.3.2.2.1. Create the Registration Database
            2. 14.3.2.2.2. Create the Web Service
            3. 14.3.2.2.3. Validate the Registration Using the Web Service
      4. 14.4. Miscellaneous Deployment Scenarios
        1. 14.4.1. Updating Your Applications
          1. 14.4.1.1. Checking for Latest Version
            1. 14.4.1.1.1. Create the Versioning Server
            2. 14.4.1.1.2. Create the Client Application
            3. 14.4.1.1.3. Testing the Versioning Server
          2. 14.4.1.2. Re-Linking Tables Automatically
        2. 14.4.2. Programmatically Creating DSNs
          1. 14.4.2.1. Creating a DSN-Less Connection
          2. 14.4.2.2. Creating a User DSN
          3. 14.4.2.3. Creating a System DSN
        3. 14.4.3. Ensuring an Application Runs Locally
      5. 14.5. Summary
    4. 15. Help and Documentation
      1. 15.1. Documentation via Built-in Properties
        1. 15.1.1. Using the Description Property
        2. 15.1.2. End SubSetting the Status Bar Text
          1. 15.1.2.1. Setting Status Bar Text Programmatically
        3. 15.1.3. Using the Tag Property
        4. 15.1.4. Setting the Control Tip
        5. 15.1.5. Database Documenter
      2. 15.2. Providing Help to Users
        1. 15.2.1. Storing Help Text in a Table
        2. 15.2.2. Creating a Help Form
        3. 15.2.3. Trapping the F1 Key
        4. 15.2.4. Alternatives to a Floating Help Window
      3. 15.3. External Approaches
        1. 15.3.1. Writing HTML Content for Help
        2. 15.3.2. Mapping HTML Files to Objects
        3. 15.3.3. Opening the Browser
        4. 15.3.4. Creating Compiled HTML Help Files
          1. 15.3.4.1. Creating Content for HTML Help Workshop
          2. 15.3.4.2. Add Files to Your Project and Set Project Properties
          3. 15.3.4.3. Add Context Mapping
          4. 15.3.4.4. Build the Table of Contents
          5. 15.3.4.5. Compile the Help File
          6. 15.3.4.6. Adding HelpContextID Values to Your Controls
      4. 15.4. Summary
    5. A. Programming Tips and Tricks
      1. A.1. Dynamically Running Code
        1. A.1.1. CallByName
          1. A.1.1.1. Retrieve a Property Value
          2. A.1.1.2. Set a Property Value
          3. A.1.1.3. Call a Method
        2. A.1.2. Application.Run
        3. A.1.3. Eval
      2. A.2. Object Helper Functions
        1. A.2.1. Object Initialization
        2. A.2.2. Object Termination
        3. A.2.3. Global Object Properties
      3. A.3. Date Data Type Tricks
        1. A.3.1. Looping Through Dates
        2. A.3.2. Arrays of Dates
        3. A.3.3. Determining Dates Dynamically
      4. A.4. Miscellaneous Tips
        1. A.4.1. Categorizing Constant Values
        2. A.4.2. Comparing Class Instances
          1. A.4.2.1. Equals
          2. A.4.2.2. ReferenceEquals
        3. A.4.3. Add Number of Retries to a Code Block
        4. A.4.4. Add a Timeout to a Code Block
      5. A.5. Tools for the Immediate Window
        1. A.5.1. Clearing the Immediate Window
        2. A.5.2. Displaying the Watch Window
        3. A.5.3. Displaying the Locals Window
        4. A.5.4. Making a Backup of Objects
        5. A.5.5. Closing All Code Windows
    6. B. Query Performance
      1. B.1. The Query Optimizer
      2. B.2. Designing Tables for Performance
        1. B.2.1. Use the Smallest Appropriate Data Type
        2. B.2.2. Indexes
          1. B.2.2.1. Use Numeric Values for Primary Key Fields
          2. B.2.2.2. Index Criteria, Sort, and Join Fields
          3. B.2.2.3. Single Field vs. Multi-Field Indexes
      3. B.3. Designing Queries for Performance
        1. B.3.1. Reduce the Amount of Data
          1. B.3.1.1. Use Between ... And, IN Operators
          2. B.3.1.2. Avoid Calculated Fields
          3. B.3.1.3. Use Fixed Column Headings in Your CROSSTAB Queries
          4. B.3.1.4. Use Count(*) for Totals
          5. B.3.1.5. Use Stored Queries Instead of Recordset Properties
      4. B.4. Other Performance Considerations
        1. B.4.1. Bulk Edits
        2. B.4.2. Minimize Network Traffic
        3. B.4.3. Database Maintenance
        4. B.4.4. Hard Drive Maintenance
      5. B.5. Microsoft Access Performance Analyzer
    7. C. Pattern Reference
      1. C.1. Introducing Patterns
        1. C.1.1. What Is a Pattern
        2. C.1.2. Why Use Patterns?
      2. C.2. Patterns Used in This Book
        1. C.2.1. Gang of Four Patterns
          1. C.2.1.1. Prototype
          2. C.2.1.2. Factory
          3. C.2.1.3. Iterator
        2. C.2.2. VBA Object Patterns
          1. C.2.2.1. Creational
            1. C.2.2.1.1. Object Instantiation
            2. C.2.2.1.2. Recordset Instantiation
          2. C.2.2.2. Destruction
            1. C.2.2.2.1. Object Destruction
            2. C.2.2.2.2. Recordset Destruction
        3. C.2.3. Other Patterns
          1. C.2.3.1. Equals Method Pattern
          2. C.2.3.2. Seek Method
          3. C.2.3.3. Attachment Fields
        4. C.2.4. Working with Strings
          1. C.2.4.1. Token Replacement
          2. C.2.4.2. Formatting Numbers as Hexadecimal
        5. C.2.5. Where and How to Find Patterns

Product information

  • Title: Expert Access™ 2007 Programming
  • Author(s):
  • Release date: November 2007
  • Publisher(s): Wrox
  • ISBN: 9780470174029