Alison Balter’s Mastering Microsoft® Office Access 2007 Development......Page 1
Table of Contents......Page 6
Introduction......Page 42
Part I: The Basics of Access Development......Page 46
What Types of Applications Can You Develop in Access?......Page 48
What Exactly Is a Database?......Page 52
Getting to Know the Database Objects......Page 53
Hardware Requirements......Page 64
How Do I Get Started Developing an Access Application?......Page 65
What\'s New in Access 2007?......Page 69
Other New Features Found in Access 2007......Page 93
Additional Tips and Tricks......Page 97
Practical Examples: The Application Design for a Computer Consulting Firm......Page 113
Summary......Page 115
Creating a New Database......Page 116
Building a New Table......Page 120
Selecting the Appropriate Field Type for Your Data......Page 123
Working with Field Properties......Page 128
Working with the Lookup Feature......Page 139
Working with Table Properties......Page 142
Using Access Tables with the Internet......Page 144
Viewing Object Dependencies......Page 150
Examining Table Specifications and Limitations......Page 151
Practical Examples: Designing the Tables Needed for a Computer Consulting Firm\'s Time and Billing Application......Page 152
Summary......Page 155
Introduction to Relational Database Design......Page 156
Establishing Relationships in Access......Page 165
Establishing Referential Integrity......Page 169
Looking at the Benefits of Relationships......Page 174
Practical Examples: Establishing the Relationships Between the Tables Included in the Time and Billing Database......Page 175
Summary......Page 177
What Is a Query, and When Should You Use One?......Page 178
Everything You Need to Know About Query Basics......Page 179
Ordering Your Query Result......Page 185
Refining Your Query with Criteria......Page 187
Working with Dates in Criteria......Page 190
Understanding How You Can Update Query Results......Page 191
Building Queries Based on Multiple Tables......Page 192
Creating Calculated Fields......Page 198
Getting Help from the Expression Builder......Page 201
Summarizing Data with Totals Queries......Page 202
Understanding Nulls and Query Results......Page 207
Refining Your Queries with Field, Field List, and Query Properties......Page 211
Building Parameter Queries When You Don\'t Know the Criteria at Design Time......Page 213
Adding Smart Tags to Your Queries......Page 216
Creating a Pivot Table or Pivot Chart from a Query......Page 220
Understanding Query Specifications and Limitations......Page 226
Practical Examples: Building Queries Needed by the Time and Billing Application for a Computer Consulting Firm......Page 227
Summary......Page 229
Understanding the Uses of Forms......Page 230
Examining the Anatomy of a Form......Page 233
Creating a New Form......Page 234
Working with the Form Design Window......Page 237
Working in Layout View......Page 246
Selecting the Correct Control for the Job......Page 259
Control Morphing......Page 268
Conditional Formatting......Page 269
Determining Which Form Properties Are Available and Why You Should Use Them......Page 270
Determining Which Control Properties Are Available and Why You Should Use Them......Page 277
Understanding Bound, Unbound, and Calculated Controls......Page 283
Using Expressions to Enhance Your Forms......Page 284
Using the Command Button Wizards: Programming Without Typing......Page 285
Building Forms Based on More Than One Table......Page 287
Basing Forms on Queries: The Why and How......Page 290
Connecting Access Forms and the Internet......Page 292
Adding Smart Tags to Your Forms......Page 294
Creating a Pivot Table or Pivot Chart from a Form......Page 297
Examining Form Specifications and Limitations......Page 303
Practical Examples: Designing Forms for Your Application......Page 304
Summary......Page 309
Examining Types of Reports Available......Page 310
Understanding the Anatomy of a Report......Page 315
Creating a New Report......Page 316
Working with the Report Design Window......Page 319
Selecting the Correct Control for the Job......Page 323
What Report Properties Are Available, and Why Should You Use Them?......Page 325
What Control Properties Are Available, and Why Should You Use Them?......Page 330
Building Reports Based on More Than One Table......Page 334
Working with Sorting and Grouping......Page 342
Improving Performance and Reusability by Basing Reports on Stored Queries or Embedded SQL Statements......Page 345
Using Access Reports and the Internet......Page 346
Practical Examples: Building Reports Needed for Your Application......Page 348
Summary......Page 355
Learning the Basics of Creating and Running a Macro......Page 356
Running an Access Macro......Page 365
Modifying an Existing Macro......Page 370
Documenting Your Macro: Adding Comments......Page 377
Testing a Macro......Page 378
Converting a Macro to VBA Code......Page 380
Creating an AutoExec Macro......Page 382
Using the DoCmd Object......Page 383
Summary......Page 384
VBA Explained......Page 386
What Are Access Class Modules, Standard Modules, Form Modules, and Report Modules?......Page 388
Working with Variables......Page 399
Adding Comments to Your Code......Page 404
Using the VBA Control Structures......Page 405
Passing Parameters and Returning Values......Page 414
The DoCmd Object: Performing Macro Actions......Page 415
Working with Built-In Functions......Page 417
Working with Constants......Page 421
Working with the Visual Basic Editor Tools......Page 424
Customizing the VBE......Page 435
Practical Examples: Using Event Routines, User-Defined Functions, and Subroutines......Page 437
Summary......Page 440
Understanding Objects, Properties, Events, and Methods......Page 442
Using the Object Browser to Learn About Access\'s Objects......Page 446
Referring to Objects......Page 448
Working with Properties and Methods......Page 449
Declaring and Assigning Object Variables......Page 451
Understanding the Differences Between Objects and Collections......Page 453
Passing Objects to Subroutines and Functions......Page 454
Determining the Type of a Control......Page 455
Using Special Properties That Refer to Objects......Page 456
Understanding Access\'s Object Model......Page 457
Taking Advantage of Additional Useful Properties......Page 465
Practical Examples: Working with Objects......Page 466
Summary......Page 470
What Are the Form Events, and When Do You Use Them?......Page 472
What Are the Section and Control Events, and When Do You Use Them?......Page 484
What Types of Forms Can I Create, and When Are They Appropriate?......Page 491
Using Built-In Dialog Boxes......Page 501
Taking Advantage of Built-In, Form-Filtering Features......Page 505
Including Objects from Other Applications: Linking Versus Embedding......Page 507
Switching a Form\'s RecordSource......Page 509
Learning Power Combo Box and List Box Techniques......Page 512
Learning Power Subform Techniques......Page 517
Using Automatic Error Checking......Page 518
Viewing Object Dependencies......Page 520
Using AutoCorrect Options......Page 521
Propagating Field Properties......Page 522
Synchronizing a Form with Its Underlying Recordset......Page 524
Creating Custom Properties and Methods......Page 525
Practical Examples: Applying Advanced Techniques to Your Application......Page 531
Summary......Page 532
Events Available for Reports and When to Use Them......Page 534
Events Available for Report Sections and When to Use Them......Page 537
Taking Advantage of Special Report Properties......Page 542
Controlling the Printer......Page 544
Using Automatic Error Checking......Page 546
Propagating Field Properties......Page 548
Incorporating Practical Applications of Report Events and Properties......Page 550
Practical Examples: Practicing What You Learned......Page 577
Summary......Page 578
Using Action Queries......Page 580
Viewing Special Query Properties......Page 589
Optimizing Queries......Page 593
Using Crosstab Queries......Page 597
Establishing Outer Joins......Page 603
Establishing Self-Joins......Page 605
Understanding SQL......Page 607
Building Union Queries......Page 616
Using Pass-Through Queries......Page 619
Examining the Propagation of Nulls and Query Results......Page 620
Running Subqueries......Page 622
Using SQL to Update Data......Page 623
Using SQL for Data Definition......Page 625
Using the Result of a Function as the Criteria for a Query......Page 627
Passing Parameter Query Values from a Form......Page 629
Understanding Jet 4.0 ANSI-92 Extensions......Page 631
Practical Examples: Applying These Techniques in Your Application......Page 636
Summary......Page 639
What Are User-Defined Types, and Why Would You Use Them?......Page 640
Working with Constants......Page 643
Working with Arrays......Page 646
Understanding Advanced Function Techniques......Page 651
Working with Empty and Null......Page 659
Creating and Working with Custom Collections......Page 666
Handling Files with Low-Level File Handling......Page 669
Understanding and Effectively Using Compilation Options......Page 670
Importing and Exporting Code Modules......Page 671
Working with Project Properties......Page 672
Practical Examples: Putting Advanced Techniques to Use......Page 673
Summary......Page 676
Object Orientation—An Introduction......Page 678
Creating and Using a Class Module......Page 679
Creating Multiple Class Instances......Page 683
Adding Code to the Initialize and Terminate Events......Page 684
Working with Enumerated Types......Page 685
Building Hierarchies of Classes......Page 687
Adding a Parent Property to Classes......Page 688
Working with Custom Collections......Page 689
Adding Your Own Events......Page 692
Practical Examples: Using Class Modules......Page 693
Summary......Page 710
Examining the ADO Model......Page 712
Understanding ADO Recordset Types......Page 721
Working with ADO Recordset Properties and Methods......Page 729
Modifying Table Data Using ADO Code......Page 745
Creating and Modifying Database Objects Using ADO Code......Page 756
Practical Examples: Applying These Techniques to Your Application......Page 760
Summary......Page 765
Part II: What to Do When Things Don\'t Go as Planned......Page 766
Avoiding Bugs......Page 768
Harnessing the Power of the Immediate Window......Page 770
Invoking the Debugger......Page 776
Using Breakpoints to Troubleshoot......Page 777
Stepping Through Code......Page 779
Setting the Next Statement to Execute......Page 783
Using the Call Stack Window......Page 784
Working with the Locals Window......Page 785
Working with Watch Expressions......Page 786
Continuing Execution After a Runtime Error......Page 791
Looking at Gotchas with the Immediate Window......Page 792
Debugging Tips......Page 793
Summary......Page 794
Implementing Error Handling......Page 796
Using On Error Statements......Page 799
Using Resume Statements......Page 801
Clearing an Error......Page 805
Examining the Cascading Error Effect......Page 806
Using the Err Object......Page 807
Raising an Error......Page 808
Using the Errors Collection......Page 810
Creating a Generic Error Handler......Page 811
Creating a Call Stack......Page 827
Building a Custom Error Handler Class......Page 831
Working with Error Events......Page 837
Creating a List of Error Codes and Descriptions......Page 838
Practical Examples: Incorporating Error Handling......Page 839
Summary......Page 840
Introducing Optimization......Page 842
Modifying Hardware and Software Configurations......Page 843
What Is the Access Database Engine?......Page 847
Letting the Performance Analyzer Determine Problem Areas......Page 848
Designing Tables to Optimize Performance......Page 849
Designing Database Objects to Improve Performance......Page 851
Practical Examples: Improving the Performance of Your Applications......Page 872
Summary......Page 873
Part III: Developing Multiuser and Enterprise Applications......Page 874
Splitting Databases into Tables and Other Objects......Page 876
Basing Forms and Reports on Queries or Embedded SQL Statements......Page 878
Preparing an Application for Distribution......Page 879
Using Access as a Front End......Page 883
Summary......Page 886
Why This Chapter Is Important......Page 888
Importing, Linking, and Opening Files: When and Why......Page 889
Importing External Data......Page 891
Creating a Link to External Data......Page 895
Opening an External Table......Page 905
Using the Jet OLEDB:Link Provider String......Page 907
Working with Passwords......Page 908
Refreshing and Removing Links......Page 910
Looking at Special Considerations......Page 913
Troubleshooting......Page 914
Working with HTML Documents......Page 915
Practical Examples: Working with External Data from Within Your Application......Page 918
Summary......Page 922
Why This Chapter Is Important?......Page 924
Exporting Data to a SharePoint Site......Page 926
Publishing Data to a SharePoint Site......Page 932
Opening Access Forms and Reports from a SharePoint Site......Page 937
Linking to and Importing from SharePoint Lists......Page 941
Taking SharePoint Lists Offline with Access......Page 944
Reestablishing Links When a SharePoint Site Has Been Moved......Page 947
Summary......Page 948
Designing Your Application with Multiuser Issues in Mind......Page 950
Understanding Access\'s Locking Mechanisms......Page 954
Deciding Whether to Use the Client/Server Model......Page 955
Understanding the Roles That Access Plays in the Application Design Model......Page 960
Learning the Client/Server Buzzwords......Page 963
Upsizing: What to Worry About......Page 964
Proactively Preparing for Upsizing......Page 966
Using Transaction Processing......Page 967
Summary......Page 972
Part IV: Black Belt Programming......Page 974
Customizing the Ribbon: An Overview......Page 976
Showing System Tables......Page 977
Enabling the Display of System Errors......Page 978
Creating the USysRibbons Table......Page 979
Adding Data to the USysRibbons Table......Page 980
Applying the Custom Ribbon......Page 981
Restoring the Ribbon to Its Default Settings......Page 984
Adding Additional Groups and Controls......Page 985
Executing a Macro from the Ribbon......Page 987
Summary......Page 988
Why This Chapter Is Important......Page 990
Declaring an Object Variable to Reference Your Application......Page 991
Manipulating an Automation Object......Page 994
Using Early Binding Versus Late Binding......Page 995
Controlling Excel from Access......Page 997
Closing an Excel Automation Object......Page 1001
Creating a Graph from Access......Page 1002
Controlling Word from Access......Page 1007
Controlling PowerPoint from Access......Page 1011
Automating Outlook from Access......Page 1015
Controlling Access from Other Applications......Page 1017
Practical Examples: Using Automation to Extend the Functionality of Your Applications......Page 1020
Summary......Page 1025
Declaring an External Function to the Compiler......Page 1026
Working with Constants and Types......Page 1032
Using API Functions......Page 1034
Practical Examples: Using Windows API Functions in Your Applications......Page 1049
Summary......Page 1050
Why This Chapter Is Important......Page 1052
Preparing a Database to Be a Library......Page 1053
Creating a Reference......Page 1054
Debugging a Library Database......Page 1063
Practical Examples: Building a Library for Your Application......Page 1064
Summary......Page 1067
Using Builders......Page 1068
Using Wizards......Page 1080
Using Menu Add-Ins......Page 1086
Practical Examples: Designing Your Own Add-Ins......Page 1088
Summary......Page 1094
Saving Database Objects as HTML......Page 1096
Linking to HTML Files......Page 1103
Importing HTML Files......Page 1106
Saving Database Objects as XML......Page 1107
Importing XML Files......Page 1109
Summary......Page 1110
Part V: Adding Polish to Your Application......Page 1112
Why This Chapter Is Important......Page 1114
Preparing Your Application to Be Self-Documenting......Page 1115
Using the Database Documenter......Page 1122
Using the Object Dependency Feature......Page 1127
Writing Code to Create Your Own Documentation......Page 1129
Summary......Page 1130
Compacting Your Database......Page 1132
Backing Up Your Database......Page 1140
Converting an Access Database......Page 1141
Summary......Page 1143
What\'s New in Access 2007 Security?......Page 1144
What Happened to User-Level Security?......Page 1146
Trusting a Database......Page 1147
Using a Database Password to Encrypt an Office Access 2007 Database......Page 1150
Packaging, Signing, and Distributing an Access Database......Page 1151
Using the Trust Center......Page 1156
Working in Sandbox Mode......Page 1164
Enabling or Disabling ActiveX Controls......Page 1166
Enabling or Disabling Add-Ins......Page 1167
Adding a Trusted Publisher......Page 1168
Summary......Page 1169
Part VI: Appendixes......Page 1170
A: Naming Conventions......Page 1172
B: Table Structures......Page 1178
The tblClients Table......Page 1179
The tblClientAddresses Table......Page 1184
The tblAddressTypes Table......Page 1189
The tblClientPhones Table......Page 1190
The tblPhoneTypes Table......Page 1192
The tblCorrespondence Table......Page 1193
The tblCorrespondenceTypes Table......Page 1197
The tblTerms Table......Page 1198
The tblContactType Table......Page 1199
The tblCompanyInfo Table......Page 1200
The tblEmployees Table......Page 1205
The tblErrorLog Table......Page 1209
The tblErrors Table......Page 1212
The tblExpenseCodes Table......Page 1213
The tblPaymentMethods Table......Page 1214
The tblPayments Table......Page 1215
The tblProjects Table......Page 1219
The tblTimeCardExpenses Table......Page 1223
The tblTimeCardHours Table......Page 1227
The tblTimeCards Table......Page 1231
The tblWorkCodes Table......Page 1232
A......Page 1234
B......Page 1239
C......Page 1240
D......Page 1248
E......Page 1255
F......Page 1258
G......Page 1266
H......Page 1267
I......Page 1268
J......Page 1269
L......Page 1270
M......Page 1272
N......Page 1276
O......Page 1278
P......Page 1281
Q......Page 1287
R......Page 1289
S......Page 1295
T......Page 1300
U......Page 1304
V......Page 1305
W......Page 1308
Z......Page 1309