F. Scott Barker’s Microsoft Access 2002 Power Programming

F. Scott Barker’s Microsoft Access 2002 Power Programming

Released September 2001
Publisher(s): Sams
ISBN: None

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

F. Scott Barker's Access 2002 Power Programming gives many practical techniques for the corporate and independent developer.

The main topics covered are:

  • The Root of Power Programming;

  • Manipulating and Presenting Data;

  • Extending Access with Interoperability;

  • Adding the Professional Look and Distributing Applications;

  • Managing Databases;

  • Adding Finishing Touches.

New features of Access 2002 will be covered thoroughly, including:

  • A whole new chapter devoted to Data Pages, which is Microsoft's way of bringing the Web interface into everyday office solutions.

  • A new chapter clarifying the confusion over ADP/MDB and DAO/ADO. Including when and where to use each.

  • There are a number of new additions to VBA which developers will need good exposure to in order to take advantage of them.

  • A number of changes have been implemented in the form design with control features added.

Table of contents

  1. Copyright
    1. Dedication
  2.  
  3. About the Author
  4. Acknowledgments
  5. Tell Us What You Think!
  6. A Letter from the Author
    1. Who Is This Book For?
    2. What’s Covered in This Book?
      1. Part I: The Root of Power Programming
      2. Part II: Manipulating and Presenting Data
      3. Part III: Extending Access with Interoperability
      4. Part IV: Managing Your Databases
      5. Part V: Adding Finishing Touches
    3. Using This Book’s Web Site
    4. Conventions Used in This Book
  7. I. The Root of Power Programming
    1. 1. Macros Are for Weenies; Code Is Cool!
      1. Understanding Where Macros End and Code Begins
        1. When Are Macros Necessary?
        2. When Is Code Necessary?
      2. Looking at Macro-to-Code Changes
        1. Using the DoCmd Object
        2. Code Equivalents of Macro Commands
      3. Converting Existing Macros to VBA Code
      4. Summary
    2. 2. Coding in Access 2002 with VBA
      1. Getting Started with Programming
        1. Using Code Modules
          1. Coding Behind Forms
          2. Coding in Standard Modules
        2. Declaring Variables
          1. Defining the Life and Location of Variables
          2. Naming Variables with the Leszynski Naming Conventions
          3. Forcing a Variable Declaration with Option Explicit
        3. Declaring Procedures
          1. Determining a Procedure’s Scope
          2. Specifying Arguments in Procedures
          3. Using Enumeration to Control Parameters
          4. Taking Advantage of Optional Parameters
          5. Taking Advantage of Parameterized Arrays
          6. Passing Arguments ByRef or ByVal
        4. Controlling Program Flow
          1. Making Decisions in Your Code
          2. Getting Dizzy with Loops
          3. Indenting Rules for Control-Flow Code Blocks
        5. Commenting Code
        6. Handling Errors
          1. Errors While Code Is Running
          2. Errors While Code Isn’t Running
      2. Getting Started with VBA
        1. Introducing Objects
        2. Using the Object Browser
          1. Displaying the Object Browser
          2. Adding References to Other Objects
      3. Programming with Objects
        1. The Public Keyword
        2. The Private Keyword
      4. Using Properties and Methods
        1. Using Existing Properties
          1. Setting Property Values
          2. Getting Property Values
          3. Using the Default Property
          4. Setting Multiple Properties with the With Statement
        2. Using Existing Methods
        3. Specifying Named Parameters
        4. Assigning Objects to Variables
          1. Using Generic Object Types
          2. Identifying the Object Type
      5. Using Collections
        1. Counting the Number of Elements
        2. Accessing Elements of the Collection
        3. Iterating Over Collections
      6. Customizing a Form
        1. Writing Custom Properties
        2. Writing Object-Valued Properties
        3. Writing Custom Methods
      7. Coding Class Modules
        1. Creating the Support Objects
        2. Creating the Class Module
        3. Using the Class Module
      8. Summary
    3. 3. Making Access Project and Data Technologies Choices
      1. Using Microsoft Database Versus Access Database Project
        1. Looking at the Objects Used in Each
      2. Using DAO Versus ADO Versus XML
      3. Summary
    4. 4. Working with Access Collections and Objects
      1. Creating Custom Collections
        1. Defining a New Collection
        2. Adding Items to the Collection
        3. Removing Items from the Collection
      2. Comparing Custom Collections to Arrays
        1. Creating a Collection of Integers
        2. Creating an Array of Integers
        3. Understanding Advanced Uses of Collections
      3. Accessing the Access Object Model
        1. Using the Application Object
          1. Turning Screen Painting on and Off
          2. Setting and Retrieving Option Values
          3. Quitting Your Application
          4. Manipulating the Active Object
          5. Using Your Application’s hWndAccessApp
          6. Controlling Access Through Automation
          7. Commanding the DoCmd Object
          8. CurrentData and CurrentProject Objects
          9. Working with CurrentProject Properties
          10. Adding Custom Properties to Access Objects
        2. Looking at the References Collection
        3. Specifying and Manipulating Printers
        4. Working with the Forms, Reports, and Data Access Pages Collections
          1. Using the Me Keyword
          2. Explicitly Referencing a Form
          3. Referencing the Form’s Default Instance
      4. Programming Multiple Copies of the Same Form
        1. Supporting Multiple-Form Instances
        2. Examining the frmEmployees Form’s Code
        3. Closing the frmEmployees Forms Automatically
      5. Summary
    5. 5. Introducing ActiveX Data Objects
      1. Looking at ADO’s Object Models
        1. The ActiveX Data Objects 2.5 (ADODB) Object Model
        2. The ADO Extensions 2.5 for DDL and Security (ADOX) Object Model
        3. Jet and Replication Objects 2.5 (JRO) Object Model
      2. Referencing the Type Libraries
      3. Opening a Connection to a Database
        1. Connecting to the Current Database
        2. Connecting to Another Database
      4. Creating a Recordset
        1. Opening a Simple Recordset
        2. Looping Through and Editing Recordsets
        3. Creating Persistent Recordsets
        4. Using the RecordCount, BOF, and EOF Properties
        5. Checking to See What Operations a Recordset Will Support
        6. Cloning Recordsets
        7. Storing Bookmarks
      5. Working with Queries
        1. Creating a New Query
        2. Creating a Parameterized Query
        3. Opening a Recordset Off a Parameterized Query
        4. Executing Bulk Queries
        5. Modifying an Existing Query
        6. Deleting a Query
      6. Working with Tables
        1. Creating a New Table with Fields and Indexes
        2. Modifying an Existing Table by Adding an Index
      7. Summary
    6. 6. Using XML with Access 2002
      1. Getting to Know XML
        1. Looking at XML’s History
        2. Examining the Files That Make Up XML Documents
      2. Working with XML with the Access User Interface
        1. Exporting from Access to XML
        2. Differences in Exporting Between Access User Interface and ADO
        3. Importing an XML Document
      3. Coding with XML and VBA in Access 2002
      4. Taking Advantage of the Other Office Applications’ XML Support
        1. Introducing the Excel XML Spreadsheet Schema (XML SS)
      5. Summary
    7. 7. Handling Your Errors in Access with VBA
      1. Examining Access’s Runtime Error Handling
        1. Using the On Error Command
          1. The On Error Goto Statement
          2. The On Error Resume Next Statement
          3. The On Error GoTo 0 Statement
        2. Using the Exit Sub|Function Command
        3. Using Resume, Resume Next, and Resume LineLabel
          1. The Resume Statement
          2. The Resume Next Statement
          3. The Resume LineLabel Statement
      2. Working with the Err and Error Objects
        1. The Err Object’s Clear Method
        2. The Err Object’s Raise Method
      3. Working with the ADO Errors Collection
      4. Creating User-Defined Errors
      5. Using Custom Error Logs to Track Errors
        1. An Example Error Handler Calling the Error Log
        2. The Actual Error Log Code
          1. Logging to the Back End First, or Front End if Necessary
          2. Updating the Back End with Any Errors That Occur
      6. Creating a Centralized Error-Handling Routine
      7. A Last Look at Error-Handling Issues
        1. Watching for Environment Changes
        2. Using Your Error Handler to Roll Back Transactions
        3. Using a Form’s On Error Event
        4. Nesting Error Handlers
        5. Looking at Some New Options for Error Handling
      8. Summary
  8. II. Manipulating and Presenting Data
    1. 8. Using Queries to Get the Most Out of Your Data
      1. Understanding Where Queries Are Used in Access
        1. Using Queries with Form and Report Record Source Properties
        2. Giving Users Access to Queries
        3. Using Naming Conventions and Query Documentation
      2. Working with Select Queries
        1. Joining Tables
        2. Using the Same Table Twice (Self Joins)
        3. Using the Access AutoLookup Feature
      3. Working with Action Queries
        1. Make Table Queries (SELECT INTO)
        2. Append Queries (INSERT INTO)
        3. Update Queries (UPDATE..SET)
        4. Delete Query (DELETE)
      4. Performing Advanced Query Operations
        1. Summary Queries
        2. Union Queries
        3. Nested Queries
        4. Subqueries
        5. DDL Queries
      5. Adding More Power with VBA
        1. Building Faster Queries
        2. Using Query by Form
          1. A Simple Query by Form
          2. A More Complex Query by Form
        3. Creating Temporary Command Objects
        4. Using the DoCmd Object’s RunSQL Method
        5. Issuing Parameter Queries
      6. Driving Reports and Forms with Queries
      7. Solving Problems with Queries
        1. Grouping to Get Percentages
        2. Finding and Deleting Duplicate Records
        3. Nesting Groups to Get the Complete Solution
        4. Distinguishing Between New and Old Records
        5. Creating a Total Row for Crosstab Queries
          1. Handling the Detail with a Crosstab Query
          2. Totaling the Values for Each Rating with a Summary Query
          3. Creating a Crosstab Query from the Summary Query with the Same Field Layout
          4. Combining the Two Crosstabs with a Union Query
          5. Extra Credit: Specifying Column Headings in a Crosstab
      8. Examining the Architecture of the Query Resolution Process
        1. Defining the Query
        2. Compiling the SQL Statement
        3. Preparing the Execution Plan (Optimization)
      9. Optimizing Queries with Jet
        1. Using Rushmore Technology
        2. Examining the Clustered Primary Index
        3. Working with Read-Ahead
      10. Understanding Optimization Techniques
        1. Increasing Performance with Table Relationships
        2. Adding Indexes
        3. Tweaking the Database Structure to Affect Performance
        4. Optimizing Join Performance
      11. Using Unconventional Optimization Techniques
        1. Understanding Some Performance-Tuning Pitfalls
        2. Diagnosing Slow Queries
        3. Resolving Ambiguous Field References
      12. Using the Analyzer Wizards
        1. Table Analyzer Wizard
        2. Performance Analyzer
        3. Database Documentor
      13. Looking at Access 2002’s New Query Features
        1. Using ANSI 92 SQL Mode
        2. Viewing Data Using PivotTable and PivotChart Views
      14. Summary
    2. 9. Creating Powerful Forms
      1. Increasing Form Performance
      2. Looking at Access 2002’s New Form Features
        1. New Base Form Events
        2. Using the New PivotTable and PivotChart Views
      3. Taking Advantage of Other Form Features
        1. Using the Form Recordset Property
        2. Using the Dirty Event
        3. Specifying a Splash Screen Form at Startup
        4. Using Form Background Properties
      4. Reusing Forms to Perform Standard Tasks
      5. Increasing Tabbed Form Performance
      6. Summary
    3. 10. Expanding the Power of Your Forms with Controls
      1. Setting Up a Field’s Lookup Properties for Use on Forms
      2. Tapping into the Power of Combo Boxes
        1. Using the Combo Box Wizard
        2. Programming Combo Boxes Beyond the Wizard
        3. Using a Union Query to Give the Choice of One or All
        4. Using a UNION SQL Statement to Requery All in a Subform
        5. Displaying Combo Box Columns Outside the Control
        6. Adding New Combo Box Items Based on User Input
      3. Working with the Access Tab Control
        1. Creating and Editing a New Tab Control
        2. Moving Pages in the Tab Control
        3. Adding Controls to the Tab Pages
        4. Using Code with the Access Tab Control
      4. Morphing Access Controls
        1. Morphing Controls at Design Time
        2. Morphing Controls with VBA at Runtime
      5. Programming Multiselect ListBox Controls
        1. List Box Properties Dealing with Multiple Selection
        2. Manipulating Items Selected in a Multiselect List Box with VBA
          1. A Simple Example for Getting Selected Items
          2. Example for Getting/Setting Selected Items from/to a Table
      6. Getting Relief with the Subform/Subreport Wizard
      7. Giving Controls Spreadsheet-Type Cursor Movements
        1. Looking at the Problem
        2. Creating a Solution
      8. Manipulating Controls Through Code
        1. Examining the Pieces of the Option Group Menu Form
        2. Introducing the ManipulatingControlsExample Form
        3. Looking at the Code Behind the Form
      9. Summary
    4. 11. Creating Powerful Reports
      1. Creating Summary, Detail, and Summary/Detail Reports from the Same Report
      2. Creating Dynamic Groupings for the Same Report with QBF
      3. The Elusive Feature: Creating Snaking Reports
        1. Looking at the Before Report
        2. Working with the After Report
      4. Printing Multiple Topics Through a MultiSelect List Box
        1. Looking at the rptMultiSelectCategoryExample Report
        2. Looking at the MultiSelect List Box Form
        3. Code Listings for the MultiSelect List Box Form
      5. Creating a Wizard-Like Interface for Selecting Group-By Items
        1. The Core Tables: WizExReports and WizExElements
        2. Working with the frmWizExReports Form
        3. Using the Group Element Wizard with a New Report
      6. Formatting Reports Dynamically
        1. Looking at the rptDynamicFormattingExample Report
        2. Using Conditional Formatting in Reports
          1. Looking at the rptConditionalFormattingExample Report
          2. Looking at the rptConditionalFormattingInCodeExample Report
      7. Summary
    5. 12. Working with Data Access Pages
      1. Why Data Access Pages?
        1. Understanding How Data Access Pages Are Structured
        2. Understanding the Navigation Control
        3. Comparing Data Access Pages to Forms and Reports
        4. Understanding What Users Need for Data Access Pages
      2. Saving Time with the Data Access Page Wizards
        1. Using AutoPage: Columnar
        2. Taking Off with the Page Wizard
      3. Creating and Enhancing Simple Data Access Pages
        1. Looking at the Data Access Page Field List
        2. Adding Hyperlinks
          1. Adding an Unbound Hyperlink
          2. Adding a Bound Hyperlink
        3. Using Expressions on Data Access Pages
        4. Using Bound Combo and List Boxes
        5. Formatting with Themes
        6. Using Additional Controls on Data Access Pages
      4. Grouping Data Access Pages: Reports for the Web
        1. Creating the Base Page
        2. Creating a Relationship on a Data Access Page
        3. Creating Group Levels with Promote
        4. Adding a Caption Section
        5. Viewing Your Data Hierarchically with Banded Data Access Pages
        6. Using a Combo for a Group Filter Control
      5. Finding Additional Resources
      6. Summary
  9. III. Extending Access with Interoperability
    1. 13. Driving Office Applications with Automation
      1. Working with Automation
        1. Declaring Object Variables in VBA
        2. Using the CreateObject() Function
        3. Using the GetObject() Function
        4. Cleaning Up When Done with an Object
      2. Running Other Applications from Access with Automation
        1. Driving Word from Access
        2. Driving Excel from Access
        3. Driving Microsoft Project from Access
        4. Driving Outlook from Access
          1. Creating a Outlook Mail Item
          2. Creating an Outlook Task Item from Access
          3. Putting Contacts into Outlook from Access
          4. Deleting Contacts in Outlook from Access
          5. Creating Outlook Calendar Entries from Access
      3. Driving Access from Another Application with Automation
      4. Summary
    2. 14. Programming for Power with ActiveX Controls
      1. Understanding the ActiveX Common Controls
      2. Using the TabStrip Control
        1. Using an ImageList Control with the TabStrip Control
        2. Programming the Standard Access Tab Versus the ActiveX TabStrip Control
      3. Taking a Closer Look at the ImageList Control
        1. Adding Images During Design Time
        2. Adding Images to the ImageList Control at Runtime
      4. Emulating the Windows Explorer with the ListView Control
        1. Looking at the Different Views of the ListView Control
        2. Seeing the Major Groupings of the ListView Control Properties
        3. Setting Up a ListView Control Manually
        4. Creating and Filling a ListView Control Using VBA
      5. Displaying a Task’s Progress with the ProgressBar Control
        1. Displaying the Access Progress Bar with SysCmd()
        2. Using the ActiveX ProgressBar Control
      6. Sizing Text Boxes at Runtime with the Slider Control
      7. Telling It Like It Is with the Rich Textbox Control
        1. Properties of the Rich Textbox Control
        2. Code Behind the Microsoft Rich Textbox Control
      8. Creating Status Bars for Individual Forms with the StatusBar Control
        1. Properties of the StatusBar Panels Collection
        2. Setting Status Bar Properties at Runtime
      9. Docking Toolbars on Forms Using the ToolBar Control
      10. Viewing Data File Manager Style with the TreeView Control
      11. Summary
    3. 15. Extending the Power of Access with API Calls
      1. Understanding Dynamic Link Libraries
      2. Examining the Syntax for API Calls
      3. Finding API Declarations
      4. Viewing the Possible API Calls
        1. Using the API Viewer to Locate Calls
          1. Copying and Pasting Calls from the API Viewer
        2. Finding API Calls in the Win32api.txt File
      5. Considering Some Issues When Using API Calls
        1. Creating Your Own API Declarations from Scratch
        2. Converting 16-Bit to 32-Bit API Calls
      6. Looking at Some Examples of API Calls
        1. Finding an Executable Application Associated with a File
        2. Connecting and Disconnecting Network Drives from Within Access
          1. Programmatically Connecting and Disconnecting Network Drives Directly
          2. Calling Standard Dialogs to Connect and Disconnect Network Drives
        3. Displaying the Current User and Computer Name
      7. Displaying Pertinent Folders from Within Your Application
      8. Using the Open File Dialog API Call
      9. Summary
    4. 16. Extending Your VBA Library Power with Class Modules and Collections
      1. Setting Up a Bookmark Tracker
        1. Feature Set of the Bookmark Tracker
        2. Basic Objects of the Bookmark Tracker
        3. Let’s Have a Little Class...Modules, That Is
          1. clsBookmarkItems Stores the Actual Bookmarks
          2. clsBookmarkManagement Performs the Hard Work
            1. The Calls to the Class Module Methods
            2. The Declarations Section for the Class Module
            3. The InitBookmarks Method
            4. The BookmarkAction Method
              1. Adding a Bookmark
              2. Removing a Bookmark
              3. Moving to a Bookmark
      2. Managing Multiple Instances of the Same Form
        1. Looking at the Feature Set
        2. Looking at the Forms Used to Open Copies of the Same Form
        3. Examining the Code for Managing Multiple Copies
      3. Summary
    5. 17. Creating Your Own Wizards and Add-ins
      1. Understanding Access Wizards, Builders, and Add-Ins
      2. Looking at the Wizards and Add-In Registry Entries
      3. Creating Your Own Add-Ins
        1. Working with the Bookmark Tracker Wizard
          1. Reviewing the Bookmark Tracker
          2. Features of the Bookmark Tracker Wizard
        2. Installing Add-Ins in Access
          1. Looking at the USysRegInfo Table
          2. Installing Your Wizard with the Add-In Manager
        3. Programming the Bookmark Tracking Wizard
          1. Initializing the Wizard Form
          2. Looking at the Command Buttons
          3. Switching Pages of the Wizard’s Tab Control
          4. Validating Fields on the First Page of the Wizard Form
          5. Fields and Event Procedures on the Second Page of the Wizard Form
          6. Fields and Event Procedures on the Wizard’s Third Page
        4. Finishing with the Wizard
      4. Using Access Code Libraries
        1. Looking at the Pros and Cons of Code Libraries
        2. Considering Where to Put the Library Database
        3. Setting a Reference to a Library
        4. Viewing Library Routines in the Object Browser
        5. Looking at Some Library Coding Issues
          1. Using CurrentProject to Reference the Application
          2. Using CodeProject to Reference the Application
          3. Executing Application Routines from the Library
      5. Summary
    6. 18. Manipulating the Registry with VBA
      1. Looking at the Windows Registry’s History
        1. Using the Windows Registry in Your Applications
        2. Parts Making Up the Registry
          1. Predefined Keys and Subkeys
          2. Values
        3. Tools Used For Working with the Registry
      2. Using VBA’s Registry Commands
      3. Performing Tasks with Registry API Calls
        1. Looking at the Sample Application
        2. Working with the Actual Code
          1. Browsing for a Back End to Track
          2. Registering the Database
          3. Listing the API Declarations and Wrapper Routines for the Registry
          4. Getting a Subkey List for a Registry Key
          5. Linking the Tables
          6. Deleting a Back End from the Registry
      4. Summary
    7. 19. Using Access with the Internet
      1. What’s New in Access 2002 for the Internet?
      2. Using the Access Hyperlink Features
        1. Working with Unbound Hyperlink Controls
          1. Using the Hyperlink Address and Hyperlink SubAddress Properties
          2. Editing the Hyperlink Properties
        2. Maintaining a Hyperlink Base for a Database
        3. Looking at the Hyperlink Data Type
        4. Using the IsHyperlink Property to Add Hyperlinks to Your Interface
        5. Programmatically Using Hyperlinks with the Follow, FollowHyperlink, and HyperlinkPart Methods
          1. Using the Follow Method
          2. Using the FollowHyperlink Method
          3. Using the HyperlinkPart Method
        6. Working with Hyperlink Options
      3. Easily Importing and Exporting Access Objects to HTML Documents
        1. Exporting to HTML
        2. Importing and Linking to HTML Files
      4. Publishing to Other Web File Formats
      5. Summary
  10. IV. Managing Databases
    1. 20. Securing Your Application
      1. Understanding the Purpose of Securing Applications
        1. Protecting Sensitive Data: The Client’s Perspective
        2. Protecting Code: The Developer’s Perspective
      2. Understanding Access Security
        1. Share-Level Security: The Database Password
        2. User-Level Security: The Real Security System of Access
          1. Users and Groups
          2. Permissions
          3. Do I Have Permissions?
          4. Ownership
          5. Database Encryption for Added Protection
          6. The System.mdw File
      3. Looking at the Security User Interface
        1. Working with PIDs, SIDs, WIDs, and Passwords
          1. User Name Criteria
          2. Personal Identifier Criteria
          3. Workgroup Identifiers
          4. Re-Creating Accounts
          5. Passwords
        2. Creating a New User
        3. Creating a New Group
        4. Removing Users and Groups
        5. Adding a User to a Group
        6. Adding a Password to a User Account
        7. Removing a Password from a User Account
        8. Setting Permissions on Objects
        9. Securing Modules in the VBE
        10. Setting Database Permissions
        11. Changing the Owner of an Object
        12. Encrypting a Database
        13. Creating a Workgroup Information File
        14. Manually Securing a Database
          1. Which Permissions Should I Set?
          2. Steps to Unsecure a Database
      4. Making Life Easier with Access Security Tools
        1. Using the Security Wizard
        2. Printing Users and Groups from Access
        3. Reading the Security White Papers
        4. Using Other Security Resources
      5. Avoiding Common Pitfalls Found in Access Security
        1. Planning Security
        2. Creating Objects with Default Accounts
        3. Securing Linked Tables in a Multiuser Environment
        4. Running with Owner’s Permissions
        5. Using Security in a Replication Environment
        6. Distributing Secured Applications with the Microsoft Office Developer
        7. Distributing Secured Applications Through an .mde File
      6. Managing Security Through Code
        1. Programming Security with DAO
          1. The User and Groups Collections
          2. Permissions and the Documents Collection
        2. Creating a New User Through Code
        3. Deleting a User Through Code
        4. Setting the Database Password Through Code
        5. Creating a Group Through Code
        6. Deleting a Group Through Code
        7. Adding a User to a Group Through Code
        8. Removing a User from a Group Through Code
        9. Changing the Owner of an Object Through Code
        10. Setting Permissions for an Object Through Code
        11. Checking Permissions Through Code
        12. Determining Who You’re Logged On As Through Code
        13. Denying Users the Ability to Create Databases
        14. Denying the Creation of Table and Query Objects
        15. Compacting, Encrypting, or Decrypting a Database Through Code
        16. Disabling the Bypass Key Through Code
      7. Using the Secured Sample Database: Chap20s.mdb
      8. Summary
    2. 21. Handling Multiuser Situations
      1. Understanding Multiuser Terminology
      2. Understanding Multiuser Handling in Access
        1. Default Record Locking
        2. Default Open Mode: Shared Versus Exclusive
        3. Number of Update Retries
        4. ODBC Refresh Interval
        5. Refresh Interval
        6. Update Retry Interval
      3. One or Two Database Containers: Knowing Where to Put the Pieces
        1. Knowing What Should Go Where: An Overview
          1. Advantages to Splitting Databases
          2. Disadvantages to Splitting Databases
        2. Splitting Databases
      4. Looking at the Built-In Locking Modes
        1. Understanding Row-Level Versus Page-Level Locking
        2. Using the Built-In Locking Modes
          1. Locking All Records Mode
          2. Locking Edited Records Mode
          3. No Locks Mode
        3. Using Locking Modes in VBA
        4. Using Alternative Locking Schemes
          1. Using the “Roll-Your-Own” Scheme
          2. Using the Unbound Forms Scheme
      5. Working in VBA with Unbound Forms
        1. Creating the Routines for Handling Unbound Forms
        2. Using the Sample Form, Step by Step
        3. Using Support Routines
          1. Adding a Record on the Unbound Form
          2. Locating and Loading a Record with the Unbound Form
          3. Saving a Record on the Unbound Form
      6. Coding for Multiuser Error Handling
      7. Summary
    3. 22. Welcome to the World of Database Replication
      1. Understanding Database Replication Concepts
        1. The Replication Design Goal
        2. Some Typical Replication Applications
      2. Working with Jet Replication Tools
        1. The Briefcase
        2. Access Menus
        3. Replication Manager
        4. Jet and Replication Objects Programming
      3. Converting Databases to Replicas
      4. Synchronizing Replicas
        1. Understanding the Design Master and Replicas
        2. Recovering the Design Master
        3. Replication Visibilities
        4. Replication System Columns, Tables, and Other Mysteries
        5. Using Replica Sets
      5. Understanding Replica Set Topologies
        1. Singly Connected List
        2. Star and Hub Topologies
        3. Automating Star and Hub Synchronization
      6. Distributing Replicable Applications
        1. Using Replicable and Non-Replicable Objects
        2. Creating Partial Replicas
          1. Partial Replica Wizard
          2. Creating Partial Replicas in Code
      7. Replicating Back-End and Front-End Applications
      8. Handling Replication Conflicts
        1. Using the Conflict Viewer
        2. Using an Alternative Conflict-Resolution Algorithm
          1. Identifying Replicas with Conflicts
          2. Using the Last-Update-Wins Algorithm
        3. Understanding Various Replication Conflicts
      9. Understanding Replication Synchronizers
        1. Synchronization Phases
        2. Direct and Indirect Synchronizations
        3. Scheduled and On-Demand Synchronizations
        4. Synchronizing Replicas over the Internet
        5. Handling Counter Fields
        6. Using Read-Only Attributes with Replication
        7. Performing Replication Identification Fixup
        8. Using the Last Synchronization Partner
        9. Using the Compact Utility with Replicated Databases
        10. Deciding Whether to Back Up Your Replicas
      10. Upgrading Replica Sets to Access 200x
      11. Securing Replicated Applications
      12. Using MDE Files with Replicated Databases
      13. Creating Successful Replication Applications
      14. Summary
    4. 23. Moving Workgroup Applications to Client/Server
      1. Understanding Client/Server
        1. Working with Open Database Connectivity
        2. Reasons to Use Access for Client/Server
      2. Factoring for Client/Server Migration
        1. Amount of Data
        2. Use and Purpose of Database
        3. Database Design
        4. Concurrent Use and Number of Users
        5. Backup and Recovery
        6. Security
        7. Data Sharing Among Applications
        8. Network Traffic
        9. Record Aggregation
        10. Bet Your Career on Choosing the Right System
      3. Planning for Client/Server
        1. Field and Table Names
        2. Reserved Words
        3. Case Sensitivity
        4. Query Processing on the Server
      4. Knowing What to Watch for in Application Development
        1. Limiting Your Data
        2. Using Combo Boxes
        3. Using Access-Specific and User-Defined Functions
        4. Creating Heterogeneous and Cross-Database Joins
        5. Dealing with OLE Objects
        6. Using Local Tables for Static Information
      5. Converting Existing Applications
        1. Starting with a Well-Designed Database
        2. Using Timestamp Fields
        3. Cleaning Up Queries
        4. Reworking Forms
        5. Developing Advanced Applications
          1. Using SQL Pass-Through
          2. Using Views
          3. Using Stored Procedures
          4. Using In-Line Functions
        6. Working with Current Access Security
        7. Upsizing Access Databases
          1. Preparing to Upsize
          2. Setting Up the ODBC Data Source
          3. Exporting Tables
          4. Rebuilding Indexes and Relationships
          5. Re-creating Defaults, Rules, and Triggers
          6. Attaching to Server Tables
        8. Using the Upsizing Wizard
          1. Creating a Project from an Application
      6. Distributing a Client/Server Solution
        1. Programmatically Setting Up an ODBC Data Source
        2. Re-creating a SQL Database with Server Scripts
        3. Loading Existing Data into SQL Server
      7. Keeping Certain Issues in Mind with Access and SQL Server
      8. Summary
    5. 24. Developing SQL Server Projects Using ADPs
      1. Understanding Project File Architecture
        1. Understanding OLE DB
        2. Linking to Data
        3. Data Links and Access Projects
        4. Data Links and VBA Code
        5. The Microsoft Data Engine
        6. Objects on a SQL Server
      2. Working with Projects
        1. Creating a New Project
        2. Project Properties
        3. Securing a Project
      3. Building a Client/Server Application
        1. Working with Tables
        2. Naming Conventions for Objects
        3. Server Data Types
        4. Using Constraints
          1. Check Constraints
          2. Default Constraints
          3. Primary Key Constraints
          4. Foreign Key Constraints
          5. Unique Constraints
        5. Using Triggers
        6. Optimizing Data Access
          1. Use Standard, Normalized Database Structures
          2. Use Relationships
          3. Use Stored Procedures and Triggers
          4. Balance Your Use of Indexes
          5. Use the Most Appropriate Data Types
          6. Always Put Timestamp Fields in Project Tables
      4. Working with Views
      5. Working with Stored Procedures
        1. Creating Stored Procedures
        2. Comparing Stored Procedure and Access Syntax
      6. Summary
  11. V. Adding Finishing Touches
    1. 25. Startup Checking System Routines Using ADO
      1. Performing Startup System Checks
      2. Setting and Retrieving System Settings
      3. Notifying and Logging Users Out of an Application
        1. Keeping Users Out at Startup Time
        2. Logging Users Out in the Middle of the Application
        3. Setting the Flag File to Log Users Out of the Back End
      4. Testing Table Links at Startup
        1. Linking and Unlinking Tables in a Jet Back End in the Application’s Folder
        2. Finding the Jet Back End with the OpenFile API Call
      5. Testing and Repairing Corrupted Jet Back-End Databases
      6. Checking and Notifying Users of a New Version
      7. Summary
    2. 26. Creating Maintenance Routines
      1. Creating an Export Dialog to Export Tables
        1. Examining the Export Utility’s Features
        2. Examining the Code Behind the Export Utility
      2. Compacting and Repairing the Back End on Demand
      3. Creating a Generic Code Table Editor
      4. Replicating Tables from Back End to Front End for Better Performance
        1. Creating a Replicated Table Editor
        2. Looking at Startup Routines for Replicating Semi-Static Data
      5. Summary
  12. VI. Appendixes for Web Site
    1. A. Debugging Code in Access 2002
      1. Setting the Correct Module Options for Maximum Debugging Power
      2. Using the Immediate Window
        1. Printing Data to the Immediate Window From Your Application
        2. Displaying Data While in the Immediate Window
        3. Assigning Values to Variables and Objects in the Immediate Window
        4. Running Code from the Immediate Window
      3. Stopping Program Execution
        1. Using the Stop Statement
        2. Using Breakpoints
        3. Using Debug.Assert
      4. Debugging One Step at a Time
        1. Stepping into Code Line by Line
        2. Stepping Through Code with Step Over
        3. Bailing Out of a Routine with Step Out
        4. Skipping Tested Code with Run to Cursor
      5. Viewing the Order of Procedure Calls
      6. Watching Expressions During Program Execution
        1. Keeping in Touch with the Locals
        2. Taking a Quick Look with the Quick Watch Dialog
        3. Adding and Viewing Expressions in the Watches Window
        4. Setting Break Conditions and Editing Expressions
      7. Controlling Code with Conditional Compilation Commands
    2. B. Getting Started with ActiveX Controls
      1. Working with ActiveX Controls
        1. Looking at the ActiveX Control Shipped with Access
        2. Placing an ActiveX Control on a Form
        3. Setting Properties on an ActiveX Control at Design Time
      2. Counting the Days with the Calendar Control
        1. Understanding the Calendar Control’s Properties
          1. Understanding the Calendar Control’s Methods
          2. Understanding the Calendar Control’s Events
        2. Programming VBA with the Calendar Control
      3. Using a Standard Interface with the Common Dialog Control
        1. Locating a File with the Common Dialog Control
        2. Changing the Default System Printer with the Common Dialog Control
    3. C. Working with Data Access Objects
      1. Understanding Data Access Objects
        1. Understanding Your Database’s Anatomy
        2. Getting Started with DAO
        3. Getting into Your Database
        4. Examining Your Database
        5. Examining Table Attributes
        6. Examining Query Types
      2. Creating a Database with DAO
        1. Creating the Database Object
          1. Opening Existing Databases
          2. Creating New Databases
          3. Compacting Existing Databases
        2. Copying Table Structures
        3. Fielding the Field Object
        4. Copying Queries
          1. Creating Temporary Queries
          2. Compiling Queries
        5. Copying Relationships
        6. Copying Data
      3. Increasing Speed with Transactions
      4. Using Custom Properties
    4. D. Programming Office Command Bars and Other Office Components
      1. Using Command Bars
        1. The Customize Dialog’s Toolbars Page
        2. The Customize Dialog’s Commands Page
        3. The Customize Dialog’s Options Page
        4. Creating a Toolbar Through the User Interface
          1. Adding Existing Commands to the Toolbar
          2. Specifying Button Groupings
          3. Adding a Custom Button That Calls Your Own VBA Function
          4. Creating Menus and Shortcut-Menu–Type Command Bars
          5. Adding Command Bars to a Form
      2. Working with Command Bars Through VBA
        1. Looking at the Command Bars Object Model
        2. Modifying a Command Bar Programmatically
          1. Looking at the Forms Involved
          2. Putting the Pieces Together with VBA
      3. Working with the Office Assistant
        1. Looking at the Office Assistant Object Model
        2. Using the Office Assistant as Help for a Form
    5. E. Access 2002 and Jet 4 Errors
  13. Accolades for F. Scott Barker’s Microsoft Access 2002 Power Programming

Product information

  • Title: F. Scott Barker’s Microsoft Access 2002 Power Programming
  • Author(s):
  • Release date: September 2001
  • Publisher(s): Sams
  • ISBN: None