II. What to Do When Things Don’t Go As Planned
by Alison Balter
Alison Balter’s Mastering Microsoft Access 2002 Desktop Development
- Copyright
- About the Author
- Acknowledgments
- Tell Us What You Think!
- Introduction
- I. The Basics of Access Development
- 1. Access as a Development Tool
- Why This Chapter Is Important
- What Types of Applications Can You Develop in Access?
- Access as a Development Platform for Personal Applications
- Access as a Development Platform for Small-Business Applications
- Access as a Development Platform for Departmental Applications
- Access as a Development Platform for Corporation-Wide Applications
- Access as a Development Platform for Enterprise-Wide Client/Server Applications
- Access as a Development Platform for Intranet/Internet Applications
- Access as a Scalable Product
- What Exactly Is a Database?
- Getting to Know the Database Objects
- Tables: A Repository for Your Data
- Queries: Stored Questions or Actions to Be Applied to Your Data
- Forms: A Means of Displaying, Modifying, and Adding Data
- Reports: Turning Data into Information
- Data Access Pages: Forms Viewed in a Browser
- Macros: A Means of Automating Your System
- Modules: The Foundation to the Application Development Process
- Object Naming Conventions
- Hardware Requirements
- How Do I Get Started Developing an Access Application?
- What’s New in Access 2002
- Summary
- 2. What Every Developer Needs to Know About Tables
- Why This Chapter Is Important
- Building a New Table
- Selecting the Appropriate Field Type for Your Data
- Text Fields: The Most Common Field Type
- Memo Fields: For Those Long Notes and Comments
- Number Fields: For When You Need to Calculate
- Date/Time Fields: Tracking When Things Happened
- Currency Fields: Storing Money
- AutoNumber Fields: For Unique Record Identifiers
- Yes/No Fields: When One of Two Answers Is Correct
- OLE Object Fields: The Place to Store Just About Anything
- Hyperlink Fields: Your Link to the Internet
- Working with Field Properties
- Field Size: Limiting What’s Entered into a Field
- Format: Determining How Data Is Displayed
- Input Mask: Determining What Data Goes into a Field
- Caption: A Great Timesaver
- Default Value: Saving Data-Entry Time
- Validation Rule: Controlling What’s Entered in a Field
- Validation Text: Providing Error Messages to the User
- Required: Making the User Enter a Value
- Allow Zero Length: Accommodating Situations with Nonexistent Data
- Indexes: Speeding Up Searches
- Unicode Compression
- The All-Important Primary Key
- Working with the Lookup Feature
- Working with Table Properties
- Using Indexes to Improve Performance
- Access Tables and the Internet
- Working with PivotTable and PivotChart Views
- Summary
- 3. Relationships: Your Key to Data Integrity
- Why This Chapter Is Important
- Introduction to Relational Database Design
- Establishing Relationships in Access
- Establishing Referential Integrity
- Looking at the Benefits of Relationships
- Examining Indexes and Relationships
- Summary
- 4. What Every Developer Needs to Know About Query Basics
- Why This Chapter Is Important?
- What Is a Query, and When Should You Use One?
- Everything You Need to Know About Query Basics
- Ordering Your Query Result
- Refining Your Query with Criteria
- Working with Dates in Criteria
- Understanding How Query Results Can Be Updated
- Building Queries Based on Multiple Tables
- Creating Calculated Fields
- Getting Help from the Expression Builder
- Summarizing Data with Totals Queries
- Excluding Fields from the Output
- Nulls and Query Results
- Refining Your Queries with Field, Field List, and Query Properties
- Building Parameter Queries When You Don’t Know the Criteria at Design Time
- Summary
- 5. What Every Developer Needs to Know About Forms
- Why This Chapter Is Important
- Uses of Forms
- Anatomy of a Form
- Creating a New Form
- Working with the Form Design Window
- Selecting the Correct Control for the Job
- Control Morphing
- Conditional Formatting
- What Form Properties Are Available, and Why Should You Use Them?
- What Control Properties Are Available, and Why Use Them?
- Bound, Unbound, and Calculated Controls
- Using Expressions to Enhance Your Forms
- The Command Button Wizards: Programming Without Typing
- Building Forms Based on More Than One Table
- Basing Forms on Queries: The Why and How
- Access Forms and the Internet
- Summary
- 6. What Every Developer Needs to Know About Reports
- Why This Chapter Is Important
- Types of Reports Available
- Anatomy of a Report
- Creating a New Report
- Working with the Report Design Window
- Selecting the Correct Control for the Job
- What Report Properties Are Available and Why Use Them
- What Control Properties Are Available and Why Use Them
- Inserting Page Breaks
- Unbound, Bound, and Calculated Controls
- Using Expressions to Enhance Your Reports
- Building Reports Based on More Than One Table
- Working with Sorting and Grouping
- Improving Performance and Reusability by Basing Reports on Stored Queries or Embedded SQL Statements
- Access Reports and the Internet
- Summary
- 7. VBA: An Introduction
- Why This Chapter Is Important
- VBA Explained
- What Are Access Class Modules, Standard Modules, Form Modules, and Report Modules?
- Working with Variables
- Adding Comments to Your Code
- Using the Line Continuation Character
- Using the VBA Control Structures
- Passing Parameters and Returning Values
- Executing Procedures from the Module Window
- The DoCmd Object: Performing Macro Actions
- Working with Built-In Functions
- Working with Constants
- Tools for Working in the Visual Basic Editor
- Customizing the VBE
- Summary
- 8. Objects, Properties, Methods, and Events Explained
- Why This Chapter Is Important
- Understanding Objects, Properties, Events, and Methods
- Using the Object Browser to Learn About Access’s Objects
- Referring to Objects
- Properties and Methods Made Easy
- Declaring and Assigning Object Variables
- Understanding the Differences Between Objects and Collections
- Passing Objects to Subroutines and Functions
- Determining the Type of a Control
- Special Properties That Refer to Objects
- Understanding Access’s Object Model
- New Access 2002 Properties
- Summary
- 9. Advanced Form Techniques
- Why This Chapter Is Important
- What Are the Form Events, and When Do You Use Them?
- Current
- BeforeInsert
- AfterInsert
- BeforeUpdate
- AfterUpdate
- Dirty
- Undo
- Delete
- BeforeDelConfirm
- AfterDelConfirm
- Open
- Load
- Resize
- Unload
- Close
- Activate
- Deactivate
- GotFocus
- LostFocus
- Click
- DblClick
- MouseDown
- MouseMove
- MouseUp
- KeyDown
- KeyUp
- KeyPress
- Error
- Filter
- ApplyFilter
- Timer
- Understanding the Sequence of Form Events
- What Are the Section and Control Events, and When Do You Use Them?
- Referring to Me
- What Types of Forms Can I Create, and When Are They Appropriate?
- Single Forms: Viewing One Record at a Time
- Continuous Forms: View Multiple Records at a Time
- Multipage Forms: When Everything Doesn’t Fit on One Screen
- Tabbed Forms: Conserving Screen Real Estate
- Switchboard Forms: Controlling Your Application
- Splash Screen Forms: A Professional Opening to Your Application
- Dialog Forms: Gathering Information
- Using Built-In Dialog Boxes
- Adding Custom Menus, Toolbars, and Shortcut Menus to Your Forms
- Taking Advantage of Built-In, Form-Filtering Features
- Including Objects from Other Applications: Linking Versus Embedding
- OpenArgs
- Switching a Form’s RecordSource
- Power Combo Box and List Box Techniques
- Power Subform Techniques
- Synchronizing a Form with Its Underlying Recordset
- Creating Custom Properties and Methods
- Summary
- 10. Advanced Report Techniques
- Why This Chapter Is Important
- Events Available for Reports, and When to Use Them
- Order of Events for Reports
- Events Available for Report Sections, and When to Use Them
- Programmatically Manipulating Report Sections
- Special Report Properties
- Controlling the Printer
- Practical Applications of Report Events and Properties
- Changing a Report’s RecordSource
- Working with Report Filters
- Working with the Report Sort Order
- Using the Same Report to Display Summary, Detail, or Both
- Numbering Report Items
- Printing Multiple Labels
- Determining Where a Label Prints
- Building a Report from a Crosstab Query
- Printing the First and Last Page Entries in the Page Header
- Creating a Multifact Crosstab Report
- Summary
- 11. Advanced Query Techniques
- Why This Chapter Is Important
- Action Queries
- Special Query Properties
- Optimizing Queries
- Crosstab Queries
- Outer Joins
- Self Joins
- Understanding SQL
- Union Queries
- Pass-Through Queries
- The Propagation of Nulls and Query Results
- Subqueries
- Using SQL to Update Data
- Using SQL for Data Definition
- Using the Result of a Function as the Criteria for a Query
- Passing Parameter Query Values from a Form
- Jet 4.0 ANSI-92 Extensions
- Summary
- 12. Advanced VBA Techniques
- Why This Chapter Is Important
- What Are User-Defined Types, and Why Would You Use Them?
- Working with Constants
- Working with Arrays
- Advanced Function Techniques
- Working with Empty and Null
- Creating and Working with Custom Collections
- Low-Level File Handling
- Understanding and Effectively Using Compilation Options
- Importing and Exporting Code Modules
- Working with Project Properties
- Summary
- 13. Exploiting the Power of Class Modules
- Exploring the Benefits of Class Modules
- Object Orientation—An Introduction
- Creating and Using a Class Module
- Creating Multiple Class Instances
- The Initialize and Terminate Events
- Working with Enumerated Types
- Building Hierarchies of Classes
- Adding a Parent Property to Classes
- The Implements Keyword
- Working with Custom Collections
- Adding Your Own Events
- Summary
- 14. What Are ActiveX Data Objects and Data Access Objects, and Why Are They Important?
- Why This Chapter Is Important
- Using ActiveX Data Objects Versus Data Access Objects
- Examining the ActiveX Data Object Model
- Understanding ADO Recordset Types
- Working with ADO Recordset Properties and Methods
- Examining Record-Movement Methods
- Detecting the Limits of a Recordset
- Counting the Number of Records in a Recordset
- Sorting, Filtering, and Finding Records
- Using the AbsolutePosition Property
- Using the Bookmark Property
- Running Parameter Queries
- Refreshing Recordset Data
- Working with Persisting Recordsets
- Modifying Table Data Using ADO Code
- Creating and Modifying Database Objects Using ADO Code
- Examining the Data Access Object Model
- Getting to Know DBEngine
- Using CurrentDB()
- Understanding DAO Recordset Types
- Selecting Among the Types of DAO Recordset Objects Available
- Working with DAO Recordset Properties and Methods
- Creating a Recordset Variable
- Using Arguments to Open a Recordset
- Examining Record-Movement Methods
- Detecting the Limits of a Recordset
- Counting the Number of Records in a Recordset
- Sorting, Filtering, and Finding Records
- Using the AbsolutePosition Property
- Using the Bookmark Property
- Using the RecordsetClone Property
- Running Parameter Queries
- Modifying Table Data Using DAO Code
- Creating and Modifying Database Objects Using DAO Code
- Using the DAO Containers Collection
- Summary
- 1. Access as a Development Tool
- II. What to Do When Things Don’t Go As Planned
- 15. Debugging: Your Key to Successful Development
- Why This Chapter Is Important
- Avoiding Bugs
- Harnessing the Power of the Immediate Window
- Invoking the Debugger
- Using Breakpoints to Troubleshoot
- Stepping Through Code
- Setting the Next Statement to Execute
- Using the Calls Window
- Working with the Locals Window
- Working with Watch Expressions
- Continuing Execution After a Runtime Error
- Looking At Gotchas with the Immediate Window
- Using Assertions
- Debugging Tips
- Summary
- 16. Error Handling: Preparing for the Inevitable
- Why This Chapter Is Important
- Implementing Error Handling
- Using On Error Statements
- Using Resume Statements
- Clearing an Error
- Examining the Cascading Error Effect
- Using the Err Object
- Raising an Error
- Using the Errors Collection
- Creating a Generic Error Handler
- Preventing Your Own Error Handling from Being Invoked
- Creating a Call Stack
- Building a Custom Error Handler Class
- Working with Error Events
- Creating a List of Error Codes and Descriptions
- Summary
- 17. Optimizing Your Application
- Why This Chapter Is Important
- Introducing Optimization
- Modifying Hardware and Software Configurations
- What Jet 3.5 Did to Improve Performance
- Understanding What Jet 4.0 Does to Improve Performance
- Letting the Performance Analyzer Determine Problem Areas
- Designing Tables to Optimize Performance
- Designing Queries to Optimize Performance
- Making Coding Changes to Improve Performance
- Eliminate Variants and Use the Smallest Data Type Possible
- Use Specific Object Types
- Use Inline Code
- Toggle Booleans Using Not
- Use the Built-In Collections
- Use the Len Function
- Use True and False Instead of 0
- Use Transactions...Sometimes?
- Eliminate Unused Dim and Declare Statements
- Eliminate Unused Code
- Use Variables to Refer to Properties, Controls, and Data Access Objects
- Use With...End With
- Resolve Variable Outside a Loop
- Use the Me Keyword
- Use String Functions When VBA Provides a Variant and String Alternative
- Use Dynamic Arrays
- Use Constants When They Are Available
- Use Bookmarks
- Set Object Variables Equal to Nothing
- Use Action Queries Instead of Looping Through Recordsets
- Deliver Your Application with the Modules Compiled
- Retaining the Compiled State
- Distribute Your Application as an MDE
- Organize Your Modules
- Designing Forms and Reports to Improve Performance
- Summary
- 15. Debugging: Your Key to Successful Development
- III. Developing Multiuser and Enterprise Applications
- 18. A Strategy to Developing Access Applications
- Why This Chapter Is Important
- Splitting Databases into Tables and Other Objects
- Basing Forms and Reports on Queries or Embedded SQL Statements
- 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
- Summary
- 19. Using External Data
- Why This Chapter Is Important
- Importing, Linking, and Opening Files: When and Why
- Importing External Data
- Creating a Link to External Data
- Opening an External Table
- Understanding Windows Registry Settings
- Using the Jet OLEDB:Link Provider String
- Working with Passwords
- Refreshing and Removing Links
- Looking at Special Considerations
- Troubleshooting
- Looking at Performance Considerations and Links
- Working with HTML Documents
- Summary
- 20. Developing Multiuser and Enterprise Applications
- Why This Chapter Is Important
- Designing Your Application with Multiuser Issues in Mind
- Understanding Access’s Locking Mechanisms
- Understanding the Client/Server Model
- Deciding Whether to Use the Client/Server Model
- The Roles Access Plays in the Application Design Model
- The Front End and Back End as Access MDB Files
- The Front End as an MDB File Using Links to Communicate to a Back End
- The Front End Using SQL Pass-Through to Communicate to a Back End
- The Front End Executing Procedures Stored on a Back End
- The Front End as a Microsoft Access Data Project Communicating Directly to a Back End
- Learning the Client/Server Buzzwords
- Upsizing: What to Worry About
- Proactively Preparing for Upsizing
- Introduction to Transaction Processing
- Understanding the Benefits of Transaction Processing
- Modifying the Default Behavior of Transaction Processing
- Implementing Explicit Transaction Processing
- Introduction to Replication
- Uses of Replication
- Understanding When Replication Isn’t Appropriate
- Understanding the Implementation of Replication
- Summary
- 18. A Strategy to Developing Access Applications
- IV. Black-Belt Programming
- 21. Using ActiveX Controls
- Why This Chapter Is Important
- Incorporating ActiveX Controls in Access 2002
- Setting Properties of an ActiveX Control at Design Time
- Coding Events of an ActiveX Control
- Using the Calendar Control
- Using the UpDown Control
- Using the StatusBar Control
- Using the Common Dialog Control
- Using the Rich Textbox Control
- Using the TabStrip Control
- Using the ImageList Control
- Licensing and Distribution Issues
- Summary
- 22. Automation: Communicating with Other Applications
- Why This Chapter Is Important
- Defining Some Automation Terms
- Declaring an Object Variable to Reference Your Application
- Creating an Automation Object
- Manipulating an Automation Object
- Early Binding Versus Late Binding
- Controlling Excel from Access
- Closing an Excel Automation Object
- Creating a Graph from Access
- Controlling Word from Access
- Controlling PowerPoint from Access
- Automating Outlook from Access
- Controlling Access from Other Applications
- Summary
- 23. Exploiting the Power of the Windows API
- Why This Chapter Is Important
- Declaring an External Function to the Compiler
- Working with Constants and Types
- Calling DLL Functions: Important Issues
- Examining the Differences Between 16-Bit and 32-Bit APIs
- Using API Functions
- Manipulating the Windows Registry
- Getting Information About the Operating Environment
- Determining Drive Types and Available Drive Space
- Summary
- 24. Creating Your Own Libraries
- 25. Using Builders, Wizards, and Menu Add-Ins
- 26. An Introduction to Access and the Internet/Intranet
- Why This Chapter Is Important
- Saving Database Objects as HTML
- Linking to HTML Files
- Importing HTML Files
- Understanding Data Access Pages
- Creating Data Access Pages
- Modifying Important Properties of a Data Access Page
- Modifying the Record Navigation Control Properties
- Creating Grouped Data Access Pages
- Augmenting Data Access Pages with VBScript
- Summary
- 21. Using ActiveX Controls
- V. Adding Polish to Your Application
- 27. Database Security Made Easy
- Why This Chapter Is Important
- Implementing Share-Level Security: Establishing a Database Password
- Encrypting a Database
- Establishing User-Level Security
- Step 1: Creating a Workgroup
- Step 2: Changing the Password for the Admin User
- Step 3: Creating an Administrative User
- Step 4: Making the Administrative User a Member of the Admins Group
- Step 5: Exiting Access and Logging On as the System Administrator
- Step 6: Removing the Admin User from the Admins Group
- Step 7: Assigning a Password to the System Administrator
- Step 8: Opening the Database You Want to Secure
- Step 9: Running the Security Wizard
- Step 10: Creating Users and Groups
- Step 11: Assigning Rights to Users and Groups
- Securing VBA Code with a Password
- Providing an Additional Level of Security: Creating an MDE
- Securing a Database Without Requiring Users to Log On
- Looking at Special Issues
- Summary
- 28. Advanced Security Techniques
- Why This Chapter Is Important
- Maintaining Groups Using Code
- Using Code to Maintain Users
- Listing All Groups and Users
- Working with Passwords
- Assigning and Revoking Permissions to Objects Using Code
- Encrypting a Database Using Code
- Accomplishing Field-Level Security Using Queries
- Prohibiting Users and Groups from Creating Objects
- Accomplishing Prohibited Tasks by Logging On as a Different User
- Securing Client/Server Applications
- Security and Replication
- Implementing Security with SQL
- DAO and Security
- Choosing Between ADOX, SQL, and DAO
- Summary
- 29. Documenting Your Application
- 30. Maintaining Your Application
- 31. Third-Party Tools That Can Help You to Get Your Job Done Effectively
- Why This Chapter Is Important
- Total Access CodeTools
- Total Access Analyzer
- Total Visual Agent
- Total Access Components
- Total Access Statistics
- Total Access Detective
- Total Visual SourceBook
- Total Access Memo
- Solutions::Explorer
- Component Toolbox OCX
- Solutions::Schedule
- Solutions::PIM Professional
- Summary
- 32. Distributing Your Application
- Why This Chapter Is Important
- Introducing the Packaging Wizard
- Loading the Packaging Wizard Add-In
- Distributing Your Application to Run with a Full Copy of Access
- Using Full Versions Versus Runtime Versions of Access
- Preparing Your Database for Use with the Access Runtime Version
- Creating the Application
- Adding Custom Help to the Application
- Testing and Debugging the Application
- Running and Testing the Application with the /Runtime Command-Line Switch
- Running the Packaging Wizard
- Identifying Application and Package Information
- Supplying Application Information
- List of Files to Search for Dependency Information
- Inclusion of the Runtime
- Modifying Installation Locations
- Inclusion of Database Components
- Selecting Start Menu Items to be Created
- Choosing a File to Run When the Installation Is Complete
- The Final Step
- Deploying the Application
- Distributing the Application
- Looking at Other Issues
- Summary
- 27. Database Security Made Easy
- VI. Appendixes
- A. Table Structures
- The tblClients Table
- The tblClientAddresses Table
- The tblAddressTypes Table
- The tblClientPhones Table
- The tblPhoneTypes Table
- The tblCorrespondence Table
- The tblCorrespondenceTypes Table
- The tblTerms Table
- The tblContactTypes Table
- The tblCompanyInfo Table
- The tblEmployees Table
- The tblErrorLog Table
- The tblErrors Table
- The tblExpenseCodes Tables
- The tblPaymentMethods Table
- The tblPayments Table
- The tblProjects Table
- The tblTimCardExpenses Table
- The tblTimeCardHours Table
- The tblTimeCards Table
- The tblWorkCodes Table
- B. Naming Conventions
- A. Table Structures
-
No Comment
..................Content has been hidden....................
You can't read the all page of ebook, please click here login for view all page.