Home Page Icon
Home Page
Table of Contents for
IV. Extending the Power of Access
Close
IV. Extending the Power of Access
by Alison Balter
Alison Balter's Mastering Access 97 Development, Second Edition
Copyright
Dedication
What's New in This Edition
Acknowledgments
About the Author
Tell Us What You Think!
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
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
Macros: A Means of Automating Your System
Modules: The Foundation to the Application Development Process
Object Naming Conventions
Hardware Requirements
What Hardware Does Your 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
A Practical Example of Application Design: A Computer Consulting Firm
Summary
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
Features of the ODE
Differences Between the Standard and Runtime Versions of Access
Preparing an Application for Distribution
The Access Runtime Engine: Summing It Up
EXE Versus Access Database: What It Means to You
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 All This Means to You Right Now
Applying the Strategy to the Computer Consulting Firm Application
Summary
3. What Every Developer Needs to Know About Tables
Building a New Table
Building a Table with a Wizard
Designing a Table from Scratch
Building a Table from a Datasheet
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
The Hyperlink Field Type
Saving Table Data as HTML
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
One-to-Many
One-to-One
Many-to-Many
Establishing Relationships
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
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
Removing a Field from the Query Grid
Inserting a Field After the Query Is Built
Moving a Field to a Different Location on the Query Grid
Saving 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 and Query Properties
Field Properties: Changing the Behavior of a Field
Field List Properties
Query Properties Changing the Behavior of the Overall Query
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
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 with 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
Checkboxes
Option Buttons
Toggle Buttons
Option Groups
Control Morphing
Text Box to Combo Box
Combo Box to List Box
What Form Properties Are Available and Why 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 Microsoft IIS 1-2
Practical Examples: Designing Forms for Your Application
Designing the Clients Form
Designing the Time Cards Form
Designing the Payments Form
Designing the Projects Form
What's Ahead
Summary
7. What Every Developer Needs to Know About Report Basics
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
Access Reports and the Internet
Adding a Hyperlink to a Report
Saving a Report as HTML
Practical Examples: Building Reports Needed for Your Application
Designing the rptClientListing Report
Designing the rptTimeSheet Report
Summary
8. VBA 101: The Basics of VBA
VBA Explained
Access Class Modules, Standard Modules, Form Modules, and Report Modules Explained
Anatomy of a Module
Option Explicit
Event Procedures Made Easy
Creating Functions and Subroutines
Calling Event and User-Defined Procedures
Scope and Lifetime of Procedures
Public Procedures
Private Procedures
Scope Precedence
Static Procedures
Naming Conventions for 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: An Introduction
Working with Built-In Functions
Format
Instr
Left
Right
Mid
UCase
DatePart
DateDiff
DateAdd
Functions Made Easy with the Object Browser
Tools for Working with the Module Window
List Properties and Methods
List Constants
Quick Info
Parameter Information
Complete Word
Definition
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
Mysteries of the Coding Environment Solved
Zoom Shift+F2
Find and Replace
Help
Splitting the Code Window
Full Module View
Using Bookmarks to Save Your Place
Executing Procedures from the Module Window
The DoCmd Object: Performing Macro Actions
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
Advanced Function Techniques
Passing by Reference Versus Passing by Value
Optional Parameters: Building Flexibility into Functions
Named Parameters: Eliminate the Need to Count Commas
Property Let and Get: Working with Custom Properties
Property Let
Property Get
Class Modules
Working with Empty and Null
Working with Empty
Working with Null
Understanding and Effectively Using Compilation Options
Compile On Demand
Compile Loaded Modules
Compile All Modules
Customizing the IDE
Code Color, Fonts, and Sizes
Coding Options
Practical Examples: Putting Advanced Techniques to Use in the Time and Billing Application
Examples of Null, the DoCmd Object, and Intrinsic Constants
An Example of Using a Type Structure
Summary
10. The Real Scoop on Objects, Properties, and Events
Understanding Access's Object Model
The Application Object
The Forms Collection
The Reports Collection
The Modules Collection
The Screen Object
The DoCmd Object
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...NEXT: Performing the Same Command on Multiple Objects
Collections Versus Containers and Documents
Creating Custom Collections
Defining Custom Collections
Adding Items to and Removing Items from a Custom Collection
Looping Through the Elements of a Custom Collection
Referring to a Specific Item in a Collection
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
Bringing Up Multiple Instances of the Projects Form
Enabling and Disabling Command Buttons
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
Macro Actions
Macro Action Arguments
Macro Names
Macro Conditions
Running an Access Macro
Running a Macro from the Macro Design Window
Running a Macro from the Macros Tab
Triggering a Macro from a Form or Report Event
Modifying an Existing Macro
Inserting New Macro Actions
Deleting Macro Actions
Moving Macro Actions
Copying Macro Actions
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
Update Queries
Delete Queries
Append Queries
Make Table Queries
Special Notes About Action Queries
Action Queries Versus Processing Records with Code
Special Query Properties
Unique Values
Unique Records
Top Values
Optimizing Queries
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
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 Clause
The FROM Clause
The WHERE Clause
The ORDER BY Clause
The JOIN Clause
ALL, DISTINCTROW, and DISTINCT Clauses
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
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
Archive Payments
Show All Payments
Create State Table
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 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 Tab Pages
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 Statement
The MsgBox Function
Input Boxes
Common Dialog Boxes
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 Record Source
Power Combo Box and List Box Techniques
Handling the NotInList Event
Populating a Combo Box 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
Practical Examples: Applying Advanced Techniques to Your Application
Building an AutoExec Routine to Launch the Application
Building a Splash Screen
Summary
14. Let's Get More Intimate with Reports: Advanced Techniques
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
Special Report Properties
MoveLayout
NextRecord
PrintSection
Interaction of MoveLayout, NextRecord, and PrintSection
FormatCount
PrintCount
HasContinued
WillContinue
Practical Applications of Report Events and Properties
Changing a Report's RecordSource
Using the Same Report to Display Summary, Detail, or Both
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
Practical Examples
Summary
15. What Are the Data Access Objects and Why Are They Important?
Understanding Data Access Objects
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 Recordset Types
Dynasets
Snapshots
Tables
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
Sorting a Recordset
Filtering a Recordset
Finding a Specific Record in a Recordset
Using the AbsolutePosition Property
Using the Bookmark Property
Using the RecordsetClone Property
Running Parameter Queries
Modifying Table Data Using 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 Code
Adding a Table Using Code
Removing a Table Using Code
Establishing Relationships Using Code
Creating a Query Using Code
Using the Containers Collection
Practical Examples: Applying These Techniques to Your Application
Creating a Report Selection Form
Using Recordset Methods on a Data-Entry Form
Summary
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
Option Explicit
Strong-Typing
Naming Standards
Variable Scoping
Bugs Happen!
Harnessing the Power of the Debug Window
Testing Values of Variables and Properties
Setting Values of Variables and Properties
Clearing the Debug Window
Practicing with the Built-In Functions
Executing Subroutines, Functions, and Methods
Printing to the Debug 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 Pane
Working with Watch Expressions
Using Auto Data Tips
Using 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 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 On Error Goto
Including the Error Number and Description in the Error Handler
Using On Error Resume Next
Using On Error Goto 0
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
Practical Examples: Incorporating Error Handling
Summary
III. Preparing Your Applications for a Multiuser Environment
18. Developing for a Multiuser Environment
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
Locking and Refreshing Strategies
Default Record Locking
Determining the Locking Mechanism for a Query
Determining the Locking Mechanism for a Form or Report
Default Open Mode
Number of Update Retries
ODBC Refresh Interval
Refresh Interval
Update Retry Interval
Refreshing Versus Requerying Data
Form Locking Strategies
No Locks
All Records
Edited Record
Recordset Locking
Pessimistic Locking
Optimistic Locking
Effectively Handling Locking Conflicts
Errors with Pessimistic Locking
Coding Around Pessimistic Locking Conflicts
Errors with Optimistic Locking or New Records
Coding Around Optimistic Locking Conflicts
Testing to See Who Has a Record Locked
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
Selecting an Option
Looking At Supported File Formats
Importing External Data
Importing External Data via the User Interface
Importing External Data Using Code
Importing Database Data Using Code
Importing Text Data Using Code
Importing Spreadsheet Data Using Code
Creating a Link to External Data
Creating a Link Using the User Interface
Using the Database Splitter to Create Links
Creating Links to Access Tables
Creating Links to Other Types of Tables
Creating a Link Using Code
Providing Connection Information
Creating the Link
Opening an External Table
Providing Connection Information
Opening the Table
Understanding Windows Registry Settings
Using the Connection String
Working with Passwords
Refreshing and Removing Links
Updating Links That Have Moved
Deleting Links
Creating an External Table
Looking At Special Considerations
dBASE
FoxPro
Text Data
Troubleshooting
Connection Problems
Temp Space
Looking At Performance Considerations and Links
Working with HTML Documents
Practical Examples: Working with External Data from within Your Application
Splitting the Database By Using the Database Splitter
Refreshing Links
Summary
20. Client/Server Techniques
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
Roles Access Plays in the Application Design Model
The Front-End and Back-End
The Front-End Using Links to Communicate to a Back-End
The Front-End Using SQL Pass-Through to Communicate to a Back-End
The Front-End Using ODBCDirect to Communicate 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
Defining an ODBC Data Source
Connecting to a Database Server
Working with Linked Tables
Linking to External Tables via the User Interface
Linking to External Tables via Code
Linking to Views Rather Than Tables
Using Pass-Through Queries
Creating a Pass-Through Query with the User Interface
Executing a Pass-Through Query Using Code
Executing a Stored Procedure
Opening a Server Table Directly
Using ODBCDirect to Access Client/Server Data
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
Optimizing Queries
Optimizing 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
Realizing That Transactions Occur in a Workspace
Making Sure the Data Source Supports Transactions
Nesting Transactions
Neglecting to Explicitly Commit Transactions
Checking Available Memory
Using Forms with Transactions
Using Transaction Processing in a Multiuser Environment
Using Transaction Processing in a Client/Server Environment
Implicit Transactions
Explicit Transactions
Nested Transactions
Lock Limits
Negative Interactions with Server-Specific Transaction Commands
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
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
Understanding What Jet 3.5 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
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
Designing Forms
Designing Reports
Practical Examples: Improving the Performance of the Time and Billing Application
Summary
24. Replication Made Easy
What Is 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
An Overview of the Implementation of Replication
The Access User Interface
Briefcase Replication
The Replication Manager
DAO Code
Programs That Support Replication Using DAO
The Replication Architecture: What Makes Replication Tick?
The Tracking Layer
The Microsoft Replication Manager
The Synchronizer
File System Transport
The Briefcase Reconciler
Registry Entries
Understanding Replication Topologies
Star Topology
Ring Topology
Fully Connected Topology
Linear Topology
Hybrid Topology
Changes That Replication Makes to Your Database
Fields Added to Each Replicated Table
System Tables Added to the Database
Properties Added to the Database Objects
Changes to Sequential AutoNumber Fields
Changes to the Size of the Database
Making a Database Replicable
Rendering a Database Replicable with the Access User Interface
Rendering a Database Replicable with the Windows 95 Briefcase
Preventing Objects from Being Replicated
Creating Additional Replicas
Creating Additional Replicas with the Access User Interface
Creating Additional Replicas with the Windows 95 Briefcase
Synchronizing Replicas
Synchronizing Databases with the Access User Interface
Synchronizing Databases with the Windows 95 Briefcase
Resolving Replication Conflicts
Using the Replication Manager
Running the Replication Manager for the First Time
Replicating a Database with the Replication Manager
Creating Replicas with the Replication Manager
Partial Replication
Synchronizing Replicas with the Replication Manager
Remote Synchronizations
Scheduled Synchronizations
Reviewing the Synchronization History
Working with Synchronization Properties
Implementing Replication by Using Code
Making a Database Replicable by Using Code
Flagging an Object as Local
Creating a Replica by Using Code
Creating a Partial Replica Using Code
Synchronizing a Database by Using Code
Handling Conflicts by Using Code
Practical Examples: Managing the Time and Billing Application with Replication
Summary
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
CreateObject
GetObject
Manipulating an Automation Object
Setting and Retrieving Properties
Executing Methods
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
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
The Calendar Control
Properties of a Calendar Control
Methods of a 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
Adding a Calendar to the Report Criteria Dialog
Summary
27. Access and the Internet
What's New with Access and the Internet
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
Static Versus Dynamic HTML Formats
The Publish to the Web Wizard
Working with HTML Templates
Sending Data to an FTP or HTTP Server
Importing or Linking to Data on FTP and HTTP Servers
Exporting an Object to an FTP Server
Taking Advantage of Hyperlinks
Storing Hyperlinks in Tables
Placing Hyperlinks on Forms and Reports
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
Adding a Database to Visual SourceSafe
Understanding the Objects Placed Under SourceSafe Control
Creating a Database from a SourceSafe Project
Checking in and Checking out Database Objects
Getting the Latest Version
Adding Objects to Visual SourceSafe
Refreshing an Object's Status
Leveraging the Power of Visual SourceSafe
Showing Differences Between Modules
Showing an Object's History
Reverting to an Object's Previous Version
Changes Visual SourceSafe Makes to Access's Behavior
The Compact Command
Opening a Database
Closing a Database
Opening an Object in Design View
Saving a New Object or Using Save As on an Existing Object
Renaming an Object
Deleting an Object
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
Understanding Library Databases
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
Using the LoadOnStartup Key
Creating an Explicit Reference
Creating a Reference Using VBA Code
Debugging a Library Database
Securing an Access Library
Practical Examples: Building a Library for Your Application
Summary
30. Using Builders, Wizards, and Menu Add-Ins
Defining Builders, Wizards, and Menu Add-Ins
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
Practical Examples: Designing Your Own Add-Ins
Summary
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
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 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
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
Understanding the Workgroup: The System.mdw File
Establishing a Workgroup
Joining a Different 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
Adding Groups
Adding Users
Assigning Users to the Appropriate Groups
Step 11: Assigning Rights to Users and Groups
Providing an Additional Level of Security: Creating an MDE File
Looking At Special Issues
Passwords
Security and Linked Tables
Ownership
Printing Security
Practical Examples: Securing the Time and Billing Application
Summary
33. Complex Security Issues
Controlling Security Via Code
Maintaining Groups By Using Code
Adding a Group
Removing a Group
Maintaining Users By Using Code
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
Working with Passwords
Assigning Passwords to Users
Listing Users without Passwords
Ensuring That Users Have 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
Prohibiting Users from Creating Databases
Prohibiting Users from Creating Other 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
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
Practical Examples: Applying What You Learned to the Time and Billing Application
Summary
35. Database Maintenance Techniques
Understanding What Database Maintenance Is All About
Compacting Your Database
Using the User Interface
Using a Shortcut
Using Code
Repairing Your Database
Using the User Interface
Using a Shortcut
Using Code
Summary
36. Developing a Help File
Deciding To Create a Help File
Looking At Help from a User's Perspective
The Contents Tab
The Index Tab
The Find Tab
The Office Assistant
Button Bars
Hotspots
Hypergraphics
Authorable Buttons
Planning the Help File
Building the Help Components
Creating a Topic File
Creating an RTF File
Adding a Help Topic
Creating Topic IDs: The # Footnote
Creating Titles: The $ Footnote
Specifying Search Keywords: The K Footnote
Using Other Tags
Giving It a Test Run: Building Some Help Topics
Adding Nonscrolling Regions
Adding Hotspots
Adding Graphics
Adding Hypergraphics
Creating a Map File
Creating a Contents File
Creating the Help Project File
Preparing the Help Project to be Compiled
Specifying the Help Topic File
Specifying the Map File
Locating Graphics Files
Adding and Customizing Help Windows
Using Other Options
Compiling the Project
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
Royalty-Free Runtime License
Setup Wizard
Replication Manager
Two-Volume Language Reference
32-Bit ActiveX Controls
Win32 API Text Viewer
Help Workshop for Windows 95
Microsoft Access Developer Sample CD-ROM
Microsoft Graph Runtime Executable
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
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 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
Automating the Process of Linking to Tables
Using Replication to Efficiently Distribute Your Application
Practical Examples: Distributing the Time and Billing Application
Summary
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
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
24. Replication Made Easy
Next
Next Chapter
25. Automation: Communicating with Other Applications
Part IV. Extending the Power of Access
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