You are previewing Alison Balter's Mastering Access 2002 Enterprise Development.
O'Reilly logo
Alison Balter's Mastering Access 2002 Enterprise Development

Book Description

Developing applications for the enterprise introduces challenges quite different from those associated with developing applications for desktops or small departments. This book offers a no-nonsense approach to enterprise development using Access 2002 as a front-end. it is written in a practical style and easily transitions the client/server neophyte to the sophisticated world of enterprise development. Although the book focuses on client/server development, the text covers other enterprise topics such as replication, trasaction processing, and data access pages. This book goes behond the basics covered by the many introductory books, but offers an easier read than the expert books available.

Table of Contents

  1. Copyright
  2. About the Author
  3. Acknowledgments
  4. Tell Us What You Think!
  5. Introduction
  6. Client/Server Development
    1. A Strategy for Developing Access Applications
      1. Why This Chapter Is Important
      2. Access as a Development Tool
      3. Access as a Scalable Product
      4. Splitting Databases into Tables and Other Objects
      5. Understanding the Access Runtime Engine
      6. Using an EXE Versus Access Database: What It Means to You
      7. Understanding the Importance of Securing Your Database
      8. Using Access as a Front End
      9. Summary
    2. Developing Multiuser Applications
      1. Why This Chapter Is Important
      2. Designing Your Application with Multiuser Issues in Mind
      3. Understanding Access's Locking Mechanisms
      4. Locking and Refreshing Strategies
      5. Form-Locking Strategies
      6. Recordset Locking
      7. Effectively Handling Locking Conflicts
      8. Testing a Record for Locking Status
      9. Using Code to Refresh or Requery
      10. Understanding the .LDB File
      11. The User Roster
      12. Creating Custom Counters
      13. Using Unbound Forms
      14. Using Replication to Improve Performance
      15. Summary
    3. Introduction to Client/Server Development Techniques
      1. Why This Chapter Is Important
      2. Understanding the Client/Server Model
      3. Deciding Whether to Use the Client/Server Model
      4. The Roles Access Plays in the Application Design Model
      5. Learning the Client/Server Buzzwords
      6. Upsizing: What to Worry About
      7. Proactively Preparing for Upsizing
      8. Using the Upsizing Wizard
      9. Defining an ODBC Data Source
      10. Connecting to a Database Server
      11. Summary
    4. SQL Server Basics
      1. Why This Chapter Is Important
      2. Editions of SQL Server Available
      3. Overview of the SQL Server Components
      4. Introduction to the Enterprise Manager
      5. Introduction to the Query Analyzer
      6. Introduction to the Profiler
      7. Introduction to Data Transformation Services
      8. Configuring the Server
      9. Designing and Creating a SQL Server Database
      10. Summary
    5. SQL Server Tables and Database Diagrams
      1. Why This Chapter Is Important
      2. Creating SQL Server Tables
      3. Working with Constraints
      4. Identity Columns
      5. Working with Computed Columns
      6. Working with User-Defined Data Types
      7. Adding and Modifying Indexes
      8. An Introduction to Triggers
      9. An Introduction to Relationships
      10. Establishing Relationships Between SQL Server Tables
      11. Establishing Referential Integrity
      12. Looking at the Benefits of Relationships
      13. Examining Indexes and Relationships
      14. Summary
    6. Mastering T-SQL
      1. Why This Chapter Is Important
      2. Introduction to T-SQL
      3. The SELECT Statement
      4. Aggregate Functions
      5. The HAVING Clause
      6. Top Values Queries
      7. Join Types
      8. Modifying Data with Action Queries
      9. T-SQL Functions
      10. Summary
    7. Working with SQL Server Views
      1. Why This Chapter Is Important
      2. Creating a Simple View
      3. Creating Complex Views
      4. Using Views to Secure Data
      5. Modifying Data in a View
      6. Modifying Views
      7. Renaming Views
      8. Deleting Views
      9. Indexed Views
      10. Optimizing the Views That You Build
      11. Summary
    8. Designing SQL Server Stored Procedures, User-Defined Functions, and Triggers
      1. Why This Chapter Is Important
      2. Stored Procedure Basics
      3. Declaring and Working with Variables
      4. Controlling the Flow
      5. The SET NOCOUNT Statement
      6. Using the @@Functions
      7. Working with Parameters
      8. Modifying Data with Stored Procedures
      9. Errors and Error Handling
      10. Stored Procedures and Transactions
      11. Stored Procedures and Temporary Tables
      12. Stored Procedures and Security
      13. Debugging the Stored Procedures That You Build
      14. Building and Working with User-Defined Functions
      15. Creating and Working with Triggers
      16. Summary
    9. SQL Server Security
      1. Why This Chapter Is Important
      2. Security Introduced
      3. Authentication
      4. Creating Roles
      5. Permissions Validation
      6. Administering Statement Permissions
      7. Creating and Working with Application Roles
      8. Access Security and Its Role in a Client/Server Application
      9. Summary
    10. ADO and SQL Server
      1. Why This Chapter Is Important
      2. A History of Data Access
      3. ADO Quick Review
      4. ADO and Stored Procedures
      5. Advanced ADO Techniques
      6. Summary
    11. Developing an MDB Client/Server Application with Linked Tables
      1. Why This Chapter Is Important
      2. What Is a Linked Client/Server Application?
      3. How Linked Table Applications Work
      4. Working with Linked Tables
      5. Using Pass-Through Queries
      6. Executing Stored Procedures
      7. Building Client/Server–Friendly Forms
      8. Working with Local Tables
      9. Populating Combo Boxes and List Boxes Dynamically
      10. Building Client/Server–Friendly Reports
      11. Linked Table Gotchas
      12. Summary
    12. Developing an ADP Application
      1. Why This Chapter Is Important
      2. What Are Access Projects?
      3. Creating an Access Project
      4. Maintaining a SQL Server Database from an Access Project
      5. Building Access Project Forms
      6. Special Access Project Form Properties and Techniques
      7. Building Access Data Project Reports
      8. ADP Gotchas
      9. Summary
    13. Building Unbound Applications
      1. Why This Chapter Is Important
      2. The Benefits of Unbound Applications
      3. The Unbound Form
      4. The Unbound Form and Pass-Through Queries
      5. The Unbound Form and Stored Procedures
      6. Summary
    14. Building N-Tier Applications
      1. Why This Chapter Is Important
      2. The Basics of N-Tier Development
      3. Access's Role in the N-Tier Model
      4. Access and a Logical Three-Tier Model
      5. Building the Middle-Tier Component
      6. Access and a Physical Three-Tier Model
      7. Summary
    15. Configuring, Maintaining, and Tuning SQL Server
      1. Why This Chapter Is Important
      2. Hardware Selection and Tuning
      3. SQL Server Configuration and Tuning
      4. Database Maintenance
      5. Table and Object Maintenance
      6. Job Maintenance
      7. Performance Monitoring
      8. Summary
  7. Access Enterprise Techniques
    1. Transaction Processing
      1. Why This Chapter Is Important
      2. Understanding the Benefits
      3. Modifying the Default Behavior
      4. Implementing Explicit Transaction Processing
      5. Looking at Transaction Processing Issues
      6. Using Transaction Processing in a Multiuser Environment
      7. Using Transaction Processing in a Client/Server Environment
      8. Practical Examples: Using Transaction Processing to Improve the Integrity of Your Applications
      9. Summary
    2. Access Replication Made Easy
      1. Why This Chapter Is Important
      2. Uses of Replication
      3. Understanding When Replication Isn't Appropriate
      4. Understanding the Implementation of Replication
      5. Understanding the Replication Architecture: What Makes Replication Tick?
      6. Understanding Replication Topologies
      7. Changes That Replication Makes to Your Database
      8. Making a Database Replicable
      9. Preventing Objects from Being Replicated
      10. Creating Additional Replicas
      11. Synchronizing Replicas
      12. Resolving Replication Conflicts
      13. Using the Replication Manager
      14. Using Partial Replication
      15. Implementing Replication Using Code
      16. Practical Examples: Managing the Time and Billing Application with Replication
      17. Summary
    3. Taking Advantage of the Microsoft Office XP Developer
      1. Why This Chapter Is Important
      2. What's in the Microsoft Office XP Developer
      3. The Access Runtime
      4. The Replication Manager
      5. The Code Librarian
      6. The Code Librarian Viewer
      7. The Packaging Wizard
      8. The Code Commentor and Error Handler Add-In
      9. The VBA String Editor
      10. Summary
    4. Source Code Control
      1. Why This Chapter Is Important
      2. How Do I Install Visual SourceSafe?
      3. Using Visual SourceSafe: An Overview
      4. The Logistics of Managing a Project with Visual SourceSafe
      5. Leveraging the Power of Visual SourceSafe
      6. Changes Visual SourceSafe Makes to Access's Behavior
      7. Understanding the Limitations of Visual SourceSafe
      8. Summary
  8. Access and the Internet
    1. Publishing Data on the Web
      1. Why This Chapter Is Important
      2. Saving Database Objects as HTML
      3. Linking to HTML Files
      4. Importing HTML Files
      5. Working with Hyperlinks
      6. Static Versus Dynamic Web Pages
      7. Working with HTX/IDC Files
      8. Working with Active Server Pages (.asp Files)
      9. Testing ASP and HTX/IDC Files
      10. Summary
    2. XML Support in Microsoft Access
      1. Why This Chapter Is Important
      2. The Basics of XML
      3. Exporting Data to XML
      4. Working with XML Style Sheets (XSL or XSLT)
      5. Working with XML Schemas—The XSD File
      6. Exporting Gotchas
      7. Exporting Forms and Reports to XML
      8. Importing XML Data into Access
      9. Programmatically Importing and Exporting XML Data
      10. Using ADO Code to Work with XML
      11. Summary
    3. Data Access Pages
      1. Why This Chapter Is Important
      2. Exploring the Uses of Data Access Pages
      3. Creating a Simple Data Access Page
      4. Creating a Complex Data Access Page
      5. Working with Data Access Page Controls
      6. Deleting Data Access Pages
      7. Deploying Data Access Pages
      8. An Introduction to Scripting Data Access Pages
      9. Summary
    4. SQL Server and the Internet
      1. Why This Chapter Is Important
      2. The Web Assistant Wizard
      3. Web-Related Stored Procedures
      4. Generating XML from SQL Queries
      5. Summary
  9. Index