Alison Balter's Mastering Access 2002 Enterprise Development

Alison Balter is the president of InfoTechnology Partners, Inc., a computer consulting firm based in Camarillo, California. Alison is a highly experienced independent trainer and consultant specializing in Windows applications training and development. During her 19 years in the computer industry, she has trained and consulted with many corporations and government agencies. Since Alison founded InfoTechnology Partners, Inc. (formerly Marina Consulting Group) in 1990, its client base has expanded to include major corporations and government agencies such as Shell Oil, Accenture, Northrop, the Drug Enforcement Administration, Prudential Insurance, Transamerica Insurance, Fox Broadcasting, the United States Navy, and others. InfoTechnology Partners, Inc. is a Microsoft Certified Partner, and Alison is a Microsoft Certified Professional. Alison was one of the first professionals in the computer industry to become a Microsoft Certified Solutions Developer (MCSD). Alison is the author of more than 300 internationally marketed computer training videos, including 7 Access 2.0 videos, 11 Access 95 videos, 13 Access 97 videos, 18 Access 2000 videos, and 35 Access 2002 videos. These videos are available by contacting Alison¿s company, InfoTechnology Partners, Inc. Alison travels throughout North America giving training seminars in Microsoft Access, Visual Basic, Microsoft SQL Server, Visual Studio .NET, Visual InterDev, and Visual Basic for Applications. She is also featured in several live satellite television broadcasts for National Technological University. Alison is a regular contributing columnist for Access/Office/VB Advisor as well as other computer publications. She is also a regular on the Access, Visual Basic, SQL Server, and Visual InterDev national speaker circuits. She was one of four speakers on the Visual Basic 4.0 and 5.0 World Tours seminar series cosponsored by Application Developers Training Company and Microsoft. Alison is also a coauthor of three other Access books published by Sams Publishing: Essential Access 95, Access 95 Unleashed, and Access 97 Unleashed. An active participant in many user groups and other organizations, Alison is a past president of the Independent Computer Consultants Association of Los Angeles and of the Los Angeles Clippers Users¿ Group. On a personal note, Alison keeps herself busy skiing, ice skating, horseback riding, running, lifting weights, hiking, traveling, and dancing. She most enjoys spending time with her husband, Dan, their daughter Alexis, and their son Brendan. Alison¿s firm, InfoTechnology Partners, Inc., is available for consulting work and onsite training in Microsoft Access, Visual Basic, SQL Server, Visual Studio .NET, and Visual InterDev, as well as for Windows NT, Windows 95/98, Windows 2000, Windows XP, Windows .NET Server, PC networking, and Microsoft Exchange Server. Contact Alison by electronic mail at Alison@InfoTechnologyPartners.com, or visit InfoTechnology Partners¿ Web site at www.InfoTechnologyPartners.com.

(NOTE: Each chapter begins with Why This Chapter is Important and concludes with a Summary.)

Introduction.
I. CLIENT/SERVER DEVELOPMENT.

1. A Strategy for Developing Access Applications.


Access as a Development Tool. Access as a Scalable Product. Splitting Databases into Tables and Other Objects. Understanding the Access Runtime Engine. Using an EXE Versus Access Database: What It Means to You. Understanding the Importance of Securing Your Database. Using Access as a Front End.

2. Developing Multiuser Applications.


Designing Your Application with Multiuser Issues in Mind. Understanding Access's Locking Mechanisms. Locking and Refreshing Strategies. Form-Locking Strategies. Recordset Locking. Effectively Handling Locking Conflicts. Testing a Record for Locking Status. Using Code to Refresh or Requery. Understanding the .LDB File. The User Roster. Creating Custom Counters. Using Unbound Forms. Using Replication to Improve Performance.

3. Introduction to Client/Server Development Techniques.


Understanding the Client/Server Model. Deciding Whether to Use the Client/Server Model. The Roles Access Plays in the Application Design Model. Learning the Client/Server Buzzwords. Upsizing: What to Worry About. Proactively Preparing for Upsizing. Using the Upsizing Wizard. Defining an ODBC Data Source. Connecting to a Database Server.

4. SQL Server Basics.


Editions of SQL Server Available. Overview of the SQL Server Components. Introduction to the Enterprise Manager. Introduction to the Query Analyzer. Introduction to the Profiler. Introduction to Data Transformation Services. Configuring the Server. Designing and Creating a SQL Server Database.

5. SQL Server Tables and Database Diagrams.


Creating SQL Server Tables. Working with Constraints. Identity Columns. Working with Computed Columns. Working with User-Defined Data Types. Adding and Modifying Indexes. An Introduction to Triggers. An Introduction to Relationships. Establishing Relationships Between SQL Server Tables. Establishing Referential Integrity. Looking at the Benefits of Relationships. Examining Indexes and Relationships.

6. Mastering T-SQL.


Introduction to T-SQL. The SELECT Statement. Aggregate Functions. The HAVING Clause. Top Values Queries. Join Types. Modifying Data with Action Queries. T-SQL Functions.

7. Working with SQL Server Views.


Creating a Simple View. Creating Complex Views. Using Views to Secure Data. Modifying Data in a View. Modifying Views. Renaming Views. Deleting Views. Indexed Views. Optimizing the Views That You Build.

8. Designing SQL Server Stored Procedures, User-Defined Functions, and Triggers.


Stored Procedure Basics. Declaring and Working with Variables. Controlling the Flow. The SET NOCOUNT Statement. Using the @@Functions. Working with Parameters. Modifying Data with Stored Procedures. Errors and Error Handling. Stored Procedures and Transactions. Stored Procedures and Temporary Tables. Stored Procedures and Security. Debugging the Stored Procedures That You Build. Building and Working with User-Defined Functions. Creating and Working with Triggers.

9. SQL Server Security.


Security Introduced. Authentication. Creating Roles. Permissions Validation. Administering Statement Permissions. Creating and Working with Application Roles. Access Security and Its Role in a Client/Server Application.

10. ADO and SQL Server.


A History of Data Access. ADO Quick Review. ADO and Stored Procedures. Advanced ADO Techniques.

11. Developing an MDB Client/Server Application with Linked Tables.


What Is a Linked Client/Server Application? How Linked Table Applications Work. Working with Linked Tables. Using Pass-Through Queries. Executing Stored Procedures. Building Client/Server-Friendly Forms. Working with Local Tables. Populating Combo Boxes and List Boxes Dynamically. Building Client/Server-Friendly Reports. Linked Table Gotchas.

12. Developing an ADP Application.


What Are Access Projects? Creating an Access Project. Maintaining a SQL Server Database from an Access Project. Building Access Project Forms. Special Access Project Form Properties and Techniques. Building Access Data Project Reports. ADP Gotchas.

13. Building Unbound Applications.


The Benefits of Unbound Applications. The Unbound Form. The Unbound Form and Pass-Through Queries. The Unbound Form and Stored Procedures.

14. Building N-Tier Applications.


The Basics of N-Tier Development. Access's Role in the N-Tier Model. Access and a Logical Three-Tier Model. Building the Middle-Tier Component. Access and a Physical Three-Tier Model.

15. Configuring, Maintaining, and Tuning SQL Server.


Hardware Selection and Tuning. SQL Server Configuration and Tuning. Database Maintenance. Table and Object Maintenance. Job Maintenance. Performance Monitoring.

II. ACCESS ENTERPRISE TECHNIQUES.

16. Transaction Processing.


Understanding the Benefits. Modifying the Default Behavior. Implementing Explicit Transaction Processing. Looking at Transaction Processing Issues. Using Transaction Processing in a Multiuser Environment. Using Transaction Processing in a Client/Server Environment. Practical Examples: Using Transaction Processing to Improve the Integrity of Your Applications.

17. Access Replication Made Easy.


Uses of Replication. Understanding When Replication Isn't Appropriate. Understanding the Implementation of Replication. Understanding the Replication Architecture: What Makes Replication Tick? Understanding Replication Topologies. Changes That Replication Makes to Your Database. Making a Database Replicable. Preventing Objects from Being Replicated. Creating Additional Replicas. Synchronizing Replicas. Resolving Replication Conflicts. Using the Replication Manager. Using Partial Replication. Implementing Replication Using Code. Practical Examples: Managing the Time and Billing Application with Replication.

18. Taking Advantage of the Microsoft Office XP Developer.


What's in the Microsoft Office XP Developer. The Access Runtime. The Replication Manager. The Code Librarian. The Code Librarian Viewer. The Packaging Wizard. The Code Commentor and Error Handler Add-In. The VBA String Editor.

19. Source Code Control.


How Do I Install Visual SourceSafe? Using Visual SourceSafe: An Overview. The Logistics of Managing a Project with Visual SourceSafe. Leveraging the Power of Visual SourceSafe. Changes Visual SourceSafe Makes to Access's Behavior. Understanding the Limitations of Visual SourceSafe.

20. Publishing Data on the Web.


Saving Database Objects as HTML. Linking to HTML Files. Importing HTML Files. Working with Hyperlinks. Static Versus Dynamic Web Pages. Working with HTX/IDC Files. Working with Active Server Pages (.asp Files). Testing ASP and HTX/IDC Files.

21. XML Support in Microsoft Access.


The Basics of XML. Exporting Data to XML. Working with XML Style Sheets (XSL or XSLT). Working with XML Schemas-The XSD File. Exporting Gotchas. Exporting Forms and Reports to XML. Importing XML Data into Access. Programmatically Importing and Exporting XML Data. Using ADO Code to Work with XML.

22. Data Access Pages.


Exploring the Uses of Data Access Pages. Creating a Simple Data Access Page. Creating a Complex Data Access Page. Working with Data Access Page Controls. Deleting Data Access Pages. Deploying Data Access Pages. An Introduction to Scripting Data Access Pages.

23. SQL Server and the Internet.


The Web Assistant Wizard. Web-Related Stored Procedures. Generating XML from SQL Queries.

Index.