Home Page Icon
Home Page
Table of Contents for
II. What to Do When Things Don’t Go As Planned
Close
II. What to Do When Things Don’t Go As Planned
by Alison Balter
Alison Balter’s Mastering Microsoft Access 2002 Desktop Development
Copyright
Dedication
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
Relationships: Tying the Tables Together
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
What Hardware Does the Developer’s System Require?
What Hardware Does the User’s System Require?
How Do I Get Started Developing an Access Application?
Task Analysis
Data Analysis and Design
Normalization Made Easy
Prototyping
Testing
Implementation
Maintenance
What’s New in Access 2002
What’s New with Forms
What’s New with Reports
The Exciting World of PivotTables and PivotCharts
Welcome to the Programmability Enhancements in Access 2002
Access 2002 and XML
What’s New with ADP Projects
What’s New with Data Access Pages
Other New Features Found in Access 2002
Summary
2. What Every Developer Needs to Know About Tables
Why This Chapter Is Important
Building a New Table
Designing a Table from Scratch
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
The Hyperlink Field Type
Saving Table Data as HTML
Saving Table Data as XML
Working with PivotTable and PivotChart Views
Summary
3. Relationships: Your Key to Data Integrity
Why This Chapter Is Important
Introduction to Relational Database Design
The History of Relational Database Design
Goals of Relational Database Design
Rules of Relational Database Design
The Rules of Tables
The Rules of Uniqueness and Keys
Foreign Keys and Domains
Normalization and Normal Forms
First Normal Form
Second Normal Form
Third Normal Form
Denormalization—Purposely Violating the Rules
Integrity Rules
Overall Rules
Database-Specific Rules
Examining the Types of Relationships
One-to-Many
One-to-One
Many-to-Many
Establishing Relationships in Access
Establishing a Relationship Between Two Tables
Looking at Guidelines for Establishing Relationships
Modifying an Existing Relationship
Establishing Referential Integrity
Cascade Update Related Fields
Cascade Delete Related Records
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
Adding Tables to Your Queries
Adding Fields to Your Query
Removing a Field from the Query Design Grid
Inserting a Field After the Query Is Built
Moving a Field to a Different Location on the Query Design Grid
Saving and Naming Your Query
Ordering Your Query Result
Sorting by More than One Field
Refining Your Query with Criteria
Working with Dates in Criteria
Understanding How Query Results Can Be Updated
Building Queries Based on Multiple Tables
Pitfalls of Multitable Queries
Row Fix-Up in Multitable Queries
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
Field Properties: Changing the Behavior of a Field
Field List Properties: Changing the Properties of the Field List
Query Properties: Changing the Behavior of the Overall Query
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
Creating a Form with the Form Wizard
Creating a Form from Design View
Working with the Form Design Window
Understanding and Working with the Form Design Tools
Toggling the Tools to Get What You Want
Adding Fields to the Form
Selecting, Moving, Aligning, and Sizing Form Objects
Selecting Form Objects
Moving Things Around
Aligning Objects to One Another
Snap to Grid
Power Sizing Techniques
Controlling Object Spacing
Modifying Object Tab Order
Selecting the Correct Control for the Job
Labels
Text Boxes
Combo Boxes
List Boxes
Check Boxes
Option and Toggle Buttons
Option Groups
Control Morphing
Morphing a Text Box to a Combo Box
Morphing a Combo Box to a List Box
Conditional Formatting
What Form Properties Are Available, and Why Should You Use Them?
Working with the Properties Window
Working with the Important Form Properties
Format Properties of a Form
Data Properties of a Form
Other Properties of a Form
What Control Properties Are Available, and Why Use Them?
Format Properties of a Control
Data Properties of a Control
Other Properties of a Control
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
Creating One-to-Many Forms
Building a One-to-Many Form by Using the Form Wizard
Building a One-to-Many Form with the Subform/Subreport Wizard
Working with Subforms
Basing Forms on Queries: The Why and How
Embedding SQL Statements Versus Stored Queries
Access Forms and the Internet
Adding a Hyperlink to a Form
Saving a Form as HTML
Saving a Form as XML
Saving a Form as Microsoft Active Server Pages or Microsoft IIS 1-2
Designing the Clients Form
Designing the Projects Form
Adding a Command Button That Links the Clients and Projects Forms
What’s Ahead
Summary
6. What Every Developer Needs to Know About Reports
Why This Chapter Is Important
Types of Reports Available
Detail Reports
Summary Reports
Cross-tabulation Reports
Reports with Graphics and Charts
Reports with Forms
Reports with Labels
Anatomy of a Report
Creating a New Report
Creating a Report with the Report Wizard
Creating a Report from Design View
Working with the Report Design Window
Understanding the Report Design Tools
Adding Fields to the Report
Selecting, Moving, Aligning, and Sizing Report Objects
Selecting Report Objects
Moving Things Around
Aligning Objects with One Another
Using Snap to Grid
Using Power-Sizing Techniques
Controlling Object Spacing
Selecting the Correct Control for the Job
Labels
Text Boxes
Lines
Rectangles
Bound Object Frames
Unbound Object Frames
Image Controls
Other Controls
What Report Properties Are Available and Why Use Them
Working with the Properties Window
The Report’s Format Properties
The Report’s Data Properties
Other Report Properties
What Control Properties Are Available and Why Use Them
The Control’s Format Properties
The Control’s Data Properties
The Other Control Properties
Inserting Page Breaks
Unbound, Bound, and Calculated Controls
Using Expressions to Enhance Your Reports
Building Reports Based on More Than One Table
Creating One-to-Many Reports
Building a One-to-Many Report with the Report Wizard
Building a Report Based on a One-To-Many Query
Building a One-to-Many Report with the Subreport Wizard
Working with Subreports
Working with Sorting and Grouping
Adding Sorting and Grouping
Sorting and Grouping Properties
What Are Group Header and Footer Properties and Why Use Them?
Improving Performance and Reusability by Basing Reports on Stored Queries or Embedded SQL Statements
Access Reports and the Internet
Adding a Hyperlink to a Report
Saving a Report as HTML
Saving a Report as XML
Designing the rptClientListing Report
Designing the rptTimeSheet Report
Summary
7. VBA: An Introduction
Why This Chapter Is Important
VBA Explained
What Are Access Class Modules, Standard Modules, Form Modules, and Report Modules?
Where Is VBA Code Written?
The Anatomy of a Module
Option Explicit
Creating Event Procedures
Creating Functions and Subroutines
Creating a User-Defined Routine in a Code Module
Creating a User-Defined Routine in a Form or Report Class Module
Calling Event and User-Defined Procedures
Scope and Lifetime of Procedures
Public Procedures
Private Procedures
Scope Precedence
Static Procedures
Working with Variables
Declaring Variables
VBA Data Types
Scope and Lifetime of Variables: Exposing Your Variables as Little as Possible
Local Variables
Static Variables: A Special Type of Local Variable
Private Variables
Public Variables
Adding Comments to Your Code
Using the Line Continuation Character
Using the VBA Control Structures
If...Then...Else
Immediate If (IIf)
The Conditional If: Conditional Compilation
Select Case
Looping
For...Next
With...End With
For Each...Next
Passing Parameters and Returning Values
Executing Procedures from the Module Window
The DoCmd Object: Performing Macro Actions
Working with Built-In Functions
Built-In Functions
Format
Instr
InStrRev
Left
Right
Mid
UCase
DatePart
DateDiff
DateAdd
Replace
StrRev
MonthName
Functions Made Easy with the Object Browser
Working with Constants
Working with Symbolic Constants
Scoping Symbolic Constants
Working with Intrinsic Constants
Tools for Working in the Visual Basic Editor
List Properties and Methods
List Constants
Quick Info
Parameter Information
Complete Word
Definition
Mysteries of the Coding Environment Solved
The Project Window
The Properties Window
The View Microsoft Access Tool
Find and Replace
Help
Splitting the Code Window
Using Bookmarks to Save Your Place
Customizing the VBE
Coding Options—The Editor Tab
Code Color, Fonts, and Sizes—The Editor Format Tab
General Options—The General Tab
Docking Options—The Docking Tab
Summary
8. Objects, Properties, Methods, and Events Explained
Why This Chapter Is Important
Understanding Objects, Properties, Events, and Methods
What Exactly Are Objects?
What Exactly Are Properties?
What Exactly Are Events?
What Exactly Are Methods?
Using the Object Browser to Learn About Access’s Objects
How to Use the Object Browser
Pasting Code Templates into a Procedure
Referring to Objects
Properties and Methods Made Easy
Default Properties
Declaring and Assigning Object Variables
Object Variables Versus Regular Variables
Generic Versus Specific Object Variables
Cleaning Up After Yourself
Understanding the Differences Between Objects and Collections
Manipulating a Single Object
With...End With: Performing Multiple Commands on an Object
Manipulating a Collection of Objects
For...Each: Performing the Same Command on Multiple Objects
Passing Objects to Subroutines and Functions
Determining the Type of a Control
Special Properties That Refer to Objects
Understanding Access’s Object Model
The Application Object
Application Object Properties
Application Object Methods
The Forms Collection
The Reports Collection
The DataAccessPages Collection
The Modules Collection
The CurrentProject Object
The AllForms Collection
The AllReports Collection
The AllMacros Collection
The AllModules Collection
The AllDataAccessPages Collection
The CurrentData Object
The AllTables Collection
The AllQueries Collection
The CodeProject Object
The CodeData Object
The Screen Object
The DoCmd Object
New Access 2002 Properties
Enabling and Disabling Command Buttons
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 Happens When a Form Is Opened?
What Happens When a Form Is Closed?
What Happens When a Form Is Sized?
What Happens When Focus Shifts from One Form to Another?
What Happens When Keys Are Pressed?
What Happens When Mouse Actions Take Place?
What Are the Section and Control Events, and When Do You Use Them?
BeforeUpdate
AfterUpdate
Updated
Change
NotInList
Enter
Exit
GotFocus
LostFocus
Click
DblClick
MouseDown
MouseMove
MouseUp
KeyDown
KeyUp
KeyPress
Understanding the Sequence of Control Events
What Happens When Focus Is Moved to or from a Control?
What Happens When the Data in a Control Is Updated?
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
Adding a Tab Control and Manipulating Its Pages
Adding Controls to the Pages of a Tab Control
Modifying the Tab Order of Controls
Changing the Properties of the Tab Control
Changing the Properties of Each Page
Switchboard Forms: Controlling Your Application
Splash Screen Forms: A Professional Opening to Your Application
Dialog Forms: Gathering Information
Using Built-In Dialog Boxes
Message Boxes
The MsgBox Function
Input Boxes
The FileDialog Object
Adding Custom Menus, Toolbars, and Shortcut Menus to Your Forms
Designing a Menu
Associating a Command with a Menu Item
Deleting and Renaming Menus
Manipulating Command Bars by Using Code
Taking Advantage of Built-In, Form-Filtering Features
Including Objects from Other Applications: Linking Versus Embedding
Bound OLE Objects
Unbound OLE Objects
OpenArgs
Switching a Form’s RecordSource
Power Combo Box and List Box Techniques
Handling the NotInList Event
Working with a Pop-Up Form
Adding Items to a Combo Box or List Box at Runtime
Populating a Combo or List Box with a Callback Function
Handling Multiple Selections in a List Box
Power Subform Techniques
Referring to Subform Controls
Synchronizing a Form with Its Underlying Recordset
Creating Custom Properties and Methods
Creating Custom Properties
Creating and Using a Public Variable as a Form Property
Creating and Using Custom Properties with PropertyLet and PropertyGet Routines
Creating Custom Methods
Getting Things Going with a Startup Form
Building a Splash Screen
Summary
10. Advanced Report Techniques
Why This Chapter Is Important
Events Available for Reports, and When to Use Them
The Open Event
The Close Event
The Activate Event
The Deactivate Event
The NoData Event
The Page Event
The Error Event
Order of Events for Reports
Events Available for Report Sections, and When to Use Them
The Format Event
The Print Event
The Retreat Event
Order of Section Events
Programmatically Manipulating Report Sections
Special Report Properties
MoveLayout
NextRecord
PrintSection
Interaction of MoveLayout, NextRecord, and PrintSection
FormatCount
PrintCount
HasContinued
WillContinue
Controlling the Printer
The Printer Object
The Printers Collection
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
Update Queries
Delete Queries
Append Queries
Make Table Queries
Special Notes About Action Queries
Using Action Queries Versus Processing Records with Code
Special Query Properties
Unique Values
Unique Records
Top Values
Optimizing Queries
Understanding the Query Compilation Process
Analyzing a Query’s Performance
Things You Can Do to Improve a Query’s Performance
Rushmore Technology
Important Notes About Rushmore
Crosstab Queries
Creating a Crosstab Query with the Crosstab Query Wizard
Creating a Crosstab Query Without the Crosstab Query Wizard
Creating Fixed Column Headings
Important Notes About Crosstab Queries
Outer Joins
Self Joins
Understanding SQL
What Is SQL, and Where Did It Come From?
What Do You Need to Know About SQL?
SQL Syntax
The SELECT Statement
The SELECT Clause
The FROM Clause
The WHERE Clause
The ORDER BY Clause
The JOIN Clause
Self Joins
Non-equi Joins
ALL, DISTINCTROW, and DISTINCT Clauses
The TOP Predicate
The GROUP BY Clause
The HAVING Clause
Applying What You Have Learned
Using the Graphical QBE Grid as a Two-Way Tool
Including SQL Statements in VBA Code
Union Queries
The ALL Keyword
Sorting the Query Results
Using the Graphical QBE to Create a Union Query
Important Notes about Union Queries
Pass-Through Queries
The Propagation of Nulls and Query Results
Subqueries
Using SQL to Update Data
The UPDATE Statement
The DELETE Statement
The INSERT INTO Statement
The SELECT INTO Statement
Using SQL for Data Definition
The CREATE TABLE Statement
The CREATE INDEX Statement
The ALTER TABLE Statement
The DROP INDEX Statement
The DROP TABLE Statement
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
Table Extensions
Creating Defaults
Creating Check Constraints
Implementing Cascading Referential Integrity
Controlling Fast Foreign Keys
Implementing Unicode String Compression
Controlling Autonumber Fields
View and Stored Procedures Extensions
Transaction Extensions
Security Extensions
Archive Payments
Show All Payments
Create State Table
Summary
12. Advanced VBA Techniques
Why This Chapter Is Important
What Are User-Defined Types, and Why Would You Use Them?
Declaring a User-Defined Type
Creating a Type Variable
Storing Information from a Record in a Form into a Type
Retrieving Information from the Elements of a Type
Working with Constants
Defining Your Own Constants
Scoping Symbolic Constants
Working with Intrinsic Constants
Working with Arrays
Declaring and Working with Fixed Arrays
Declaring and Working with Dynamic Arrays
Passing Arrays as Parameters
Advanced Function Techniques
Passing by Reference Versus Passing by Value
Optional Parameters: Building Flexibility into Functions
Named Parameters: Eliminate the Need to Count Commas
Recursive Procedures
Using Parameter Arrays
Working with Empty and Null
Working with Empty
Working with Null
Creating and Working with Custom Collections
Creating a Collection
Adding Items to a Collection
Accessing an Item in a Collection
Removing Items from a Collection
Iterating Through the Elements of a Collection
Low-Level File Handling
Understanding and Effectively Using Compilation Options
Compile on Demand
Importing and Exporting Code Modules
Working with Project Properties
Examples of Null, the DoCmd Object, and Intrinsic Constants
An Example of Using a Type Structure
Summary
13. Exploiting the Power of Class Modules
Exploring the Benefits of Class Modules
Object Orientation—An Introduction
Creating and Using a Class Module
Adding Properties
Adding Methods
Instantiating and Using the Class
Property Let and Get—Adding Properties the Right Way
Setting Values with Property Set
Creating Multiple Class Instances
The Initialize and Terminate Events
Initialize
Terminate
Working with Enumerated Types
Building Hierarchies of Classes
Adding a Parent Property to Classes
The Implements Keyword
Working with Custom Collections
Creating a Collection
Adding Items to a Collection
Looping Through the Elements of a Custom Collection
Referencing Items in a Collection
Removing Items from a Collection
Adding Your Own Events
The FileInformation Class
Using a Collection to Manipulate Multiple Instances of the FileInformation Class
Data Access Class
System Information Class
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
The Connection Object
The Recordset Object
The Command Object
Understanding ADO Recordset Types
The CursorType Parameter
The LockType Parameter
The Options Parameter
Consistent Versus Inconsistent Updates
Selecting a Cursor Location
Working with the Supports Method
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
Sorting a Recordset
Filtering a Recordset
Finding a Specific Record in a Recordset
Working with Variables in Strings
Using the AbsolutePosition Property
Using the Bookmark Property
Running Parameter Queries
Refreshing Recordset Data
Working with Persisting Recordsets
Modifying Table Data Using ADO Code
Changing Record Data One Record at a Time
Performing Batch Updates
Making Bulk Changes
Deleting an Existing Record
Adding a New Record
Creating and Modifying Database Objects Using ADO Code
Adding a Table Using Code
Removing a Table Using Code
Establishing Relationships Using Code
Creating a Query Using Code
Examining the Data Access Object Model
Workspaces
Users
Groups
Databases
TableDefs
Indexes
QueryDefs
Fields
Parameters
Recordsets
Relations
Containers
Documents
Properties
Errors
Getting to Know DBEngine
Using CurrentDB()
Understanding DAO Recordset Types
Dynasets
Snapshots
Tables
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
Sorting a Recordset
Filtering a Recordset
Finding a Specific Record Within a Recordset
Using the AbsolutePosition Property
Using the Bookmark Property
Using the RecordsetClone Property
Running Parameter Queries
Modifying Table Data Using DAO Code
Changing Record Data One Record at a Time
Making Bulk Changes
Deleting an Existing Record
Adding a New Record
Using the LastModified Property
Creating and Modifying Database Objects Using DAO Code
Adding a Table Using Code
Removing a Table Using Code
Establishing Relationships Using Code
Creating a Query Using Code
Using the DAO Containers Collection
Using Recordset Methods on a Data-Entry Form
Summary
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
Option Explicit
Strong-Typing
Naming Standards
Variable Scoping
Bugs Happen!
Harnessing the Power of the Immediate Window
Testing Values of Variables and Properties
Setting Values of Variables and Properties
Clearing the Immediate Window
Practicing with the Built-In Functions
Executing Subroutines, Functions, and Methods
Printing to the Immediate Window at Runtime
Invoking the Debugger
Using Breakpoints to Troubleshoot
Stepping Through Code
Using Step Into
Executing Until the Next Breakpoint Is Reached
Using Step Over
Using Step Out
Setting the Next Statement to Execute
Using the Calls Window
Working with the Locals Window
Working with Watch Expressions
Using Auto Data Tips
Using a Quick Watch
Adding a Watch Expression
Editing a Watch Expression
Breaking When an Expression Is True
Breaking When an Expression Changes
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 On Error Goto
Including the Error Number and Description in the Error Handler
Using On Error Goto 0
Using On Error Resume Next
Using Resume Statements
The Resume Statement
The Resume Next Statement
The Resume <LineLabel> Statement
Clearing an Error
Examining the Cascading Error Effect
Using the Err Object
Raising an Error
Generating an Error on Purpose
Creating User-Defined Errors
Using the Errors Collection
Creating a Generic Error Handler
Logging the Error
Determining the Appropriate Response to an Error
Creating an Error Form
Printing the Error Form
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
Hardware, Hardware, More Hardware, Please!
RAM, RAM—That’s All I Need!
Defragment Your User’s Hard Disk
Compact Your Database
Don’t Use Compressed Drives
Tune Virtual Memory: Tweak the Swap File
Run Access and Your Application Locally
Do Everything You Can to Make Windows Itself Faster
Change Access’s Software Settings
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
Why Be Normal?
I Thought You Just Told Me to Normalize
Index, Index, Index!
Select the Correct Data Type
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
Designing Forms
Designing Reports
Summary
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
Features of the MOD
Differences Between the Standard and Runtime Versions of Access
Steps for Preparing an Application for Distribution
Basing Your Application Around Forms
Adding Start-Up Options to Your Database
Securing Your Application
Building Error Handling in to Your Applications
Adding Custom Help
Building Custom Command Bars
The Access Runtime Engine: Summing It Up
Using an EXE Versus Access Database: What It Means to You
Understanding the Importance of Securing Your Database
Using Access as a Front End
Things You Need to Worry About in Converting to Client/Server
Benefits and Costs of Client/Server Technology
Your Options When Using Access as a Front End
What Are the Considerations for Migrating to a Client/Server Environment?
Summary
19. Using External Data
Why This Chapter Is Important
Importing, Linking, and Opening Files: When and Why
Selecting an Option
Looking at Supported File Formats
Importing External Data
Using the User Interface
Using Code
Importing Database Data Using Code
Importing Text Data Using Code
Importing Spreadsheet Data Using Code
Creating a Link to External Data
Using the User Interface
Using the Database Splitter to Create Links
Creating Links to Access Tables
Creating Links to Other Types of Tables
Using Code
Providing Connection Information
Creating the Link
Opening an External Table
Providing Connection Information
Opening the Table
Understanding Windows Registry Settings
Using the Jet OLEDB:Link Provider String
Working with Passwords
Refreshing and Removing Links
Updating Links That Have Moved
Deleting Links
Looking at Special Considerations
dBASE
Text Data
Troubleshooting
Connection Problems
Temp Space
Looking at Performance Considerations and Links
Working with HTML Documents
Splitting the Database Using the Database Splitter
Refreshing Links
Summary
20. Developing Multiuser and Enterprise Applications
Why This Chapter Is Important
Designing Your Application with Multiuser Issues in Mind
Multiuser Design Strategies
Strategies for Installing Access
Strategies for Installing Your Application
The Basics of Linking to External Data
Understanding Access’s Locking Mechanisms
Understanding the Client/Server Model
Deciding Whether to Use the Client/Server Model
Dealing with a Large Volume of Data
Dealing with a Large Number of Concurrent Users
Demanding Faster Performance
Handling Increased Network Traffic
Implementing Backup and Recovery
Focusing on Security
Sharing Data Among Multiple Front-End Tools
Understanding What It All Means
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
Indexes
AutoNumber Fields
Default Values
Validation Rules
Relationships
Security
Table and Field Names
Reserved Words
Case Sensitivity
Properties
Visual Basic Code
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
Sharing Data Among Offices
Sharing Data Among Dispersed Users
Reducing Network Load
Distributing Application Updates
Backing Up the Data in Your Application
Understanding When Replication Isn’t Appropriate
Understanding the Implementation of Replication
The Access User Interface
Briefcase Replication
The Replication Manager
ADO Code
Programs That Support Replication Using DAO
Summary
IV. Black-Belt Programming
21. Using ActiveX Controls
Why This Chapter Is Important
Incorporating ActiveX Controls in Access 2002
Installing an ActiveX Control
Registering an ActiveX Control
Adding ActiveX Controls to Forms
Understanding and Managing the Control Reference in Your Access Application
Setting Properties of an ActiveX Control at Design Time
Coding Events of an ActiveX Control
Using the Calendar Control
Properties of a Calendar Control
Methods of a 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
Adding a Calendar to the Report Criteria Dialog
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
Declaring an Object Variable
Manipulating an Automation Object
Setting and Retrieving Properties
Executing Methods
Early Binding Versus Late Binding
CreateObject and GetObject
Controlling Excel from Access
Closing an Excel Automation Object
Creating a Graph from Access
Controlling Word from Access
Using Word to Generate a Mass Mailing
Using Word to Overcome the Limitations of Access as a Report Writer
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
Passing Parameters to DLL Functions
Passing by Reference Versus Passing by Value
Passing String Parameters
Aliasing a Function
Function Calls and Invalid Characters
DLL Functions with Duplicate Names
Eliminating the “A” Suffix Required by ANSI
Unique Procedure Names in an Access Library or Module
Calling Functions Referenced with Ordinal Numbers
Working with Constants and Types
Working with Constants
Working with Types
Using the Windows API Text Viewer
Loading a Text File
Loading a Database File
Pasting API Declares, Types, and Constants
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
Why This Chapter Is Important
Preparing a Database to Be a Library
Structuring Code Modules for Optimal Performance
Writing Library Code That Runs
Compiling the Library
Creating a Reference
Creating a Library Reference
Creating a Runtime Reference
Calling a Function from a Library at Runtime
Using the LoadOnStartup Key
Creating an Explicit Reference
Creating a Reference Using VBA Code
Debugging a Library Database
Securing an Access Library
Summary
25. Using Builders, Wizards, and Menu Add-Ins
Why This Chapter Is Important
Using Builders
Looking at Design Guidelines
Creating a Builder
Writing a Builder Function
Designing a Builder Form
Registering a Builder
Manually Adding Entries to the Windows Registry
Automating the Creation of Registry Entries
Using Wizards
Looking at Design Guidelines
Creating a Wizard
Getting the Wizard Ready to Go
Using Menu Add-Ins
Looking at Design Guidelines
Creating a Menu Add-In
Summary
26. An Introduction to Access and the Internet/Intranet
Why This Chapter Is Important
Saving Database Objects as HTML
Saving Table Data as HTML
Saving Query Results as HTML
Saving Forms as HTML
Saving Reports as HTML
Linking to HTML Files
Importing HTML Files
Understanding Data Access Pages
Creating Data Access Pages
Creating a Data Access Page Using the AutoPage Feature
Creating a Data Access Page Using a Wizard
Creating a Data Access Page from an Existing Web Page
Creating a Data Access Page from Scratch
Modifying Important Properties of a Data Access Page
The ConnectionFile Property
The ConnectionString Property
The DataEntry Property
The MaxRecords Property
The RecordsetType Property
The UseRemoteProvider Property
Modifying the Record Navigation Control Properties
The RecordSource Property
First, Previous, Next, and Last Buttons
The New Button
The Delete Button
The Save Button
The Undo Button
The Sort Ascending and Sort Descending Buttons
The ShowToggleFilterButton Button
Creating Grouped Data Access Pages
Augmenting Data Access Pages with VBScript
Important Data Access Page Events
VBScript Versus VBA
VBScript Versus JavaScript
Summary
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
Understanding the Workgroup: The System.mdw File
Establishing a Workgroup
Joining a Different 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
Adding Groups
Adding Users
Assigning Users to the Appropriate 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
Passwords
Security and Linked Tables
Ownership
Printing Security
Summary
28. Advanced Security Techniques
Why This Chapter Is Important
Maintaining Groups Using Code
Adding a Group
Removing a Group
Using Code to Maintain Users
Adding Users
Assigning Users to a Group
Removing Users from a Group
Removing Users
Listing All Groups and Users
Listing All Groups
Listing All Users
Listing Users in a Specific Group
Determining Whether a User Is a Member of a Specific Group
Working with Passwords
Assigning Passwords to Users
Listing Users Without Passwords
Ensuring That Users Have Passwords
Assigning and Revoking Permissions to Objects Using Code
Determining Whether a Group Has Permission to an Object
Determining Whether a User Has Permission to an Object
Determining Whether a User Has Implicit Rights to an Object
Setting Permissions to New Objects
Manipulating Database Permissions
Encrypting a Database Using Code
Accomplishing Field-Level Security Using Queries
Prohibiting Users and Groups from Creating Objects
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
Maintaining Users with SQL
Using SQL to Add a User
Using SQL to Add a User to a Group
Using SQL to Remove a User from a Group
Using SQL to Remove a User
Using SQL to Change a Password
Maintaining Groups with SQL
Using SQL to Add a Group
Using SQL to Remove a Group
Using SQL to Assign and Remove Permissions
DAO and Security
Choosing Between ADOX, SQL, and DAO
Summary
29. Documenting Your Application
Why This Chapter Is Important
Preparing Your Application to Be Self-Documenting
Documenting Your Tables
Documenting Your Queries
Documenting Your Forms
Documenting Your Reports
Documenting Your Macros
Documenting Your Modules
Using Database Properties to Document the Overall Database
Using the Database Documenter
Using the Documenter Options
Producing Documentation in Other Formats
Writing Code to Create Your Own Documentation
Summary
30. Maintaining Your Application
Why This Chapter Is Important
Compacting Your Database
Using the User Interface
Using a Shortcut
Compacting Whenever a Database Closes
Using the CompactDatabase Method of the JetEngine Object
Using the CompactRepair Method of the Application Object
Converting an Access Database
Detecting Broken References
Summary
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
Hidden Database Window
Hidden Design Views
Built-In Toolbars Not Supported
Unavailable Menu Items
Disabled Keys
Preparing Your Database for Use with the Access Runtime Version
Creating the Application
Building the Application Around Forms and Menus
Building Error Handling into the Application
Adding Custom Menus and Toolbars
Setting Startup Options
Securing the Application
Distributing Your Application as an MDE
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
Automating the Process of Linking to Tables
Using Replication to Efficiently Distribute Your Application
Summary
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
Search in book...
Toggle Font Controls
Playlists
Add To
Create new playlist
Name your new playlist
Playlist description (optional)
Cancel
Create playlist
Sign In
Email address
Password
Forgot Password?
Create account
Login
or
Continue with Facebook
Continue with Google
Sign Up
Full Name
Email address
Confirm Email Address
Password
Login
Create account
or
Continue with Facebook
Continue with Google
Prev
Previous Chapter
14. What Are ActiveX Data Objects and Data Access Objects, and Why Are They Important?
Next
Next Chapter
15. Debugging: Your Key to Successful Development
Part II. What to Do When Things Don’t Go As Planned
Add Highlight
No Comment
..................Content has been hidden....................
You can't read the all page of ebook, please click
here
login for view all page.
Day Mode
Cloud Mode
Night Mode
Reset