- 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
Copyright © 1997 by Sams Publishing
SECOND EDITION
All rights reserved. No part of this book shall be reproduced, stored in a retrieval system, or transmitted by any means, electronic, mechanical, photocopying, recording, or otherwise, without written permission from the publisher. No patent liability is assumed with respect to the use of the information contained herein. Although every precaution has been taken in the preparation of this book, the publisher and author assume no responsibility for errors or omissions. Neither is any liability assumed for damages resulting from the use of the information contained herein. For information, address Sams Publishing, 201 W. 103rd St., Indianapolis, IN 46290.
Library of Congress Catalog Card Number: 96-72230
2000 99 98 4 3
Interpretation of the printing code: the leftmost number is the year of the book's printing; the rightmost single digit the number of the book's printing. For example, a printing code of 97-1 shows that the first printing of the book occurred in 1997.
Composed in AGaramond, Optima, and MCPdigital by Macmillan Computer Publishing
Printed in the United States of America
Trademarks
All terms mentioned in this book that are known to be trademarks or service marks have been appropriately capitalized. Sams Publishing cannot attest to the accuracy of this information. Use of a term in this book should not be regarded as affecting the validity of any trademark or service mark. Access is a registered trademark of Microsoft Corporation.
Publisher and President: Richard K. SwadleyPublishing Manager: Rosemarie GrahamDirector of Editorial Services: Cindy MorrowAssistant Marketing Managers: Kristina Perry, Rachel WolfeAcquisitions Editor Grace M. BuechleinDevelopment Editor Kristi AsherSoftware Development Specialist John WarrinerProduction Editors Fran Blauw Lisa M. LordIndexer Tom DinseTechnical Reviewers Karen Jaskolka John Nelson Paul Cassel Nick Malik Chris Rothberg David ShankEditorial Coordinator Katie WiseTechnical Edit Coordinator Lorraine SchafferResource Coordinator Deborah FrisbyEditorial Assistants Carol Ackerman Andi Richter Rhonda Tinch-MizeCover Designer Tim AmrheinBook Designer Alyssa YeshCopy Writer Peter FullerProduction Team Supervisors Brad Chinn Charlotte ClappProduction Rick Bond Carol Bowers Brad Lenser Chris Livengood Carl Pierce
I dedicate this book to my husband, Dan; my daughter, Alexis; my parents, Charlotte and Bob; and to my real father, Herman. Dan, you are my partner in life and the wind beneath my wings. Your support of my endeavors is relentless. Alexis, you are the sweet little girl that I always dreamed for. You are everything that I could have ever wanted and so very much more. Mom and Dad, without all that you do to help out with life's chores, the completion of this book would never have been possible.
To my real father, Herman, I credit my ability to soar in such a technical field to you. I hope that I inherited just a small part of your intelligence, wit, and fortitude. I am sorry that you did not live to see this accomplishment. I hope that you can see my work and that you are proud of it. More than anyone else, I dedicate this book to you.
-
No Comment
..................Content has been hidden....................
You can't read the all page of ebook, please click here login for view all page.