PO Box 634, Rivonia 2128 Tel/Fax: 012-659-0798 Cell: 083 398 7918 E-mail: pj@mevaprod.co.za

Duration: 2 days Location: On-site or Corlett Drive, Bramley On-site courses available nation-wide

Book II

Microsoft Access

Develop & Program

Versions 2000, XP and 2003

Table of Contents

v Manual Conventions

v Why Create a Database?

v Benchmark, benchmark, benchmark

v Implementation Guidelines

¨ A Bottom-up Approach

¨ The Nature of a Brief

v A Visual Basic Primer

¨ Section Objectives

¨ The Visual Basic Editor (VBE)

¨ Exercise VB1 – setting up the VBE

¨ The Standard VB Toolbar

¨ The Debug Toolbar

¨ Some VB definitions

¨ Declaring Variables

¨ Exercise VB2 – Option Explicit

¨ Visual Basic Data Types

¨ Variable Scope

¨ Variable Life

¨ Constants

¨ The Programming Window

¨ Exercise VB3 – the VB text editor

¨ Phrasing

¨ The Immediate Window

¨ Exercise VB4 – The VB Scratchpad

¨ The Locals Window

¨ Exercise VB5 – view the variables

¨ The Watch Window

¨ Exercise VB6 - watch out

v Programming Access Objects

¨ Section Objectives

v The Microsoft Access Object Model

¨ Visual Basic Reference Libraries

¨ Exercise AO1 – setting up object references

¨ The Object Browser

¨ Exercise AO2 – determining programmable objects

¨ Standard and Class Modules

v Unfinished Business

¨ Exercise AO3 – adding buttons

¨ Exercise AO4 – more on auto-entry

¨ Exercise AO5 – adding new entries to combo boxes

¨ Exercise AO6 – save on close

¨ Finding and Setting Criteria for Reports

¨ Exercise AO7 – set-up a Reports menu

¨ Exercise AO8 – match report to menu

¨ Exercise AO9 – creating a picture button.

¨ Exercise AO10 – programming reports

¨ Exercise AO11 – a shady trick

¨ Exercise AO12 – preventing accidental closure

v The Access User-Level Security Model

¨ Section Objectives

¨ Exercise AS1 – import all objects

¨ Exercise AS2 – create a new security file

¨ Exercise AS2 – change password

¨ Exercise AS3 – assign permissions

¨ Security in a Front-End/Back-End Solution

¨ Exercise AS4 – exposing the user

¨ Exercise AS5 – securing the code

¨ Exercise AS6 – change the icon

v Practical Code from Real-Life Databases

¨ Section Objectives

¨ The Recordset Object

¨ DAO vs. ADO – Comparison

¨ Deciding whether to use DAO or ADO

¨ Exercise PC1 – explore the database

¨ Exercise PC2 – date calculations in code

¨ Exercise PC3 – working with recordsets

¨ Exercise PC3 – communicating with Outlook

¨ Exercise PC4 – picking up totals in a sub-form

¨ Exercise PC5 – getting statistics on users

¨ Exercise PC6 – security at control level

¨ Exercise PC6 – sequential numbers

¨ Exercise PC7 – protecting the system master

v Database Replication

¨ Section Objectives

¨ Two Reasons to Replicate

¨ Exercise RP1 – a front-end example

¨ Exercise RP2 – import a toolbar

¨ Partial Replication

¨ Replicating Tables

¨ Managing Replication

v Automating other Programs

v Section Objectives

¨ Letting Word do the work

¨ Populating fields in Word documents

¨ Moving data to Excel

¨ Auto-charting Excel from Access

¨ Populating Excel's named ranges

¨ More on working with Outlook

v Error Handling

v Section Objectives

v Learning from the user

v Correcting mistakes

v Validating data

Scope

The manual assumes that the delegate has a sound understanding of Microsoft Access, specifically, table normalisation rules, field data types and the organisation of tables as related entities. In addition, the creation of complex queries, forms, reports and pages should be known.

The course is designed for people who wish to pursue the development and implementation of Access in a multi-user environment.

For those unfamiliar with the Visual Basic language, a brief primer is included before launching into practical coding of the Access Object Model. The accent is on user productivity, in preparing input screens for rapid manipulation and search of data.

Most exercises are written from a user perspective. What mistakes is a user likely to make in using the database, and how can the program prevent them?

Most code examples are taken from databases currently in use by our clients. The manual does not take an academic approach to programming, rather the down-to-earth practical application of code in real-life situations.

Access security, automation and replication are covered in detail.