V. Putting the Final Polish on Your Application
by Alison Balter
Alison Balter's Mastering Access 97 Development, Second Edition
- Copyright
- What's New in This Edition
- Acknowledgments
- About the Author
- Introduction
- I. Building a Foundation for Your Access Applications
- 1. Introduction to Access Development
- 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 Scalable Product
- What Exactly Is a Database?
- Getting to Know the Database Objects
- Object Naming Conventions
- Hardware Requirements
- How Do I Get Started Developing an Access Application?
- Summary
- What Types of Applications Can You Develop in Access?
- 2. A Strategy for Developing Access Applications
- Why Strategize?
- Splitting Tables and Other Objects
- Basing Forms and Reports on Queries
- Understanding the Access Runtime Engine
- EXE Versus Access Database: What It Means to You
- The Importance of Securing Your Database
- Using Access as a Front-End
- Applying the Strategy to the Computer Consulting Firm Application
- Summary
- 3. What Every Developer Needs to Know About Tables
- 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: 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: Make the User Enter a Value
- Allow Zero Length: Accommodate Situations with Nonexistent Data
- Indexed: Speeding Up Searches
- The All-Important Primary Key
- Working with the Lookup Feature
- Working with Table Properties
- Using Indexes to Improve Performance
- Access Tables and the Internet
- Practical Examples: Designing the Tables Needed for the Computer Consulting Firm's Time and Billing Application
- Summary
- 4. Relationships: Your Key to Data Integrity
- Understanding Relationships
- Examining the Types of Relationships
- Establishing Relationships
- Establishing Referential Integrity
- Looking At the Benefits of Relationships
- Examining Indexes and Relationships
- Practical Examples: Establishing the Relationships Between the Tables Included in the Time and Billing Database
- Summary
- 5. What Every Developer Needs to Know About Query Basics
- What Is a Query and When Should You Use One?
- Everything You Need to Know About Selecting Fields
- 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 and Query Properties
- Field List Properties
- Building Parameter Queries When You Don't Know the Criteria at Design Time
- Practical Examples: Building Queries Needed by the Time and Billing Application for the Computer Consulting Firm
- Summary
- 6. What Every Developer Needs to Know About Form Basics
- 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
- What Form Properties Are Available and Why 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
- Practical Examples: Designing Forms for Your Application
- Summary
- 7. What Every Developer Needs to Know About Report Basics
- 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
- Access Reports and the Internet
- Practical Examples: Building Reports Needed for Your Application
- Summary
- 8. VBA 101: The Basics of VBA
- VBA Explained
- Access Class Modules, Standard Modules, Form Modules, and Report Modules Explained
- Working with Variables
- Adding Comments to Your Code
- Using the Line-Continuation Character
- Using the VBA Control Structures
- Passing Parameters and Returning Values: An Introduction
- Working with Built-In Functions
- Tools for Working with the Module Window
- Practical Examples: Event Routines, User-Defined Functions, and Subroutines Needed for the Time and Billing Application
- Summary
- 9. Advanced VBA Techniques
- Navigation Tips and Tricks
- Executing Procedures from the Module Window
- The DoCmd Object: Performing Macro Actions
- What Are User-Defined Types and Why Would You Use Them?
- Working with Constants
- Working with Arrays
- Advanced Function Techniques
- Property Let and Get: Working with Custom Properties
- Class Modules
- Working with Empty and Null
- Understanding and Effectively Using Compilation Options
- Customizing the IDE
- Practical Examples: Putting Advanced Techniques to Use in the Time and Billing Application
- Summary
- 10. The Real Scoop on Objects, Properties, and Events
- Understanding Access's Object Model
- The Forms Collection
- 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
- Using the New Keyword
- Passing Objects to Functions and Subroutines
- Returning to a Unique Item in a Collection
- Determining the Type of a Control
- Special Properties That Refer to Objects
- Practical Examples of Working with Objects
- Summary
- 11. What Are Macros and When Do You Need Them?
- Why Learning About Macros Is Important
- The Basics of Creating and Running a Macro
- Running an Access Macro
- Modifying an Existing Macro
- Documenting Your Macro: Adding Comments
- Testing a Macro
- When You Should Use Macros and When You Shouldn't
- Converting a Macro to VBA Code
- Creating an AutoExec Macro
- Creating an AutoKeys Macro
- The DoCmd Object
- Practical Examples: Adding an AutoExec Macro to the Time and Billing Application
- Summary
- 12. Advanced Query Concepts
- 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 the Result of a Function as the Criteria for a Query
- Passing Parameter Query Values from a Form
- Practical Examples: Applying These Techniques in Your Application
- Summary
- 13. Let's Get More Intimate with Forms: Advanced Techniques
- What Are the Form Events and When Do You Use Them?
- Current
- BeforeInsert
- BeforeUpdate
- AfterUpdate
- AfterInsert
- 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 Record Source
- Power Combo Box and List Box Techniques
- Power Subform Techniques
- Synchronizing a Form with Its Underlying Recordset
- Creating Custom Properties and Methods
- Practical Examples: Applying Advanced Techniques to Your Application
- Summary
- What Are the Form Events and When Do You Use Them?
- 14. Let's Get More Intimate with Reports: Advanced Techniques
- 15. What Are the Data Access Objects and Why Are They Important?
- Understanding Data Access Objects
- Examining the Data Access Object Model
- Getting to Know DBEngine
- Using CurrentDB()
- Understanding Recordset Types
- Selecting Among the Types of Recordset Objects Available
- Working with 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 Code
- Creating and Modifying Database Objects Using Code
- Using the Containers Collection
- Practical Examples: Applying These Techniques to Your Application
- Summary
- 1. Introduction to Access Development
- II. What To Do When Things Don't Go As Planned
- 16. Debugging: Your Key to Successful Development
- Understanding the Importance of Debugging
- Avoiding Bugs
- Harnessing the Power of the Debug 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 Pane
- Working with Watch Expressions
- Continuing Execution After a Runtime Error
- Looking At Gotchas with the Debug Window
- Practical Examples: Debugging Real Applications
- Summary
- 17. Handling Those Dreaded Runtime Errors
- Implementing Error Handling
- Working with Error Events
- 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
- Practical Examples: Incorporating Error Handling
- Summary
- 16. Debugging: Your Key to Successful Development
- III. Preparing Your Applications for a Multiuser Environment
- 18. Developing for a Multiuser Environment
- Designing Your Application with Multiuser Issues in Mind
- Understanding Access's Locking Mechanisms
- Locking and Refreshing Strategies
- Form Locking Strategies
- Recordset Locking
- Effectively Handling Locking Conflicts
- Testing a Record for Locking Status
- Using Code to Refresh or Requery
- Understanding the .LDB File
- Creating Custom Counters
- Using Unbound Forms
- Using Replication to Improve Performance
- Practical Examples: Making an Application Multiuser Ready
- Summary
- 19. Using External Data
- Understanding External Data
- Importing, Linking, and Opening: When and Why
- Importing External Data
- Creating a Link to External Data
- Opening an External Table
- Understanding Windows Registry Settings
- Using the Connection String
- Working with Passwords
- Refreshing and Removing Links
- Creating an External Table
- Looking At Special Considerations
- Troubleshooting
- Looking At Performance Considerations and Links
- Working with HTML Documents
- Practical Examples: Working with External Data from within Your Application
- Summary
- 20. Client/Server Techniques
- Understanding the Client/Server Model
- Deciding Whether to Use the Client/Server Model
- Roles Access Plays in the Application Design Model
- Learning the Client/Server Buzzwords
- Upsizing: What to Worry About
- Proactively Preparing for Upsizing
- Defining an ODBC Data Source
- Connecting to a Database Server
- Working with Linked Tables
- Summary
- 21. Client/Server Strategies
- Developing Client/Server Strategies
- Selecting the Best Recordset Type
- Using Forward-Scrolling Snapshots
- Using Key Set Fetching
- Using Pass-Through Queries and Stored Procedures
- Using ODBCDirect
- Preconnecting to the Server
- Reducing the Number of Connections
- Optimizing Data Handling
- Optimizing Queries and Forms
- Practical Examples: Using Client/Server Strategies
- Summary
- 22. Transaction Processing
- Understanding Transaction Processing
- Understanding the Benefits
- Modifying the Default Behavior
- Implementing Explicit Transaction Processing
- Looking At Transaction Processing Issues
- Using Transaction Processing in a Multiuser Environment
- Using Transaction Processing in a Client/Server Environment
- Practical Examples: Improving the Integrity of the Time and Billing Application Using Transaction Processing
- Summary
- 23. Optimizing Your Application
- Understanding Optimization
- Modifying Hardware and Software Configurations
- Understanding What Jet 3.5 Does to Improve Performance
- Letting the Performance Analyzer Determine Problem Areas
- Designing Tables to Optimize Performance
- Designing Queries to Optimize Performance
- Changing Code 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 Length Function
- Use True and False Instead of Zero
- 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
- Use the Me Keyword
- Use String Functions When Possible
- Use Dynamic Arrays
- Use Constants When Possible
- 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
- Practical Examples: Improving the Performance of the Time and Billing Application
- Summary
- 24. Replication Made Easy
- What Is Replication?
- Uses of Replication
- Understanding When Replication Isn't Appropriate
- An Overview of the Implementation of Replication
- The Replication Architecture: What Makes Replication Tick?
- Understanding Replication Topologies
- Changes That Replication Makes to Your Database
- Making a Database Replicable
- Preventing Objects from Being Replicated
- Creating Additional Replicas
- Synchronizing Replicas
- Resolving Replication Conflicts
- Using the Replication Manager
- Implementing Replication by Using Code
- Practical Examples: Managing the Time and Billing Application with Replication
- Summary
- 18. Developing for a Multiuser Environment
- IV. Extending the Power of Access
- 25. Automation: Communicating with Other Applications
- Understanding Automation
- Defining Some Automation Terms
- Declaring an Object Variable to Reference Your Application
- Using CreateObject and GetObject
- Manipulating an Automation Object
- Controlling Excel from Access
- Closing an Automation Object
- Creating a Graph from Access
- Controlling Word from Access
- Controlling PowerPoint from Access
- Controlling Access from Other Applications
- Practical Examples: Using Automation to Extend the Functionality of the Time and Billing Application
- Summary
- 26. Using ActiveX Controls
- ActiveX Controls Explained
- Incorporating ActiveX Controls in Access 97
- Adding ActiveX Controls to Forms
- Setting Properties of an ActiveX Control at Design Time
- Coding Events of an ActiveX Control
- The Calendar Control
- The UpDown Object
- The StatusBar Control
- The Common Dialog Control
- The Rich Textbox Control
- The TabStrip Control
- The ImageList Control
- Licensing and Distribution Issues
- Practical Examples: Implementing ActiveX Controls
- Summary
- 27. Access and the Internet
- What's New with Access and the Internet
- Saving Database Objects as HTML
- Linking to HTML Files
- Importing HTML Files
- Static Versus Dynamic HTML Formats
- The Publish to the Web Wizard
- Working with HTML Templates
- Sending Data to an FTP or HTTP Server
- Taking Advantage of Hyperlinks
- The Microsoft WebBrowser Control
- The Web Toolbar
- Replication Over the Internet
- Summary
- 28. Managing Application Development with Visual SourceSafe
- What Is Visual SourceSafe?
- How Do I Install Visual SourceSafe?
- Using Visual SourceSafe: An Overview
- The Logistics of Managing a Project with Visual SourceSafe
- Leveraging the Power of Visual SourceSafe
- Changes Visual SourceSafe Makes to Access's Behavior
- Understanding the Limitations of Visual SourceSafe
- Practical Examples: Putting the Time and Billing Application Under SourceSafe Control
- Summary
- 29. Leveraging Your Application: Creating Your Own Libraries
- 30. Using Builders, Wizards, and Menu Add-Ins
- 25. Automation: Communicating with Other Applications
- V. Putting the Final Polish on Your Application
- 31. Using External Functions: The Windows API
- Using the Win32 API
- 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
- Getting Information about the Operating Environment
- Determining Drive Types and Available Drive Space
- Practical Examples: Applying What You Have Learned to the Time and Billing Application
- Summary
- 32. Database Security Made Easy
- Reviewing Your Options for Securing a Database
- Implementing Share-Level Security: Establishing a Database Password
- Encrypting a Database
- Establishing User-Level Security
- Step 1: Creating a Workgroup
- Step 2: Creating an Administrative User
- Step 3: Making the Administrative User a Member of the Admins Group
- Step 4: Changing the Password for the Admin User
- Step 5: Removing the Admin User from the Admins Group
- Step 6: Exiting Access and Logging On as the System Administrator
- 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
- Providing an Additional Level of Security: Creating an MDE File
- Looking At Special Issues
- Practical Examples: Securing the Time and Billing Application
- Summary
- 33. Complex Security Issues
- Controlling Security Via Code
- Maintaining Groups By Using Code
- Maintaining Users By Using Code
- Listing All Groups and Users
- Working with Passwords
- Assigning and Revoking Permissions to Objects By Using Code
- Encrypting a Database By Using Code
- Accomplishing Field-Level Security By Using Queries
- Prohibiting Users from Creating Objects
- Accomplishing Prohibited Tasks By Logging on as a Different User
- Securing Client/Server Applications
- Examining Security and Replication
- Practical Examples: Applying Advanced Techniques to Your Application
- Summary
- 34. Documenting Your System
- Understanding Why You Should Document
- Preparing Your Application to Be Self-Documenting
- Using Database Properties to Document the Overall Database
- Using the Database Documenter
- Writing Code to Create Your Own Documentation
- Practical Examples: Applying What You Learned to the Time and Billing Application
- Summary
- 35. Database Maintenance Techniques
- 36. Developing a Help File
- Deciding To Create a Help File
- Looking At Help from a User's Perspective
- Planning the Help File
- Building the Help Components
- Adding Custom Help to Your Applications
- Getting Help with Help: Authoring Tools
- Practical Examples: Adding Help to the Time and Billing Application
- Summary
- 37. Distributing Your Application with ODE
- Distributing Your Application: An Introduction
- Looking At the ODE
- 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
- 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 Setup Wizard
- Viewing the Introductory Screen
- Adding Files to be Included on the Distribution Disks
- Adding Components
- Defining Shortcuts
- Adding Registry Values
- Designating Access Components
- Reordering Components and Setting Their Properties
- Setting Key Attributes of the Application
- Specifying That a Program Execute After Setup is Completed
- Specifying a Location for the Setup Files
- Packaging and Distributing the Application
- Looking At Other Issues
- Practical Examples: Distributing the Time and Billing Application
- Summary
- 31. Using External Functions: The Windows API
- VI. Appendixes
- A. Table Structures
- The tblClients 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 tblTimeCardExpenses 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.