Cover image for Alison Balter's mastering Access 2002 enterprise development
Alison Balter's mastering Access 2002 enterprise development
Balter, Alison.
Personal Author:
Publication Information:
Indianapolis, Ind. : Sams ; [London] : [Pearson Education] (distributor), [2002]

Physical Description:
xxi, 802 pages : illustrations ; 24 cm + 1 CD-ROM (4 3/4 in.)
Title Subject:
Format :


Call Number
Material Type
Home Location
Item Holds
QA76.9.D3 B33 2002 Book and Software Set Central Closed Stacks

On Order



Best-selling author, Alison Balter, presents the tricks of the trade that will ensure your Access 2002 Enterprise Applications are optimized for performance.

Author Notes

Alison Balter is the founder of InfoTechnology Partners, Inc.

Table of Contents

Introductionp. 1
Part I Client/Server Developmentp. 5
1 A Strategy for Developing Access Applicationsp. 7
Why This Chapter Is Importantp. 7
Access as a Development Toolp. 8
Access as a Scalable Productp. 12
Splitting Databases into Tables and Other Objectsp. 13
Understanding the Access Runtime Enginep. 15
Using an EXE Versus Access Database: What It Means to Youp. 20
Understanding the Importance of Securing Your Databasep. 20
Using Access as a Front Endp. 21
Summaryp. 24
2 Developing Multiuser Applicationsp. 27
Why This Chapter Is Importantp. 27
Designing Your Application with Multiuser Issues in Mindp. 27
Understanding Access's Locking Mechanismsp. 39
Locking and Refreshing Strategiesp. 39
Form-Locking Strategiesp. 44
Recordset Lockingp. 48
Effectively Handling Locking Conflictsp. 53
Testing a Record for Locking Statusp. 60
Using Code to Refresh or Requeryp. 62
Understanding the .LDB Filep. 63
The User Rosterp. 63
Creating Custom Countersp. 64
Using Unbound Formsp. 64
Using Replication to Improve Performancep. 65
Summaryp. 65
3 Introduction to Client/Server Development Techniquesp. 67
Why This Chapter Is Importantp. 67
Understanding the Client/Server Modelp. 67
Deciding Whether to Use the Client/Server Modelp. 68
The Roles Access Plays in the Application Design Modelp. 73
Learning the Client/Server Buzzwordsp. 76
Upsizing: What to Worry Aboutp. 78
Proactively Preparing for Upsizingp. 80
Using the Upsizing Wizardp. 81
Defining an ODBC Data Sourcep. 84
Connecting to a Database Serverp. 89
Summaryp. 94
4 SQL Server Basicsp. 95
Why This Chapter Is Importantp. 95
Editions of SQL Server Availablep. 95
Overview of the SQL Server Componentsp. 96
Introduction to the Enterprise Managerp. 102
Introduction to the Query Analyzerp. 113
Introduction to the Profilerp. 116
Introduction to Data Transformation Servicesp. 119
Configuring the Serverp. 124
Designing and Creating a SQL Server Databasep. 126
Summaryp. 137
5 SQL Server Tables and Database Diagramsp. 139
Why This Chapter Is Importantp. 139
Creating SQL Server Tablesp. 139
Working with Constraintsp. 142
Identity Columnsp. 149
Working with Computed Columnsp. 149
Working with User-Defined Data Typesp. 150
Adding and Modifying Indexesp. 152
An Introduction to Triggersp. 153
An Introduction to Relationshipsp. 154
Establishing Relationships Between SQL Server Tablesp. 156
Establishing Referential Integrityp. 162
Looking at the Benefits of Relationshipsp. 165
Examining Indexes and Relationshipsp. 166
Summaryp. 166
6 Mastering T-SQLp. 167
Why This Chapter Is Importantp. 167
Introduction to T-SQLp. 167
The SELECT Statementp. 167
Aggregate Functionsp. 177
The HAVING Clausep. 182
Top Values Queriesp. 183
Join Typesp. 184
Modifying Data with Action Queriesp. 189
T-SQL Functionsp. 191
Summaryp. 205
7 Working with SQL Server Viewsp. 207
Why This Chapter Is Importantp. 207
Creating a Simple Viewp. 208
Creating Complex Viewsp. 219
Using Views to Secure Datap. 222
Modifying Data in a Viewp. 222
Modifying Viewsp. 223
Renaming Viewsp. 223
Deleting Viewsp. 224
Indexed Viewsp. 225
Optimizing the Views That You Buildp. 229
Summaryp. 236
8 Designing SQL Server Stored Procedures, User-Defined Functions, and Triggersp. 237
Why This Chapter Is Importantp. 237
Stored Procedure Basicsp. 238
Declaring and Working with Variablesp. 246
Controlling the Flowp. 248
The SET NOCOUNT Statementp. 258
Using the @@Functionsp. 259
Working with Parametersp. 262
Modifying Data with Stored Proceduresp. 266
Errors and Error Handlingp. 269
Stored Procedures and Transactionsp. 272
Stored Procedures and Temporary Tablesp. 273
Stored Procedures and Securityp. 274
Debugging the Stored Procedures That You Buildp. 275
Building and Working with User-Defined Functionsp. 276
Creating and Working with Triggersp. 278
Summaryp. 281
9 SQL Server Securityp. 283
Why This Chapter Is Importantp. 283
Security Introducedp. 283
Authenticationp. 284
Creating Rolesp. 290
Permissions Validationp. 297
Administering Statement Permissionsp. 304
Creating and Working with Application Rolesp. 304
Access Security and Its Role in a Client/Server Applicationp. 306
Summaryp. 307
10 ADO and SQL Serverp. 309
Why This Chapter Is Importantp. 309
A History of Data Accessp. 309
ADO Quick Reviewp. 310
ADO and Stored Proceduresp. 325
Advanced ADO Techniquesp. 336
Summaryp. 346
11 Developing an MDB Client/Server Application with Linked Tablesp. 347
Why This Chapter Is Importantp. 347
What Is a Linked Client/Server Application?p. 347
How Linked Table Applications Workp. 348
Working with Linked Tablesp. 348
Using Pass-Through Queriesp. 353
Executing Stored Proceduresp. 358
Building Client/Server-Friendly Formsp. 360
Working with Local Tablesp. 379
Populating Combo Boxes and List Boxes Dynamicallyp. 381
Building Client/Server-Friendly Reportsp. 382
Linked Table Gotchasp. 391
Summaryp. 391
12 Developing an ADP Applicationp. 393
Why This Chapter Is Importantp. 393
What Are Access Projects?p. 393
Creating an Access Projectp. 394
Maintaining a SQL Server Database from an Access Projectp. 397
Building Access Project Formsp. 411
Special Access Project Form Properties and Techniquesp. 422
Building Access Data Project Reportsp. 430
ADP Gotchasp. 433
Summaryp. 434
13 Building Unbound Applicationsp. 435
Why This Chapter Is Importantp. 435
The Benefits of Unbound Applicationsp. 435
The Unbound Formp. 435
The Unbound Form and Pass-Through Queriesp. 444
The Unbound Form and Stored Proceduresp. 451
Summaryp. 461
14 Building N-Tier Applicationsp. 463
Why This Chapter Is Importantp. 463
The Basics of N-Tier Developmentp. 463
Access's Role in the N-Tier Modelp. 464
Access and a Logical Three-Tier Modelp. 465
Building the Middle-Tier Componentp. 476
Access and a Physical Three-Tier Modelp. 481
Summaryp. 489
15 Configuring, Maintaining, and Tuning SQL Serverp. 491
Why This Chapter Is Importantp. 491
Hardware Selection and Tuningp. 491
SQL Server Configuration and Tuningp. 493
Database Maintenancep. 495
Table and Object Maintenancep. 505
Job Maintenancep. 505
Performance Monitoringp. 508
Summaryp. 521
Part II Access Enterprise Techniquesp. 523
16 Transaction Processingp. 525
Why This Chapter Is Importantp. 525
Understanding the Benefitsp. 526
Modifying the Default Behaviorp. 527
Implementing Explicit Transaction Processingp. 529
Looking at Transaction Processing Issuesp. 531
Using Transaction Processing in a Multiuser Environmentp. 536
Using Transaction Processing in a Client/Server Environmentp. 541
Practical Examples: Using Transaction Processing to Improve the Integrity of Your Applicationsp. 544
Summaryp. 546
17 Access Replication Made Easyp. 547
Why This Chapter Is Importantp. 547
Uses of Replicationp. 547
Understanding When Replication Isn't Appropriatep. 549
Understanding the Implementation of Replicationp. 549
Understanding the Replication Architecture: What Makes Replication Tick?p. 551
Understanding Replication Topologiesp. 553
Changes That Replication Makes to Your Databasep. 555
Making a Database Replicablep. 557
Preventing Objects from Being Replicatedp. 559
Creating Additional Replicasp. 560
Synchronizing Replicasp. 560
Resolving Replication Conflictsp. 561
Using the Replication Managerp. 563
Using Partial Replicationp. 575
Implementing Replication Using Codep. 577
Practical Examples: Managing the Time and Billing Application with Replicationp. 582
Summaryp. 583
18 Taking Advantage of the Microsoft Office XP Developerp. 585
Why This Chapter Is Importantp. 585
What's in the Microsoft Office XP Developerp. 585
The Access Runtimep. 586
The Replication Managerp. 595
The Code Librarianp. 595
The Code Librarian Viewerp. 603
The Packaging Wizardp. 603
The Code Commentor and Error Handler Add-Inp. 613
The VBA String Editorp. 615
Summaryp. 616
19 Source Code Controlp. 617
Why This Chapter Is Importantp. 617
How Do I Install Visual SourceSafe?p. 617
Using Visual SourceSafe: An Overviewp. 618
The Logistics of Managing a Project with Visual SourceSafep. 619
Leveraging the Power of Visual SourceSafep. 627
Changes Visual SourceSafe Makes to Access's Behaviorp. 629
Understanding the Limitations of Visual SourceSafep. 631
Summaryp. 631
Part III Access and The Internetp. 633
20 Publishing Data on the Webp. 635
Why This Chapter Is Importantp. 635
Saving Database Objects as HTMLp. 635
Linking to HTML Filesp. 639
Importing HTML Filesp. 641
Working with Hyperlinksp. 642
Static Versus Dynamic Web Pagesp. 645
Working with HTX/IDC Filesp. 646
Working with Active Server Pages (.asp Files)p. 647
Testing ASP and HTX/IDC Filesp. 647
Summaryp. 650
21 XML Support in Microsoft Accessp. 651
Why This Chapter Is Importantp. 651
The Basics of XMLp. 651
Exporting Data to XMLp. 653
Working with XML Style Sheets (XSL or XSLT)p. 655
Working with XML Schemas--The XSD Filep. 658
Exporting Gotchasp. 660
Exporting Forms and Reports to XMLp. 661
Importing XML Data into Accessp. 670
Programmatically Importing and Exporting XML Datap. 671
Using ADO Code to Work with XMLp. 672
Summaryp. 675
22 Data Access Pagesp. 677
Why This Chapter Is Importantp. 677
Exploring the Uses of Data Access Pagesp. 677
Creating a Simple Data Access Pagep. 679
Creating a Complex Data Access Pagep. 691
Working with Data Access Page Controlsp. 701
Deleting Data Access Pagesp. 707
Deploying Data Access Pagesp. 707
An Introduction to Scripting Data Access Pagesp. 718
Summaryp. 721
23 SQL Server and the Internetp. 723
Why This Chapter Is Importantp. 723
The Web Assistant Wizardp. 723
Web-Related Stored Proceduresp. 735
Generating XML from SQL Queriesp. 738
Summaryp. 745
Indexp. 747