book

Expert Access™ 2007 Programming

Name: Expert Access™ 2007 Programming
ISBN: 9780470174029

by Rob Cooper, Michael Tucker

November 2007

Intermediate to advanced

719 pages

17h 16m

English

Wrox

Read now

Unlock full access

Copyright
About the Authors
Credits
Acknowledgments
Introduction
Building Off-the-Shelf ApplicationsA Little BackgroundWhom This Book Is ForWhat This Book CoversHow This Book Is StructuredPart I: Programming Access ApplicationsPart II: Data ManipulationPart III: Interacting with the ApplicationPart IV: Finalizing the ApplicationWhat You Need to Use This BookConventionsSourceThis section includes the source code.OutputThis section lists the output:Source CodeErratap2p.wrox.com
I. Programming Access Applications
1. Overview of Programming for Access
1.1. Writing Code for Access1.1.1. The Access Object Model1.1.2. The DAO Object Model1.1.3. The ADO Object Model1.1.4. Object-Oriented Thinking1.2. Windows API Programming1.3. Working with Managed Code1.3.1. What Is Managed Code?1.3.2. Versions of Visual Studio1.3.3. Writing Managed Code Libraries to Use with Access1.3.4. Referencing the Access Object Model from Managed Code1.4. Referencing Other Applications1.4.1. Discovering References1.4.2. Adding References to Office Applications1.5. Summary
2. Extending Applications Using the Windows API
2.1. Windows API Overview2.1.1. Why Use API Functions2.1.2. API Resources2.1.3. Writing Declare Statements2.1.3.1. How to Find API Functions2.1.3.2. C to VBA Data Type Conversions2.1.3.2.1. ByRef vs. ByVal2.1.3.2.2. Strings2.1.3.2.3. ANSI Strings2.1.3.2.4. Wide Strings2.1.3.2.5. Structures2.1.3.2.6. Unions2.1.4. Example Conversion2.1.5. Error Messages from API Functions2.2. Retrieving System Information2.2.1. Return the User Name in Different Formats2.2.2. Return the Computer Name in Different Formats2.2.3. Retrieve the Command Line2.3. Windows Shell Functions2.3.1. Get the Path to a Special Folder2.3.2. Show the Property Dialog Box for a File2.3.3. Determine Whether a Computer Is Connected to the Internet2.3.4. Determine Whether a User Is an Administrator2.4. Controlling Processes and Threads2.4.1. Determining the Current Process ID2.4.2. Pausing Code Execution2.4.3. Quitting a Process2.4.4. Waiting for a Process to Complete2.4.4.1. Create the Process2.4.4.2. Waiting for the Process2.4.4.3. Closing the Process2.4.4.4. Putting It Together2.4.4.5. Testing the Routine2.5. Windows Vista Task Dialogs2.5.1. Introduction to Task Dialogs2.5.1.1. Modify the Access Manifest File2.5.1.2. StrPtr2.5.2. A Simple Message Box Replacement2.5.3. More Complex Task Dialogs2.5.3.1. Adding an Expanded Section2.5.3.2. Adding a Footer Section2.5.3.3. Setting Custom Button Text2.5.3.4. Using Command Links2.5.3.5. Adding Verification Text2.5.3.6. Adding Radio Buttons2.5.3.7. Adding Hyperlinks2.5.4. Icons2.6. Miscellaneous API Functions2.6.1. Creating a GUID2.6.2. ShellExecute Tricks2.6.2.1. Sending E-mail Using the Default E-mail Client2.6.2.2. Opening the Default Web Browser2.7. Summary
3. Programming Class Modules
3.1. Overview of Class Modules3.1.1. Why Use Class Modules?3.1.1.1. Making Complex Tasks Easier3.1.1.2. Code Reuse3.1.1.3. Controlling Visibility3.1.2. Class Module Members3.1.2.1. Enumerations3.1.2.1.1. Flags3.1.2.1.2. Flag-Related Functions3.1.2.2. Properties3.1.2.2.1. Read-Only Properties3.1.2.2.2. Write-Only Properties3.1.2.2.3. Metadata Properties3.1.2.3. Methods3.1.2.4. Events3.1.2.4.1. Initialize Event3.1.2.4.2. Terminate Event3.1.3. Code Reuse in VBA3.1.3.1. Instancing Property3.1.3.2. Wrapper Method3.1.3.3. Instancing Property (Revisited)3.2. Subclassing3.2.1. Subclassing Access Forms and Reports3.2.2. Sinking Form Events3.2.2.1. Subclassing the Form_Error event3.2.2.2. Subclassing the Form_Load Event3.3. Interfaces3.3.1. Inheritance in VBA3.3.1.1. Writing an Interface3.3.1.2. Implementing an Interface3.3.1.3. Using an Interface3.3.2. Specialization3.3.3. Testing the Type of an Object3.3.3.1. TypeOf Operator3.3.3.2. TypeName Function3.3.3.3. Determining Whether an Object Has Been Set3.4. Collections3.4.1. Writing the Standard Collection Class3.4.1.1. Storage3.4.1.2. Add Method3.4.1.3. Item Method3.4.1.4. Count Property3.4.1.5. Remove Method3.4.1.6. Initialization and Cleanup3.4.1.7. Using the Collection Class3.4.2. An Alternate Implementation for the Add Method3.4.3. Setting a Default Member3.4.4. For Each Enumeration3.4.5. The ICollectionEx Interface3.4.5.1. Clear Method3.4.5.2. Contains Method3.4.5.3. Find Method3.4.5.4. ForEach Method3.4.5.5. Testing the ICollectionEx Interface3.5. Events3.5.1. Why Write Custom Events?3.5.2. Creating Custom Events3.5.2.1. Event Keyword3.5.2.2. RaiseEvent Keyword3.5.2.3. Creating an Event That Can Be Cancelled3.5.3. Listening for Custom Events3.6. Design Considerations3.6.1. Copying a Class Instance3.6.2. Exposing the Interface of a Derived Type3.6.3. Raising Errors3.6.3.1. Using Err.Raise3.6.3.2. Defining an Error Event3.7. Summary
4. Debugging, Error Handling, and Coding Practices
4.1. Debugging4.1.1. Using Watches4.1.1.1. Adding a Quick Watch4.1.1.2. Adding a Watch4.1.1.3. Watch Types4.1.1.3.1. Watch Expression4.1.1.3.2. Break When Value Is True4.1.1.3.3. Break When Value Changes4.1.2. Using the Watches Window4.1.3. Using the Immediate Window4.1.4. Using the Locals Window4.1.5. Viewing the Call Stack4.1.6. Building a Call Stack Using Code4.1.6.1. Creating the CallStack Class4.1.6.2. Instrumenting Your Code4.1.6.3. Programmatically Generating a Call Stack Using Error Handling4.1.7. Assertions4.1.7.1. Using Debug.Assert4.1.7.2. Conditional Compilation4.1.7.2.1. Private Conditional Compilation Constants4.1.7.2.2. Public Conditional Compilation Constants4.1.7.3. Writing a Better Assert Method4.1.7.4. Using the DebugEx.Assert Method4.1.8. Creating a Debug Build4.1.8.1. Using the Command Line to Specify a Debug Build4.1.8.2. Using a Table to Create a Debug Build4.1.8.3. Using Conditional Compilation to Create a Debug Build4.1.8.4. Using a Database Property to Create a Debug Build4.2. Error Handling4.2.1. Handling Errors Inline4.2.2. Categorizing Errors4.2.3. System Error Codes and HRESULTS4.2.3.1. System Error Codes4.2.3.2. HRESULT Error Codes4.2.4. Creating an Error Handler Class4.2.4.1. Enumerations4.2.4.2. Private Data4.2.4.3. Properties4.2.4.4. Methods4.2.5. Logging Errors4.2.5.1. The ILog Interface4.2.5.2. Logging to a Database Table4.2.5.3. Logging to a Text File4.2.5.4. Creating an Instance of the ILog Class4.2.6. Using the Error Handling Class and Logging4.3. Coding Practices4.3.1. Readability4.3.1.1. Comments4.3.1.1.1. Module Headers4.3.1.1.2. Procedure Headers4.3.1.1.3. Other Comments4.3.1.2. Formatting4.3.1.3. Naming Conventions4.3.2. Version Control4.3.3. Refactoring4.3.3.1. Modules4.3.3.2. Procedures4.3.4. Code Reviews4.4. Summary

II. Data Manipulation
5. Parsing Data
5.1. File Input/Output5.1.1. Using VBA I/O statements5.1.1.1. The Importance of Error Handling5.1.1.2. Reading an Existing Text File5.1.1.3. Opening a File for Random Access5.1.1.4. Writing Data5.1.2. Using FileSystemObject5.1.3. Determining Whether a File Is ANSI, Unicode, or UTF-85.2. Splitting Strings5.2.1. The VBA String Handling Functions5.2.1.1. PeekToken5.2.1.2. PeelToken5.2.2. Replacing Tokens in Strings5.2.3. Formatted Token Replacement5.2.4. Parsing into Data Types5.2.5. An End-to-End Example5.2.6. Splitting Names5.2.7. Address Element Granularity5.2.8. Phone Number Granularity5.3. Creating HTML5.3.1. Exporting HTML Files5.3.2. Why Create HTML Using Code?5.3.3. Creating HTML Files5.4. Summary
6. Using SQL to Retrieve Data and Manipulate Objects
6.1. Where SQL Can Be Used in Access6.1.1. Form and Report Recordsets6.1.2. Partial SQL Properties6.2. The Various Types of SQL Queries6.2.1. The SELECT Query6.2.2. Project Your Columns: the Field List6.2.2.1. Add a Field Using Field Names6.2.2.1.1. Fully Qualified Field Names6.2.2.2. Add All Fields Using the Wildcard Character6.2.2.3. Add Expressions6.2.2.4. Field Name Aliases6.2.3. Choose Your Tables: The FROM Clause6.2.3.1. Table Name Aliases6.2.4. Filter Your Data: The WHERE Clause6.2.4.1. Comparison Operators6.2.4.2. Matching on Strings6.2.4.3. Matching on String Patterns Using Wildcard Characters6.2.4.4. Matching on Data in a Range Using the BETWEEN ... AND Operator6.2.4.5. Null vs. vbNullString6.2.4.6. Matching on Null Values and Empty String Values6.2.4.7. Matching on Dates6.2.4.8. Matching on GUID Values6.2.4.9. Matching to Items in a List (IN Operator)6.2.5. Sort Your Results: The ORDER BY Clause6.2.5.1. Setting the Sort Direction6.2.5.2. Sorting on Binary or Memo Fields6.3. Add Data from More Than One Table Using Table Joins6.3.1. Inner Joins6.3.2. Outer Joins6.3.3. Joining More Than Two Tables Using Nested Joins6.3.4. Self Joins6.3.5. Cartesian Products6.4. Prompt Users for Data with Parameters6.4.1.6.4.1.1. Form and Report Parameters6.4.1.2. Improve Performance and Readability with the Parameters Declaration6.4.1.3. Accidental Parameters6.5. Add Data from Other Databases Using the IN Clause6.6. Selection Predicates6.6.1. ALL Predicate6.6.2. TOP Predicate6.6.3. DISTINCT Predicate6.7. Aggregating Data6.7.1. Bucket Your Data: The GROUP BY Clause6.7.1.1. Aggregate Functions6.7.2. Filter Your Data Based on Bucketed Data: the HAVING Clause6.7.2.1. Comparing the WHERE Clause and the HAVING Clause6.7.2.2. Domain Aggregate Functions6.8. Action Queries6.8.1. Make Table Query6.8.2. Append Query6.8.2.1. Append Multiple Records Using a SELECT Query as the Data Source6.8.2.2. Creating a Multi-Record APPEND Query Using the Access Query Designer6.8.2.3. Append a Single Record Using a Value List as the Data Source6.8.2.4. Creating a Single-Record Append Query Using the Access Query Designer6.8.3. UPDATE Query6.8.3.1. Creating an Update Query Using the Access Query Designer6.8.4. DELETE Query6.9. Crosstab Queries6.9.1. Ordering Column Headings6.9.2. Common Expressions Used with Crosstab Queries6.9.2.1. Partition Function6.10. UNION Queries6.10.1. Sorting a UNION Query6.10.2. Using the Designer to Create Union Queries6.11. Subqueries6.11.1.6.11.1.1. IN Predicate6.12. Data Definition Queries6.12.1. Create Table6.12.1.1. Data Types Used6.12.1.2. Working with Primary Keys6.12.2. Alter Table6.12.3. Drop Table6.12.4. Create Index6.12.5. Drop Index6.12.6. Alter Database Password6.13. SQL Pass-Through Queries6.13.1. Running Ad Hoc SQL Pass-Through Queries6.14. ANSI Mode6.14.1. String Pattern Matching Using ANSI-92 Syntax6.15. Summary
7. Managing Data
7.1. Finding Data7.1.1. Find Methods7.1.2. Seek Method7.1.2.1. Seek Pattern7.1.3. Move Methods7.1.4. Search Optimization Tips7.2. Categorization and Sorting7.2.1. Categorization Using the Choose Function7.2.2. Using a Custom Sort Order Field7.3. Backup and Restore7.4. Using Checksum Functions7.4.1. MD5 Algorithm7.4.2. Mod-10 Checksum7.4.2.1. How the Mod-10 Checksum Algorithm Works7.4.2.2. Using VBA to Calculate Mod-10 Checksums7.5. Access 2007 Specifics7.5.1. Locale-Agnostic Parser for ColumnHistory Function7.5.2. Getting a List of Attachments in an Attachment Field7.5.3. Searching for Records with Attachments7.5.4. Searching for Specific Attachments7.6. Summary
III. Interacting with the Application
8. Using Code to Add Advanced Functionality to Forms
8.1. Working with Form Events8.1.1. How to Determine When Data Is Added8.1.2. How to Determine When Data Is Changed8.1.3. How to Determine When Data Is Deleted8.1.4. Validating Form Data8.1.5. Suppressing Access Error Messages8.1.6. How to Determine If a Modifier Key Is Pressed8.1.7. Periodic Backups Using the Timer Event8.1.8. Moving a Form Without a Border8.1.9. Customizing ControlTipText8.2. Working with Controls8.2.1. Validating Control Data8.2.2. Disabling all Controls8.2.3. Reusing a Subform Control8.2.4. Extended List Box Functionality8.2.4.1. Moving Items Up and Down in a List Box8.2.4.1.1. Create the Table8.2.4.1.2. Create the Form8.2.4.2. Deleting Items in a List Box with the Delete Key8.2.4.3. Using a Multi-Select List Box for Filter Criteria8.2.4.4. Selecting All Items in a Multi-Select List Box Using the Keyboard8.2.5. Custom Progress Bars8.2.5.1. Create the Form8.2.5.2. Run the Process8.2.6. Custom Up/Down Buttons8.2.7. Displaying Multiple Attachments Onscreen8.3. Common Forms for Your Applications8.3.1. Dynamic Menu Forms and Dashboards8.3.1.1. Defining the Dashboard Layouts8.3.1.1.1. USysFrmDashboard18.3.1.1.2. USysFrmDashboard28.3.1.1.3. USysFrmDashboard38.3.1.1.4. USysFrmDashboard48.3.1.2. Creating the Tables8.3.1.2.1. Create the Dashboard Table8.3.1.2.2. Create the Component Table8.3.1.2.3. Create the Dashboard Components Table8.3.1.2.4. Creating Relationships for the Tables8.3.1.3. Choosing a Dashboard8.3.1.4. Opening the Selected Dashboard8.3.1.5. Choosing Components8.3.1.6. Filling the Dashboard8.3.2. Splash Screens8.3.3. About Dialog Boxes8.3.4. Kiosk Forms8.4. Custom Form Navigation8.4.1. Navigation Bars8.4.2. "I Need To" Drop-Down8.4.3. Keyboard-Driven Navigation8.5. Summary
9. Using Code to Add Advanced Functionality to Reports
9.1. Interactive Reports Using Access 20079.1.1. Sorting a Report Using Controls on a Report9.1.2. Filtering a Report Using Controls9.1.3. Drill-Down9.1.4. Navigating to a Map Dynamically9.2. Report Scenarios9.2.1. Creating a Report with Data Bars9.2.2. Creating a Calendar Report9.2.2.1. Creating the Report9.2.2.2. Filling the Report with Data9.2.3. Displaying Images Using an Attachment Field9.2.4. Displaying Images Dynamically Using a Path9.3. Creating a Report Manager9.3.1. Creating the Reports Table9.3.2. Implementing Report Manager Features9.3.2.1. Categorization9.3.2.2. Print, Preview, and E-mail9.3.2.3. Tracking Report Usage9.3.2.4. Bulk Print9.3.2.5. Allowing Users to Add Their Own Reports9.4. Summary
10. Using Automation to Add Functionality
10.1. Automation Basics10.1.1. When to Automate10.1.2. Shell Function10.1.3. CreateObject vs. GetObject10.1.4. Early Binding vs. Late Binding10.1.5. Discovering Object Models Using Built-in Tools10.2. Automating Windows10.2.1. The Shell Object10.2.1.1. Opening Folders10.2.1.2. Running a Command10.2.1.3. Launching an Item in the Control Panel10.2.1.4. Sending Mail Using the Default Mail Program10.2.2. Windows Scripting Host10.2.2.1. Reading from and Writing to the Registry10.2.2.1.1. RegWrite Method10.2.2.1.2. RegRead Method10.2.2.1.3. RegDelete Method10.2.2.1.4. Expanding REG_EXPAND_SZ Values10.2.2.1.5. Working with REG_MULTI_SZ Values10.2.2.2. Determining Special Folders10.2.2.3. Creating a Temporary File in the Temp Folder10.2.2.4. Creating Desktop Shortcuts10.2.2.5. Using the WshShell Object to Wait for a Process to Complete10.2.2.6. Working with Printers10.2.2.6.1. Adding Printers10.2.2.6.2. Removing Printers10.2.2.6.3. Enumerating Printers10.3. Automating Office Applications10.3.1. Determining If an Office Application Is Installed10.3.2. Word: Creating a Formatted Letter with Data10.3.3. Excel: Creating an Excel Chart with Data10.3.4. Outlook: Create Appointments from an Events Database10.4. Automating Internet Explorer10.4.1. Opening a URL in a New Tab in IE710.4.2. Common Web Queries10.4.3. Parsing HTML from Internet Explorer10.4.4. Summary
11. Creating Dynamic Ribbon Customizations
11.1. Overview of Ribbon Customizations in Access11.1.1. Development Tips11.1.1.1. Discovering Errors11.1.1.2. Using IntelliSense11.1.1.3. Prevent Loading at Startup11.1.1.4. Finding Controls and Images11.1.1.5. More Resources11.1.2. How to Write Ribbon Customizations11.1.2.1. Getting Started11.1.2.2. Common Attributes11.2. Loading Ribbons11.2.1. Using the USysRibbons Table11.2.2. Using the LoadCustomUI Method11.3. Programming the Ribbon11.3.1. Ribbon Objects11.3.2. Using Callback Routines11.3.2.1. Common Callback Routines11.3.2.2. Similar Properties, Methods, or Events in Access11.3.3. Refreshing Controls11.4. Organizing Ribbon Items11.4.1. Tabs11.4.2. Contextual Tabs11.4.3. Groups11.5. Ribbon Controls11.5.1. Buttons11.5.2. Toggle Buttons11.5.3. Check Boxes11.5.4. Combo Boxes and Drop-Downs11.5.5. Labels and Edit Boxes11.5.6. Menus11.5.6.1. itemSize Attribute11.5.6.2. description Attribute11.5.6.3. Menu Separators11.6. New Types of Controls11.6.1. Dialog Box Launcher11.6.2. Gallery11.6.3. Split Button11.6.4. Dynamic Menu11.7. Images11.7.1. Images Included with Office11.7.2. Loading Images from External Files11.7.2.1. Creating a Global Image Handler11.7.2.2. getImage and getItemImage Callbacks11.7.3. Loading Images from an Attachment Field11.7.3.1. Create the Table11.7.3.2. Create the Form11.7.3.3. Create the Customization11.7.3.4. Add the Callback11.8. Moving Functionality into the Ribbon11.8.1. The NotInList Event — Ribbon Style11.8.2. Form Navigation11.8.3. Managing Filters Using a Dynamic Menu11.8.4. Creating a Split Button That Sticks11.9. Other Ribbon Modifications11.9.1. Modifying the Office Menu11.9.2. Overriding Commands and Repurposing Controls11.10. Summary
IV. Finalizing the Application
12. Configuration and Extensibility
12.1. Localization12.1.1. Locale Settings12.1.1.1. Using the API to Determine the Locale12.1.1.2. Changing the User Locale12.1.2. Determining the Current Language of Office12.1.3. How to Create Localized Access Applications12.1.3.1. VBA Intrinsic Functions12.1.3.2. Localized Text Using Tables12.1.3.2.1. Creating the String Table12.1.3.2.2. Retrieving Strings from the String Table12.1.3.3. Creating a Resource DLL12.1.3.4. Deploying a Resource DLL12.1.3.5. Reading Resources Using the LoadString API12.2. Application Options12.2.1. Storing Options in Tables12.2.2. Storing Options in the Registry12.2.3. Creating a Class to Work with Options12.2.3.1. Creating the Option Constants12.2.3.2. Managing Options from Tables12.2.3.3. Managing Options in the Registry12.2.4. Creating Options Forms12.2.4.1. Creating the Form12.2.4.2. Adding Events12.2.5. Displaying Options in the Ribbon12.2.5.1. Creating the Customization12.2.5.2. Adding the Callbacks12.2.5.3. Modifying the Options Form12.3. Creating Form Themes12.3.1. Theming Forms12.3.1.1. Creating a Table to Store Theme Data12.3.1.2. Applying Themes at Runtime12.4. Summary
13. Personalization and Security
13.1. What Is Security?13.1.1. Authentication13.1.2. Authorization13.2. What Is Personalization?13.3. Overview of Security in Access 200713.3.1. Database Encryption13.3.2. Disabled Mode13.3.3. Office Trust Center13.3.3.1. Macro Settings13.3.3.2. Trusted Locations13.3.4. Digital Signatures13.3.5. Signed Packages13.3.5.1. Creating a Signed Package13.3.5.1.1. Creating the Self-Signed Certificate13.3.5.1.2. Creating the Package File13.3.5.2. Extracting the Signed Package13.4. Access 2007 Navigation Pane13.4.1. Customizing the Navigation Pane13.4.2. Restricting the View of the Navigation Pane13.4.2.1. Modify the Employees Table13.4.2.2. Create a Login Form13.4.3. Hiding the Design Surface of Objects13.5. Password-Protecting Objects13.5.1. Creating the User Table13.5.2. Creating the Login Form13.5.3. Prompting for Password with Forms and Reports13.5.4. Creating a Password Protected Form Object13.6. Windows Integration13.6.1. Determining the Logon Name13.6.2. Locking the Computer13.6.3. Receiving Notifications When the Computer Is Locked or Unlocked13.7. Simulating Record Level Security13.7.1. Limiting the View to a Particular User13.7.2. Locking Records Based on Logon13.8. Database Encryption with DAO and ADO13.8.1. Creating an Encrypted Database with DAO13.8.2. Changing the Database Password with DAO13.8.3. Adding a Database Password to a Database with DAO13.8.4. Changing the Database Password with ADO13.9. Best Practices13.9.1. Using a Client-Server Database to Protect Data13.9.2. AllowBypassKey Property13.9.3. Using an ACCDE or MDE File13.9.4. Using an ACCDR File13.9.5. File Format Selection13.10. Summary
14. Deployment
14.1. Creating an Automated Build14.1.1. Creating the Visual Studio Project14.1.1.1. Adding References14.1.1.2. Private Data14.1.1.3. Helper Functions14.1.1.4. Design the Form14.1.1.4.1. Adding Controls14.1.1.4.2. Setting Form Properties14.1.1.4.3. Control Events14.1.1.5. Retrieving Information from the Source Database14.1.2. Building the Application14.1.2.1. Creating the Target Database14.1.2.2. Set Build Properties on the Target Database14.1.2.3. Setting Build Properties on the Source Database14.1.2.4. Deleting Data from Tables14.1.2.5. Calculating the Version Number14.2. Handling Application Dependencies14.2.1. Installing Files from an Attachment Field14.2.2. Updating References14.2.3. Testing Reference Fix-Up14.2.3.1. Create the Library Database14.2.3.2. Call Code in the Library Database14.2.3.3. Break the Reference14.2.4. Late Binding14.3. Licensing Your Applications14.3.1. Creating a Limited-Use Application14.3.1.1. Expiration Date14.3.1.2. Number of Records14.3.1.3. Limited Functionality14.3.1.4. Restricting the Number of Times an Application is Launched14.3.2. Registering an Application14.3.2.1. Generating Registration Keys14.3.2.1.1. Numeric Registration Keys14.3.2.1.2. Alpha-Numeric Registration Keys14.3.2.1.3. Testing the Key Generation14.3.2.2. Creating a Registration Web Service14.3.2.2.1. Create the Registration Database14.3.2.2.2. Create the Web Service14.3.2.2.3. Validate the Registration Using the Web Service14.4. Miscellaneous Deployment Scenarios14.4.1. Updating Your Applications14.4.1.1. Checking for Latest Version14.4.1.1.1. Create the Versioning Server14.4.1.1.2. Create the Client Application14.4.1.1.3. Testing the Versioning Server14.4.1.2. Re-Linking Tables Automatically14.4.2. Programmatically Creating DSNs14.4.2.1. Creating a DSN-Less Connection14.4.2.2. Creating a User DSN14.4.2.3. Creating a System DSN14.4.3. Ensuring an Application Runs Locally14.5. Summary
15. Help and Documentation
15.1. Documentation via Built-in Properties15.1.1. Using the Description Property15.1.2. End SubSetting the Status Bar Text15.1.2.1. Setting Status Bar Text Programmatically15.1.3. Using the Tag Property15.1.4. Setting the Control Tip15.1.5. Database Documenter15.2. Providing Help to Users15.2.1. Storing Help Text in a Table15.2.2. Creating a Help Form15.2.3. Trapping the F1 Key15.2.4. Alternatives to a Floating Help Window15.3. External Approaches15.3.1. Writing HTML Content for Help15.3.2. Mapping HTML Files to Objects15.3.3. Opening the Browser15.3.4. Creating Compiled HTML Help Files15.3.4.1. Creating Content for HTML Help Workshop15.3.4.2. Add Files to Your Project and Set Project Properties15.3.4.3. Add Context Mapping15.3.4.4. Build the Table of Contents15.3.4.5. Compile the Help File15.3.4.6. Adding HelpContextID Values to Your Controls15.4. Summary
A. Programming Tips and Tricks
A.1. Dynamically Running CodeA.1.1. CallByNameA.1.1.1. Retrieve a Property ValueA.1.1.2. Set a Property ValueA.1.1.3. Call a MethodA.1.2. Application.RunA.1.3. EvalA.2. Object Helper FunctionsA.2.1. Object InitializationA.2.2. Object TerminationA.2.3. Global Object PropertiesA.3. Date Data Type TricksA.3.1. Looping Through DatesA.3.2. Arrays of DatesA.3.3. Determining Dates DynamicallyA.4. Miscellaneous TipsA.4.1. Categorizing Constant ValuesA.4.2. Comparing Class InstancesA.4.2.1. EqualsA.4.2.2. ReferenceEqualsA.4.3. Add Number of Retries to a Code BlockA.4.4. Add a Timeout to a Code BlockA.5. Tools for the Immediate WindowA.5.1. Clearing the Immediate WindowA.5.2. Displaying the Watch WindowA.5.3. Displaying the Locals WindowA.5.4. Making a Backup of ObjectsA.5.5. Closing All Code Windows
B. Query Performance
B.1. The Query OptimizerB.2. Designing Tables for PerformanceB.2.1. Use the Smallest Appropriate Data TypeB.2.2. IndexesB.2.2.1. Use Numeric Values for Primary Key FieldsB.2.2.2. Index Criteria, Sort, and Join FieldsB.2.2.3. Single Field vs. Multi-Field IndexesB.3. Designing Queries for PerformanceB.3.1. Reduce the Amount of DataB.3.1.1. Use Between ... And, IN OperatorsB.3.1.2. Avoid Calculated FieldsB.3.1.3. Use Fixed Column Headings in Your CROSSTAB QueriesB.3.1.4. Use Count(*) for TotalsB.3.1.5. Use Stored Queries Instead of Recordset PropertiesB.4. Other Performance ConsiderationsB.4.1. Bulk EditsB.4.2. Minimize Network TrafficB.4.3. Database MaintenanceB.4.4. Hard Drive MaintenanceB.5. Microsoft Access Performance Analyzer
C. Pattern Reference
C.1. Introducing PatternsC.1.1. What Is a PatternC.1.2. Why Use Patterns?C.2. Patterns Used in This BookC.2.1. Gang of Four PatternsC.2.1.1. PrototypeC.2.1.2. FactoryC.2.1.3. IteratorC.2.2. VBA Object PatternsC.2.2.1. CreationalC.2.2.1.1. Object InstantiationC.2.2.1.2. Recordset InstantiationC.2.2.2. DestructionC.2.2.2.1. Object DestructionC.2.2.2.2. Recordset DestructionC.2.3. Other PatternsC.2.3.1. Equals Method PatternC.2.3.2. Seek MethodC.2.3.3. Attachment FieldsC.2.4. Working with StringsC.2.4.1. Token ReplacementC.2.4.2. Formatting Numbers as HexadecimalC.2.5. Where and How to Find Patterns

Content preview from Expert Access™ 2007 Programming

Appendix B. Query Performance

Many different factors determine how efficient and performant your queries will be. Simply reordering elements of your query can make a world of difference. Other times, changes to your database schema will provide a more efficient design that the database engine can use to decrease the time it takes to run your queries. Even simple periodic maintenance can speed things up.

In this appendix, we discuss several issues and techniques you can use to design tables and queries that will make it easy to create efficient queries.

The Query Optimizer

Both the Access database engine and Jet database engine contain a Query Optimizer that analyzes a set of statistics and determines the optimal query execution strategy. Every time you save a query, the SQL is automatically run through the Optimizer, which determines the optimal way to run a query.

The Query Optimizer is a cost-based analyzer. The Optimizer examines the SQL and identifies a list of actions that can be performed when the query is executed. An estimated cost is generated for each action. The Optimizer identifies the least expensive set of actions that will generate the required results. This set of actions is known as the query execution strategy or query plan. The database engine stores the execution strategy along with the query so that when you run the query, the execution plan is already determined and you save the time of computing the optimal execution plan. Queries that have a stored execution ...

Become an O’Reilly member and get unlimited access to this title plus top books and audiobooks from O’Reilly and nearly 200 top publishers, thousands of courses curated by job role, 150+ live events each month,
and much more.

Read now

Unlock full access

More than 5,000 organizations count on O’Reilly

O’Reilly covers everything we've got, with content to help us build a world-class technology community, upgrade the capabilities and competencies of our teams, and improve overall team performance as well as their engagement.

Julian F.

Head of Cybersecurity

I wanted to learn C and C++, but it didn't click for me until I picked up an O'Reilly book. When I went on the O’Reilly platform, I was astonished to find all the books there, plus live events and sandboxes so you could play around with the technology.

Addison B.

Field Engineer

I’ve been on the O’Reilly platform for more than eight years. I use a couple of learning platforms, but I'm on O'Reilly more than anybody else. When you're there, you start learning. I'm never disappointed.

Amir M.

Data Platform Tech Lead

I'm always learning. So when I got on to O'Reilly, I was like a kid in a candy store. There are playlists. There are answers. There's on-demand training. It's worth its weight in gold, in terms of what it allows me to do.

Mark W.

Embedded Software Engineer

Publisher Resources

ISBN: 9780470174029Purchase book

Cloud Computing

Data Engineering

Data Science

AI & ML

Programming Languages

Software Architecture

IT/Ops

Security

Design

Business

Soft Skills

Expert Access™ 2007 Programming

by Rob Cooper, Michael Tucker

Appendix B. Query Performance

The Query Optimizer

Become an O’Reilly member and get unlimited access to this title plus top books and audiobooks from O’Reilly and nearly 200 top publishers, thousands of courses curated by job role, 150+ live events each month,
and much more.