Course Overview
Microsoft Access is a powerful visual tool with which to design and develop Windows-based database applications. Windows power users can also use Access to manipulate data, generate reports and act as the front-end for client/server database systems.
This hands-on course provides an in-depth view of the features and functionality of Access. You learn key database design concepts, and work with Access to build a database system in class. You also develop a Microsoft Access front-end that queries a SQL Server database over a Windows network.
Audience
This course is valuable for anyone who plans to prototype, build and integrate database applications using Microsoft Access 2003 or a prior version of Access. Familiarity with Windows is assumed; some knowledge of databases or macros is helpful.!
Skills Gained
- Design robust relational database applications using Microsoft Access 2003
- Develop client/server database front-ends
- Build database applications quickly using Form, Table, Report and Query wizards
- Link to ODBC and OLE-DB data sources to leverage enterprise security
- Create and integrate macros into your applications
- Implement advanced Access reporting features
Course Outline
Introduction to Access
Overview of database concepts
- Databases and their contents
- Flat file and relational databases
The fundamentals of database design
- Tables, records and fields
- Ordering schemes
- Indexes and keys
Introduction to the Access product
- The visual development environment
- When to use Access
Creating a Relational Database Management System with Access
Defining the requirements
- Eliciting user needs and wants
- Designing the data model
Building an Access database system
- Access tables, fields, indexes
- Table relationships
- Table Wizards
Adding the data
- Working with the datasheet view
- Attaching or importing existing tables
- Designing a friendly user interface
- Forms design
- Form wizards
- Controls and control properties
Querying the Database
Query types and elements
- What is a Query?
- Query by Example (QBE)
- Query by Form (QBF)
- Select queries
- Crosstab queries
- Action queries
- Parameter queries
Managing queries
- Query wizards
- Expressions in queries
Access as a Client/Server Front-end
Attaching to a SQL Server database
- The ODBC OLE-DB connectivity standards
- Multiuser and security concerns
SQL queries across the network
- Using Query wizards with SQL
- Issuing commands to SQL databases using pass-through queries
Access Reports
Using report wizards
- Quick printouts with AutoReports
- Tabular, groups/totals, mailing labels and other standard report types
- Visually enhancing your presentation with wizards
- Exploiting formulas and expressions in reports
- Designing queries for effective report grouping
Modifying the standard report formats
- Elements of effective custom reports
- Adjusting report controls
Application Development with Macros and VBA
Designing effective macros
- Point and click
- Debugging tips
- Macro style guidelines
- Running a macro from events
- Converting macros to VBA code
Event-driven programming
- Writing event-driven programs
- Associating macros with controls to respond to user-triggered events
- Programming conditional macros to respond to user input
Automating your application with VBA
- Correlating data types with Access table definitions
- Controlling form values with variables
Advanced Access Techniques
Techniques for forms
- Validating user data with field masks, field-level validation and advanced logic control
- Populating list and combo boxes with queries
- Hiding columns in list and combo boxes
Macro techniques
- Grouping macros for organisation
- Creating keyboard shortcuts with AutoKeys
- Converting Access macros to VBA code
