Microsoft Access 2003 Development and SQL Server Training

Access as a Development Tool

• Types of Applications You Can Develop in Access
• 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?

What Every Developer Needs to Know About Tables

• Building a New Table
• Selecting the Appropriate Field Type for Your Data
• Working with Field Properties
• The All-Important Primary Key
• Working with the Lookup Feature
• Working with Table Properties
• Using Indexes to Improve Performance
• Access Tables and the Internet

Relationships: Your Key to Data Integrity

• Why Relationships are Important
• Introduction to Relational Database Design
• Examining the Types of Relationships
• Establishing Relationships
• Establishing Referential Integrity
• Looking at the Benefits of Relationships
• Examining Indexes and Relationships
• What Every Developer Needs to Know About Query Basics
• Why Queries are Important
• What is a Query and When Should You Use One?
• Everything You Need to Know About Query Basics
• Ordering Your Query Result
• Refining Your Query with Criteria
• Working with Dates in Criteria
• Understanding How Query Results Can be Updated
• Building Queries Based on Multiple Tables
• Creating Calculated Fields
• Getting Help from the Expression Builder
• Summarizing Data with Totals Queries
• Excluding Fields from the Output
• Nulls and Query Results
• Refining Your Queries with Field, Field List, and Query Properties
• Building Parameter Queries: When You Don't Know the Criteria at Design Time

What Every Developer Needs to Know About Forms

• Uses of Forms
• Anatomy of a Form
• Creating a New Form
• Working with the Form Design Window
• Selecting the Correct Control for the Job
• Control Morphing
• Conditional Formatting
• What Form Properties Are Available and Why Use Them?
• What Control Properties Are Available and Why Use Them?

What Every Developer Needs to Know About Forms - Beyond the Basics

• 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

What Every Developer Needs to Know About Reports

• 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

What Every Developer Needs to Know About Reports - Beyond the Basics

• 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
• Basing Reports on Stored Queries
• Access Reports and the Internet

VBA: AN INTRODUCTION

• VBA Explained
• What Are Access Class Modules, Standard Modules, Form Modules, and Report Modules?
• Working with Variables
• Adding Comments to Your Code
• Using the Line Continuation Character
• Using the VBA Control Structures
• Passing Parameters and Returning Values
• Executing Procedures from the Module Window
• The DoCmd Object: Performing Macro Actions
• Working with Built-In Functions
• Working with Constants
• Tools for Working in the Visual Basic Editor
• Customizing the VBE
• Understanding Access's Object Model

Objects, Properties, Methods, and Events

• Understanding Objects, Properties, Events, and Methods
• Using the Object Browser to Learn About Access's Objects
• Referring to Objects
• Properties and Methods Made Easy
• Declaring and Assigning Object Variables
• Understanding the Differences Between Objects and Collections
• Passing Objects to Subroutines and Functions
• Determining the Type of a Control

Advanced Form Techniques

• What are the Form Events
• What are the Section and Control Events
• What Types of Forms Can I Create
• Using Built-in Dialogs
• Adding Menus, Toolbars, and Shortcut Menus
• Taking Advantage of Filtering Techniques
• Including Objects from Other Applications
• Switching a Form's Record Source
• Power Combo and List Box Techniques
• Power Subform Techniques
• Synchronizing a Form with Its Underlying Recordset
• Creating Custom Properties and Methods

Advanced Report Techniques

• Events Available for Reports and When to Use Them
• Order of Events for Reports
• Events Available for Report Sections and When to Use Them
• Programmatically Manipulating Report Sections
• Special Report Properties
• Controlling the Printer
• Practical Examples of Report Events and Properties

Advanced Query Techniques

• Action Queries
• Special Query Properties
• Optimizing Queries
• Crosstab Queries
• Outer Joins
• Self Joins
• Understanding SQL
• Union Queries
• Pass-through Queries
• The Propagation of Nulls and Query Results
• Subqueries
• Using SQL to Update Data
• Using SQL for Data Definition
• Using the Result of a Function as the Criteria for a Query
• Passing Parameter Query Values from a Form
• Jet 4.0 ANSI-92 Extensions

Advanced VBA Techniques

• What are User-defined Types, and Why Would You Use Them
• Working with Constants
• Working with Arrays
• Advanced Function Techniques

Exploiting the Power of Class Modules

• Exploring the Benefits of Class Modules
• Object Orientation - An Introduction
• Creating and Using a Class Module
• Creating Multiple Class Instances
• The Initialize and Terminate Events
• Working with Enumerated Types
• Building Hierarchies of Classes
• Adding a Parent Property to Classes
• The Implements Keyword
• Working with Custom Collections
• Adding Your Own Events
• Practical Examples: Class Modules
• Summary

What Are ActiveX Data Objects and Data Access Objects, and Why Are They Important?

• Using ActiveX Data Objects Versus Data Access Objects
• Examining the ActiveX Data Object Model
• Understanding ADO Recordset Types
• Working with ADO Recordset Properties and Methods
• Modifying Table Data Using ADO Code
• Creating and Modifying Database Objects Using ADO Code

Debugging: Your Key to Successful Development

• Avoiding Bugs
• Harnessing the Power of the Immediate Window
• Invoking the Debugger
• Using Breakpoints to Troubleshoot
• Stepping Through Code
• Setting the Next Statement to Execute
• Using the Calls Window
• Working with the Locals Window
• Working with Watch Expressions
• Continuing Execution After a Runtime Error
• Looking At Gotchas with the Immediate Window
• Using Assertions
• Debugging Tips

ERROR HANDLING: PREPARING FOR THE INEVITABLE

• Implementing Error Handling
• Using On Error Statements
• Using Resume Statements
• Clearing an Error
• Examining the Cascading Error Effect
• Using the Err Object
• Raising an Error
• Using the Errors Collection
• Creating a Generic Error Handler
• Preventing Your Own Error Handling from Being Invoked
• Creating a Call Stack
• Building a Custom Error Handler Class
• Working with Error Events
• Creating a List of Error Codes and Description

OPTIMIZING YOUR APPLICATION

• Introducing Optimization
• Modifying Hardware and Software Configurations
• What Jet 3.5 Did to Improve Performance
• Letting the Performance Analyzer Determine Problem Areas
• Designing Tables to Optimize Performance
• Designing Queries to Optimize Performance
• Making Coding Changes to Improve Performance
• Designing Forms and Reports to Improve Performance

USING EXTERNAL DATA

• Importing, Linking, and Opening Files: When and Why
• Importing External Data
• Creating a Link to External Data
• Opening an External Table
• Understanding Windows Registry Settings
• Using the Jet OLEDB:Link Provider String
• Working with Passwords
• Refreshing and Removing Links
• Working with SharePoint Team Services
• Looking at Special Considerations
• Troubleshooting
• Looking at Performance Considerations and Links
• Working with HTML Documents

A STRATEGY FOR DEVELOPING ACCESS APPLICATIONS

• Splitting Databases into Tables and Other Objects
• Basing a Form or Report on a Tab, Query, or SQL Statement
• Understanding the Access Runtime Engine
• Using an EXE versus Access Database: What It Means to You
• Understanding the Importance of Securing Your Database
• Using Access as a Front End

DEVELOPING MULTIUSER AND ENTERPRISE APPLICATIONS

• Options Available with MultiUser Applications
• Access's Locking Mechanisms
• Client/Server Applications
• Knowing When to Switch to the Client/Server Model
• Client/Server Terminology

USING ACTIVEX CONTROLS

• Incorporating ActiveX Controls in Access 2002
• Setting Properties of an ActiveX Control at Design Time
• Coding Events of an ActiveX Control
• Using the Calendar Control
• Using the UpDown Control
• Using the StatusBar Control
• Using the Common Dialog Control
• Using the Rich Textbox Control
• Using the TabStrip Control
• Using the ImageList Control
• Licensing and Distribution Issues

AUTOMATION: COMMUNICATING WITH OTHER APPLICATIONS

• Defining Some Automation Terms
• Why This Section Is Important
• Declaring an Object Variable to Reference Your Application
• Creating an Automation Object
• Manipulating an Automation Object
• Early Binding Versus Late Binding
• Controlling Excel from Access
• Closing an Excel Automation Object
• Creating a Graph from Access
• Controlling Word from Access
• Controlling PowerPoint from Access
• Automating Outlook from Access
• Controlling Access from Other Applications

EXPLOITING THE POWER OF THE WINDOWS API

• Declaring an External Function to the Compiler
• Working with Constants and Types
• Calling DLL Functions: Important Issues
• Using API Functions

PUBLISHING DATA ON THE WEB

• Saving Database Objects as HTML
• Linking to HTML files
• Importing HTML Files
• Creating Data Access Pages
• Modifying Data Access Page Properties
• Customizing the Record Navigation Control
• Grouping Records on a Data Access Page
• Creating Scripts Behind the Events of Objects on a Data Access Page

ADVANCED SECURITY TECHNIQUES

• Maintaining Groups Using Code
• Using Code to Maintain Users
• Listing All Groups and Users
• Working with Passwords
• Assigning and Revoking Permissions to Objects Using Code
• Encrypting a Database Using Code
• Accomplishing Field-Level Security Using Queries
• Prohibiting Users and Groups from Creating Objects
• Accomplishing Prohibited Tasks by Logging On as a Different User
• Securing Client/Server Applications
• Security and Replication
• Implementing Security with SQL
• DAO and Security
• Choosing Between ADOX, SQL, and DAO

DATABASE SECURITY MADE EASY

• Implementing Share-Level Security: Establishing a Database Password
• Encrypting a Database
• Establishing User-Level Security
• 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

DOCUMENTING YOUR APPLICATION

• Preparing Your Application to Be Self-Documenting
• Using the Database Documenter
• Object Dependencies
• Writing Code to Create Your Own Documentation

DISTRIBUTING YOUR APPLICATION

• The Packaging Wizard
• Distributing with a Full Copy of Access
• Differences Between the Full and Runtime Versions
• Preparing Your Application for Runtime Distribution
• Additional Issues

MAINTAINING YOUR APPLICATION

• Compacting Your Database
• Backing Up a Database
• Converting an Access Database
• Detecting Broken References

INTRODUCTION TO CLIENT/SERVER DEVELOPMENT TECHNIQUES

• Understanding the Client/Server Model
• Deciding Whether to Use the Client/Server Model
• The Roles Access Plays in the Application Design Model
• Learning the Client/Server Buzzwords
• Upsizing: What to Worry About
• Using the Upsizing Wizard
• Defining an ODBC Data Source
• Connecting to a Database Server

SQL SERVER BASICS

• Editions of SQL Server Available
• Overview of the SQL Server Components
• Introduction to the Enterprise Manager
• Introduction to the Query Analyzer
• Introduction to the Profiler
• Introduction to Data Transformation Services
• Configuring the Server
• Designing and Creating a SQL Server Database

SQL SERVER TABLES AND DATABASE DIAGRAMS

• Creating SQL Server Tables
• Working with Constraints
• Identity Columns
• Creating Computed Columns
• Working with User-defined Data Types
• Adding and Modifying Indexes
• An Introduction to Triggers
• Working with Relationships
• Working with Database Diagrams
• Establishing Referential Integrity
• Looking at the Benefits of Relationships
• Examining Indexes and Relationships

MASTERING T-SQL

• Introduction to T-SQL
• The SELECT Statement
• Aggregate Functions
• The HAVING Clause
• Top Values Queries
• Join Types
• Modifying Data with Action Queries
• T-SQL Functions

WORKING WITH SQL SERVER VIEWS

• Creating a Simple View
• Creating Complex Views
• Using Views to Secure Data
• Modifying Data in a View
• Modifying Views
• Renaming Views
• Deleting Views
• Indexed Views

DESIGNING SQL SERVER STORED PROCEDURES, TRIGGERS, AND USER-DEFINED FUNCTIONS

• Stored Procedure Basics
• Declaring and Working with Variables
• Controlling the Flow
• The SET NOCOUNT Statement
• Using the @@Functions
• Working with Parameters
• Modifying Data with Stored Procedures
• Errors and Error Handling
• Building and Working with User-Defined Functions
• Creating and Working with Triggers

SQL SERVER SECURITY

• Security Introduced
• Authentication
• Creating Roles
• Permissions Validation
• Administering Statement Permissions
• Creating and Working with Applications Roles
• Access Security and Its Role in a Client/Server Application

ADO AND SQL SERVER

• A History of Data Access
• ADO Quick Review
• ADO and Stored Procedures
• Advanced ADO Techniques

DEVELOPING AN MDB CLIENT/SERVER APPLICATION WITH LINKED TABLES

• What is a Linked Client/Server Application?
• How Linked Table Applications Work
• Working with Linked Tables
• Using Pass-Through Queries
• Executing Stored Procedures
• Building Client/Server-Friendly Forms
• Working with Local Tables
• Populating Combo Boxes and List Boxes Dynamically
• Building Client/Server-Friendly Reports
• Linked Table Gotchas

DEVELOPING AN ADP APPLICATION

• What are Access Projects?
• Creating an Access Project
• Maintaining a SQL Server Database from an Access Project
• Building Access Project Forms
• Special Access Project Form Properties and Techniques
• Building Access Data Project Reports
• ADP Gotchas

BUILDING UNBOUND APPLICATIONS

• The Benefits of Unbound Applications
• The Unbound Form
• The Unbound Form and Pass-Through Queries
• The Unbound Form and Stored Procedures

TUNING AND MAINTAINING SQL SERVER

• Hardware Selection and Tuning
• SQL Server Configuration and Tuning
• Database Maintenance
• Table and Object Maintenance
• Job Maintenance
• Performance Monitoring

BUILDING N-TIER APPLICATIONS

• The Basics of N-Tier Development
• Access's Role in the N-Tier Model
• Access and the Logical Three-Tier Model
• Building the Middle-Tier Component
• Access and a Physical Three-Tier Model