Cover image for Microsoft Access 2000 developer's guide
Microsoft Access 2000 developer's guide
Jones, Edward, 1952-
Personal Author:
Publication Information:
Foster City, CA : IDG Books Worldwide, [1999]

Physical Description:
xxxiv, 849 pages : illustrations ; 23 cm + 1 computer optical disc (4 3/4 in.)
General Note:
Includes index.
Title Subject:
Subject Term:
Format :


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

On Order



A high-level introduction to application development using Access 2000. It is accompanied by a CD-ROM containing the source code and databases for all the examples in the book, and further information.

Author Notes

Edward Jones is the author of more than 30 books about database, spreadsheet, and word processing programs, among other technology topics. Presently, he is a Senior Program Analyst at Wilmer, Cutler and Pickering, a Washington D.C.-based international law firm. He has performed database development and consulting services for law firms and government agencies and has worked as a technical editor on numerous computer books.

Table of Contents

Prefacep. ix
Acknowledgmentsp. xvii
Part I Introduction to Access Development
Chapter 1 A Developer's Overview of Accessp. 3
What Is Access?p. 3
The History of Accessp. 4
What's New in Access 2000p. 6
How Access Compares to the Competitionp. 7
Corporate Developer Versus Independent Consultantp. 8
The Corporate Developerp. 8
The Independent Consultantp. 9
Chapter 2 Database Design Issuesp. 11
Design Fundamentals: From Paper to Realityp. 11
Client Consultationp. 13
Output Definitionp. 13
Data Definitionp. 14
Table Definition and Normalizationp. 15
Input (Form) Definitionp. 17
Application Designp. 17
Test and Debugp. 17
Understanding Primary Keysp. 18
Understanding Relationships Between Tablesp. 19
Using the Database Wizardsp. 21
Database Maintenancep. 26
Compacting a Databasep. 27
Repairing a Databasep. 29
Adding Icons for Compacting or Repairingp. 30
Part II Working with Access Objects
Chapter 3 Access Tablesp. 33
A Developer's Overview of Table Designp. 33
Assigning Field Namesp. 35
Selecting Data Typesp. 37
Adding a Descriptionp. 39
Using Field Propertiesp. 39
Designating Fields as Primary Keysp. 51
Rearranging and Deleting Fields from a Table's Designp. 53
Saving Your New Tablep. 53
Adding Indexes to Tablesp. 54
Establishing Relationships Between Tables and Referential Integrityp. 56
Getting Normalization Help from the Table Analyzer Wizardp. 61
Using External Data Sourcesp. 62
Importing and Linking Filesp. 63
Dealing with Field Conversion Issuesp. 64
Deciding Between Importing and Linking Filesp. 66
A Real-World Example: Tracking Used in a Human Resources Applicationp. 67
Chapter 4 Access Queriesp. 71
Query Types and Their Usagep. 72
A Developer's Overview of Query Designp. 73
Creating Select Queriesp. 73
Adding Tables to Queriesp. 75
Adding Fields to Queriesp. 76
Specifying Criteria for Queriesp. 77
Choosing a Sort Orderp. 83
Hiding Fieldsp. 84
Running Queriesp. 84
Printing Query Resultsp. 85
Saving Queries for Later Usep. 86
Additional Query Design Featuresp. 86
Using Alias Names in Queriesp. 86
Using Calculations in Queriesp. 88
Calculating Totals in Queriesp. 89
Working with Action Queriesp. 92
Creating and Using Make-Table Queriesp. 93
Creating and Using Append Queriesp. 94
Creating and Using Update Queriesp. 95
Creating and Using Delete Queriesp. 97
Troubleshooting Action Queriesp. 98
Working with Parameter Queriesp. 99
Working with Relational Queriesp. 103
Creating Relational Queriesp. 104
Adding Multiple Tables to Queriesp. 104
Creating Relational Joinsp. 105
Designing Tips for Updating Fields in Relational Queriesp. 109
Editing Limitations of Relational Queriesp. 109
Changing the Default Join Typep. 110
Creating Self-Joinsp. 115
Creating Crosstab Queriesp. 116
The Structured Query Language (SQL)p. 120
SQL Language Conventionsp. 122
SQL-Specific Queriesp. 127
Microsoft Jet SQL Versus ANSI SQLp. 129
Using Queries with Forms and Reportsp. 130
Setting Query Propertiesp. 132
Optimizing the Performance of Queriesp. 137
Queries and Table Relationshipsp. 137
Queries and Indexingp. 138
The Performance Analyzerp. 139
A Real-World Example: Queries Used in a Human Resources Applicationp. 140
Chapter 5 Access Formsp. 143
Creating Forms with the Form Wizardp. 144
Creating Forms Manuallyp. 149
Previewing Formsp. 151
Saving Formsp. 151
Using Controls in Formsp. 152
Adding Bound Controls to Formsp. 152
Selecting and Working with Controlsp. 154
Using the Toolbox to Add Controlsp. 155
Adding Calculated Controls to Formsp. 158
Changing Control Propertiesp. 158
Entering Expressions in Controlsp. 163
Changing Colors and Effects in Controlsp. 164
Adding Check Box, Option Button, and Toggle Button Controlsp. 165
Adding List Box and Combo Box Controlsp. 167
Adding Command Button Controlsp. 168
Adding Picture and Graphic Controlsp. 170
Changing the Tab Order for Controlsp. 171
Printing Formsp. 172
Working with Form Propertiesp. 173
The Format Propertiesp. 174
The Data Propertiesp. 176
The Other Propertiesp. 178
Creating Relational Formsp. 179
Creating Relational Forms with Pop-up Formsp. 182
Creating Tabbed Formsp. 186
Adding Tab Controls to Formsp. 187
Viewing the Tab Control Propertiesp. 188
Creating Multipage Formsp. 193
Using Filter By Form to Analyze Datap. 194
Using ActiveX Controlsp. 196
Adding the Calendar Control to Formsp. 197
Using ActiveX Control Eventsp. 200
Registration and Licensing Issues of ActiveX Controlsp. 200
Forms and the Multiuser Environmentp. 202
Lightweight Versus Heavyweight Formsp. 204
A Real-World Example: Forms Used in a Human Resources Applicationp. 205
Chapter 6 Access Reportsp. 209
Design Issues Behind Reportsp. 210
Creating Reports with the Report Wizardp. 211
Creating Reports Manuallyp. 216
Understanding the Layout of Reportsp. 217
Previewing Reportsp. 219
Saving Reportsp. 220
Using Controls in Reportsp. 220
Adding Bound Controls to Reportsp. 221
Selecting and Working with Controlsp. 222
Using the Toolbox to Add Controls to Reportsp. 223
Adding Calculated Controls to Reportsp. 225
Working with Label Controls and Textp. 227
Changing Control Properties in Reportsp. 229
Changing the Data Source of Reportsp. 231
Sorting and Grouping Data in Reportsp. 232
Adding Page Numbers and Page Breaks to Reportsp. 234
Adding the Current Date or Time to Reportsp. 235
Adding Pictures and Graphics to Reportsp. 235
Creating Relational Reportsp. 237
Adding Subreports to Main Reportsp. 238
Using Crosstab Queries as Subreportsp. 240
Adding Charts to Reportsp. 241
Understanding Chart Terminologyp. 241
Adding Charts to Reports with the Chart Wizardp. 241
Editing Chartsp. 244
Creating Reports Formatted as Mailing Labelsp. 248
Creating Mailmerge Reportsp. 251
Using Special Effects in Reportsp. 254
Creating a Text Shadowp. 254
Using Reverse Videop. 254
Raising or Sinking Controlsp. 254
Using the Concatenating Textp. 255
Using the IIF Function in Reportsp. 255
Using Summary Calculationsp. 256
Dealing with Unusual Tasks in Reportsp. 256
Creating Multiple-Column ("Snaking") Reportsp. 256
Handling Odd and Even Pagesp. 258
Printing Multiple Sets of Mailing Labelsp. 259
Specifying a Custom Starting Page Numberp. 260
Solving Problems with Currency Formats and Alignmentsp. 261
Controlling the Printerp. 261
A Real-World Example: Reports Used in a Human Resources Applicationp. 262
Chapter 7 The Jump from Macros to VBAp. 267
Creating and Running Macrosp. 268
Specifying Macro Actions Manuallyp. 271
Specifying Macro Actions with Drag-and-Dropp. 276
Saving Macrosp. 277
Running Macrosp. 277
Copying Macrosp. 278
Grouping Macrosp. 278
Creating Macros That Run Automaticallyp. 280
Assigning Hot Keys to an AutoKeys Macrop. 280
Using Conditional Expressions in Macrosp. 281
Branching Within Macrosp. 283
Debugging Macrosp. 283
Polishing Applications with Macrosp. 284
Changing the Hourglassp. 285
Turning off the Echop. 285
Turning off Warningsp. 285
Macros Versus VBA Codep. 286
Benefits of Macrosp. 286
Benefits of VBA Codep. 286
Make the Switch to VBA Codep. 287
Convert Existing Macros to VBAp. 290
Part III Access Programming
Chapter 8 Introduction to Object-Based Developmentp. 297
The Access Application Definedp. 297
The Object-Oriented Programming Environmentp. 300
Objects Definedp. 301
Object-Oriented Programming Termsp. 301
The Object-Based Access Environmentp. 303
Objects and Formsp. 303
Other Basic Access Objectsp. 304
Objects and Propertiesp. 305
The Object Modelp. 305
The Object Browserp. 307
Chapter 9 The Basics of VBAp. 311
Why Use VBA?p. 312
Similarities Between VBA and Popular Structured Languagesp. 314
Creating VBA Code with the Control Wizardsp. 315
Application Areas Where VBA Is Usefulp. 317
Modules and Proceduresp. 318
Creating Class Modules for Forms and Reportsp. 320
Creating Standard Modulesp. 321
Looking at the Module Structurep. 321
Entering and Editing Code in the Module Windowp. 323
Compiling Your Code in Modulesp. 325
Using the Line Continuation Characterp. 325
Including Comments in VBA Codep. 326
Printing Procedures Within Modulesp. 326
Using Function Procedures and Sub Proceduresp. 326
Calling Procedures in Modulesp. 328
Passing Parameters to Proceduresp. 329
Variables, Data Types, and Arraysp. 329
Giving Variables an Application-Wide Scopep. 330
Defining Variable Data Typesp. 331
Declaring Arraysp. 332
Operators, Constants, Methods, and Eventsp. 333
Operatorsp. 333
Constantsp. 335
Methodsp. 335
Eventsp. 336
Using Control Structures in VBAp. 336
Using If ... Then ... Elsep. 336
Using Immediate Ifp. 337
Using Select Casep. 337
Using Do ... Loop and For ... Nextp. 338
Using While ... Wendp. 340
Using With ... End Withp. 341
Using For ... Each to Work with Collectionsp. 342
Using DoCmd to Call Macro Actionsp. 343
Using Online Help in VBAp. 344
Customizing the VBA Editorp. 345
The Editor Tabp. 345
The Editor Format Tabp. 347
The General Tabp. 348
The Docking Tabp. 349
Writing Easy-to-Maintain VBA Codep. 350
Samples of VBA Proceduresp. 350
The Add Record Buttonp. 351
The View Calls Buttonp. 352
The Edit Record and Save Record Buttonsp. 353
The Form's After Update Eventp. 354
The Form's Before Update Eventp. 354
The Form's On Open Eventp. 355
The Option Group's After Update Eventp. 355
The Combo Box's After Update Eventp. 356
The Print Client List Option of the File Menup. 358
Chapter 10 Event-Driven Programmingp. 361
The Event-Driven Modelp. 361
Linking Events to VBA Codep. 363
Linking Events to Macrosp. 365
An Example of How to Use Eventsp. 365
Types of Eventsp. 367
Data Eventsp. 367
Error and Timing Eventsp. 369
Filter Eventsp. 369
Focus Eventsp. 370
Keyboard Eventsp. 371
Mouse Eventsp. 373
Print Eventsp. 373
Window Eventsp. 374
Understanding the Order of Eventsp. 375
Opening, Switching Between, and Closing Formsp. 375
Updating Data in Controlsp. 376
Using Events for Various Tasksp. 377
Responding to Keystrokesp. 377
Handling Record Deletionsp. 379
Working with Events for Entire Recordsp. 379
Working with Events for Reports and Report Sectionsp. 380
Chapter 11 Building the user Interfacep. 385
Making the User Interface User Friendlyp. 386
Implementing the User Interface Designp. 387
Menus and Toolbarsp. 389
Defining Menu Termsp. 391
Building Menus and Toolbars with the Command Bar Objectp. 392
Modifying the Built-In Access Menus and Toolbarsp. 402
Attaching Menus and Toolbars to Forms and Reportsp. 403
Replacing Default Menus with Custom Menusp. 404
An Example of Menu and User Interface Design for an Applicationp. 405
Converting Menu Macros to Command Barsp. 410
Using Switchboards to Create an Application Prototypep. 412
Modifying Switchboards Created with the Database Wizardsp. 414
Implementing a Splash Screenp. 417
Hiding the Database Windowp. 420
Displaying Messages with the MsgBox Functionp. 420
Using the InputBox Function to Get a User's Responsep. 423
User Interface Design Tipsp. 425
Segment the Screen to Organize Datap. 425
Make Data Entry as Logical as Possiblep. 426
Use Modal Forms for Critical Data Entryp. 426
Use Text as an Aidp. 428
Include Many Useful Hot Keys for Menusp. 428
Set Docking Status to Avoid Custom Menu or Toolbar Lossp. 428
Use Easy-to-Understand Graphics on Command Buttonsp. 428
Display and Hide Buttons and Fields as Neededp. 429
Make Effective Use of Access Controlsp. 429
Leave White Space Around Formsp. 429
Add Help to Applicationsp. 429
Make Use of Status Bar and Control Tip Helpp. 430
Minimize Use of Unnecessary Graphicsp. 430
Use Color Judiciouslyp. 430
Implement Clear and Concise Error Messagesp. 430
Chapter 12 Using Data Access Objectsp. 433
What Is DAO?p. 433
Why Use DAO?p. 435
Access and the DAO Modelp. 435
Properties and Methodsp. 436
The DBEngine Objectp. 437
The Error Objectp. 438
The Workspace Objectp. 438
The User and Group Objectsp. 439
The Database Objectp. 440
The TableDef Objectp. 441
The QueryDef Objectp. 442
The Recordset Objectp. 443
The Field Objectp. 444
The Relation Objectp. 445
The Container and Document Objectsp. 446
The Index Objectp. 447
The Parameter Objectp. 448
Setting a Reference to the DAO Librariesp. 448
Working with DAO-Specific Codep. 449
Working with TableDef and QueryDef Objectsp. 451
Creating Tables with the TableDef Objectp. 451
Linking External Tables with the TableDef Objectp. 452
Creating and Working with Queries with the QueryDef Objectp. 453
Working with Recordset Objectsp. 454
Using Table-Type Recordset Objectsp. 456
Using Dynaset-Type Recordset Objectsp. 457
Using Snapshot-Type Recordset Objectsp. 458
Navigating Within Recordsetsp. 458
Finding Records with the Recordset Objectp. 459
Editing Records with the Recordset Objectp. 461
Adding and Deleting Records with the Recordset Objectp. 463
Implementing Transaction Processing with DAO Codep. 463
About Nested Transactionsp. 466
About Transactions and ISAM-Compatible Filesp. 466
About Transactions and Temp Filesp. 466
Using Unbound Forms with DAO Codep. 467
Real-World Examples of the Use of DAO Codep. 474
Chapter 13 Building Bulletproof Applicationsp. 477
Bulletproofing Definedp. 478
Design Tips Behind Bulletproof Applicationsp. 478
Bulletproofing Techniquesp. 479
Implement a Startup Formp. 480
Connect the Dotsp. 481
Provide Navigation Throughout the Applicationp. 483
Set the Remaining Startup Optionsp. 486
Create a Shortcut to Start the Applicationp. 488
Debugging and Error-Handling Techniquesp. 488
Bugs??! Not in My Codep. 488
Debugging in an Object-Based Environmentp. 489
VBA Errors Definedp. 489
VBA Debuggingp. 493
VBA Error-Handlingp. 501
Design Methodologies to Avoid Errorsp. 509
Get the Big Picturep. 509
Chapter 14 The Multiuser Environmentp. 511
Access and the Multiuser Environmentp. 512
Deciding Where to Place the Programp. 513
Deciding Where to Place the Applicationp. 514
Exclusive Versus Shared Modesp. 517
Understanding How Access Locks Recordsp. 519
Setting the Multiuser Optionsp. 520
Setting the Locking Defaultsp. 521
Setting the Retry Interval Options to Update Locked Recordsp. 522
Setting Record Locking for Individual Formsp. 523
Using the Form_Error Eventp. 524
Implementing and Maintaining Securityp. 526
Protecting a Database with Password Protectionp. 527
Activating Full Securityp. 529
Adding Groups to a Secure Databasep. 531
Adding Users to a Secure Databasep. 531
Adding Users to Groupsp. 532
Establishing Permissionsp. 532
Changing Passwordsp. 535
Creating a Workgroup Information Filep. 535
Encrypting a Database for Additional Protectionp. 536
Delivering a Database as an MDE Filep. 537
Using Database Replicationp. 538
Typical uses of Replicationp. 539
Creating a Replica of a Databasep. 541
Making Additional Replicasp. 542
Synchronizing a Replica with Another Replica Set Memberp. 543
Resolving Conflicts Between Replica Set Membersp. 544
Recovering a Lost Design Masterp. 545
Replication Behind the Scenesp. 546
Chapter 15 Access Within the Client/Server Environmentp. 549
File Server Versus Client/Serverp. 549
ODBC Versus ADOp. 551
Configuring an ODBC Data Sourcep. 552
Separating Tables from the Access Databasep. 555
Linking to External Tables Stored on the Database Serverp. 557
Linking Tables via DAO Codep. 559
Using Pass-Through Queriesp. 560
Using the Upsizing Wizard to Move an Applicationp. 561
Using Access Projects to Implement Client/Serverp. 562
Work with SQL Server 6.5p. 562
Install the Integrated Storep. 562
Create an Access Project and Connect to SQL Serverp. 563
An Introduction to ADO Codingp. 563
Optimizing the Performance of Client/Server Applicationsp. 566
Part IV Advanced Programming
Chapter 16 Optimizing the Performance of an Applicationp. 571
Using the Performance Analyzerp. 571
Optimizing the Performance of Hardwarep. 574
Optimizing the Performance of Windowsp. 576
Tweaking Windows Virtual Memoryp. 577
Changing the Registry Settings in Windowsp. 578
MaxBufferSizep. 581
FlushTransactionTimeoutp. 581
SharedAsyncDelayp. 581
ExclusiveAsyncDelayp. 581
Optimizing the Performance of the Access Databasep. 582
Databasesp. 582
Tablesp. 583
Queriesp. 585
Formsp. 586
Reportsp. 590
VBA Codep. 591
ODBC Connectionsp. 592
Improving the Perceived Speed of an Applicationp. 593
Chapter 17 Providing Users with Online Helpp. 595
The Future of Windows Helpp. 595
Understanding Windows Helpp. 597
The Microsoft Help Workshopp. 600
Creating Windows Help with the Help Workshopp. 601
Planning the Custom Help Filep. 604
Creating the Topic Filep. 604
Creating the Project Filep. 607
Creating the Contents File and Adding it to the Project Filep. 608
Creating the Map Filep. 612
Compiling the Help Filep. 614
Testing the Help Filep. 615
Integrating a Custom Help File into an Access Applicationp. 617
Adding Bells and Whistles to Windows Helpp. 620
Adding Jumps to Other Help Topicsp. 620
Adding Graphics to a Help Topicp. 621
Adding Hypergraphics to Other Topicsp. 623
Adding Video or Animation to a Help Topicp. 624
Preventing Text from Wrapping in a Help Topicp. 625
Creating Nonscrolling Regions in a Help Topicp. 626
Setting Various Options of Windows Helpp. 626
Third-Party Help Toolsp. 629
Implementing HTML-Based Helpp. 629
Other Alternatives to Traditional Windows Helpp. 633
Chapter 18 Distributing Applications with the Office Developer Editionp. 637
What Is the Office Developer Edition?p. 637
What's in the Office Developer Edition?p. 638
Limitations of the Runtime Version of Accessp. 639
How to Use the Setup Wizardp. 642
Chapter 19 Enhancing Access Applications with Libraries and Add-Insp. 645
Using Libraries to Maximize Efficiencyp. 645
References from Applications to Library Databasesp. 647
An Example of the Use of a Libraryp. 649
Library, Library, Where's the Library?p. 651
Creating Add-Ins to Enhance Functionalityp. 652
Decide on the Purpose of the Add-Inp. 653
Plan the User Interface and Design the Program Flowp. 654
Create the Database and the Objects, and Write the Codep. 654
Test the Add-Inp. 655
Implementing Add-Insp. 655
Add the UsysRegInfo Tablep. 655
Set the Database Propertiesp. 659
Use the Add-In Managerp. 659
Chapter 20 Access and Office 2000 Integrationp. 665
Exporting Access Data to Word and Excelp. 666
Exporting Access Data to Other Programsp. 668
Evolution of Automation and Basic Terminologyp. 670
Working with ActiveX Objectsp. 672
Linking an ActiveX Object to an OLE Object Fieldp. 673
Embedding an Existing ActiveX Object in an Object Fieldp. 674
Embedding a New ActiveX Object in an OLE Object Fieldp. 675
Embedding OLE Object Files in Unbound Framesp. 676
Embedding Audio and Video in an OLE Object Fieldp. 677
Using VBA Code with Automationp. 678
Specifying References to Object Librariesp. 678
Declaring an Instance of an Automation Objectp. 682
Using the CreateObject Function to Declare an Instancep. 684
Using the GetObject Function to Get an Existing Instancep. 684
Working with Automation Objectsp. 685
Closing an Instance of an Automation Objectp. 687
Declaring an Instance of Access as an Automation Objectp. 687
An Automation Example with Word 2000p. 687
Code to Ensure an Existing Instance of a Word Objectp. 690
Code to Make the Object Visible and to Add a Documentp. 691
Code to Build the Text of the Form Letterp. 692
An Automation Example with Excel 2000p. 692
An Automation Example with Outlookp. 695
Using the Macro Recorder to Create Automation Codep. 698
Using VBA Code to Control the Office Assistantp. 701
Displaying Messages with the Office Assistantp. 702
Controlling Sounds and Animationp. 703
The Microsoft Office Object Modelp. 705
Microsoft Word Object Modelp. 705
Microsoft Excel Object Modelp. 706
Microsoft PowerPoint Object Modelp. 707
Microsoft Outlook Object Modelp. 707
Chapter 21 Using the Windows API in Accessp. 709
What Is the Windows API?p. 709
Declaring Windows API Proceduresp. 710
Call Third-Party DLLsp. 712
To Alias or Not to Aliasp. 712
Where to Find API Declarationsp. 713
An Example of the Use of the Windows APIp. 714
Part V Web Development
Chapter 22 Internet-Centric Accessp. 719
The Internet and the World Wide Webp. 719
Access and the Internetp. 721
About Intranetsp. 722
About HTMLp. 722
Importing and Linking HTML Datap. 725
Saving Access Data as Static HTML Filesp. 728
Saving Access Data as Dynamic HTML Filesp. 734
Using Macros and VBA to Export Data to HTMLp. 737
Working with Data Access Pagesp. 739
Creating Data Access Pages with the Data Access Page Wizardp. 740
Creating Data Access Pages Manuallyp. 743
Editing or Deleting Data Access Pagesp. 751
Setting Up the Personal Web Serverp. 752
Requirements of the Personal Web Serverp. 753
Installation of the Personal Web Serverp. 754
Chapter 23 Web-Enabling Access Applicationsp. 755
About Hyperlinksp. 755
Using Hyperlinks in Tablesp. 756
How Hyperlink Data is Added to Tablesp. 756
How Hyperlink Data is Stored in Tablesp. 758
Using Hyperlinks in Forms and Reportsp. 759
Creating a Label, Image Control, or Command Button That Follows a Hyperlinkp. 762
Using Hyperlinks to Jump to Access Objectsp. 762
Using VBA Code with Hyperlinksp. 764
Using the WebBrowser Control in Formsp. 765
WebBrowser Control Methodsp. 769
A Working Example of the WebBrowser Controlp. 769
Using the Web Toolbar in Accessp. 772
Appendix A What's on the CD-ROMp. 775
Appendix B What's New in Access 2000p. 777
Appendix C Microsoft Jet and DAO Error Codesp. 781
Appendix D Developer Resourcesp. 809
Appendix E Access Conversion Techniquesp. 815
Indexp. 823
End-User License Agreementp. 850
CD-ROM Installation Instructionsp. 854