Advanced Access

This course builds on our Introductory to Microsoft Access course, where we introduced Microsoft's powerful relational database tool for personal computers. Advanced Access is only offered on a 'one on one' basis and is geared toward people who want to construct working Access databases. These will go above and beyond what can be achieved the simple database design shown in our earlier course and in the use of Access "wizards". We would expect to work with you to construct a database which contains several tables and join them in various relationships and look at issues in transferring data from one database to another (importing and linking data). We will confront data validation and formatting issues that can make or break a database (a database is only as good as the data it holds).

CompexRelationships

We need time to prepare for your course and need to know something about your aims and so need information from you prior to starting. It is important for us that we satisfy ourselves that we can indeed deliver what you require and so want to work on your materials prior to attending. We encourage you to look at the list of topics below and indicate those which are important to you.

Cost of courses

$750 for a day of one on one training (time spent in preparation is free). Please note that we are not providing consulting 'fixes' for existing databases. For that you need a database consultant.

Prerequisites

Before coming to this class, we would normally expct that you have completed our 'Introduction to Microsoft Access' course. Attendees should understand:

  • the difference between a flat file and a relational system

  • the different data types one finds in databases

  • the concept of primary and foreign keys.

We may cover some of the following: 

 

    Module

    Description
How our learning system works. Examining a professional database application developed in Access. Prerequisites for this course. Revising your understanding of relational databases, emphasizing the need to plan your database application. Making sure that you are clear about the required data structure for your database problem - data modelling.
Developing Documentation
Professional database developers make sure that they document their application and keep the documents up to date. Access 2007 has a tool to help manage this and in this module, we explore that tool.
Good databases need a menu system so that inexperienced people can perform data entry and retrieval. Access provides a ''Switchboard" system to do just that. We discussed the need for documenting your application in the previous module. the documentation contains a description of the menus used. Here we discuss the use of the tool. The switchboard is a powerful tool to create a menu system to make management easier and allows the Access application itself to be hidden from casual users.
We examine a database in need of a switchboard. Access 2003 and Access 2007 approach the switchboard differently. The navigation pane created by the switchboard tool now conforms to the single document model advocated for Office 2007 products. Creating an auto start macro which shows the menu and hide the database objects.
A close look at tables. Data types, hyperlinks, text fields, number fields, yes/no fields, input masks, naming fields, reserved words, null values, order of fields, storing calculated values, descriptions, number that are not numbers, validation rules for fields, validation rules for tables, lookup fields.
A second look at Forms and their use. Using the Form Wizard. Moving to layout view and reorganizing the appearance of a form. Applying themes to a form. Moving to the Design View of a Form. Using the toolbox to populate forms with objects.
Select Queries. Sources for Queries. Types of Queries. Editing via Queries, SQL and SQL language Queries, the Query Wizard, calculated fields, editing SQL expressions in calculated fields. Concatenating field names in a Query.
Basing reports on Queries. Calculating totals in reports.
Early versions of Access allowed the creation of web pages which allowed for data access. Access 2007 now encourages the use of SharePoint Services to achieve the same end.
Some revision from the introductory course. Everything you ever wanted to know about relationships.
Build a project definition with your client. Find out what is required. Mock up the user interface, get lots of feedback before beginning to develop a database.
Defining your problem. Database design is an iterative process. Create tables, print relationship diagram, test by entering data, refine your design, split tables, test again, document and document some more!
Backing up, compacting and repair, analyzing the content etc.
The importance of planning your entire application before you start. We create a database for a company managing the issue of record keeping for safety equipment, licenses and certification and training records.
Information in and out. Interacting with a mainframe database holding information on batches of wool which have been scoured. Importing information from Excel, from comma delimited text files, fixed width text files, from another Access database, from other databases, from a HTML file, from XML. Export to other programs, linking to another database.
School administration. Developing a database for managing enquiries for school enrolments. Includes the development of a project charter.
Case Study 4
Developing a database for managing contact with members of indigenous communities.
Managing contact between Contract Research Organizations and study site monitors.
Developing a database capable of recording images in a field.
Keeping information accurate
Use data type and filed type settings to restrict data. Using input masks to restrict data, use validation rules, using lookup lists. Updating information in tables, deleting information. The FrontPage question and answer database as an example.
Action Queries
Make table, append table, delete table, cross tab, select and SQL queries etc.
Pages and Modules
Creating static web pages, exporting Visual Basic for Applications, Using Visual Basic for Applications (VBA) to create a web page, creating a data access page with the AutoPage facility, creating a data access page using the page wizard, allowing others to analyze data on the web.
The raw data for the generation of form letters can come from a variety of sources - mainframe computers, specialist databases such as the Bureau of Statistics, databases such as Access, FileMaker Pro, SQL Server, Oracle etc. It is not difficult to obtain that information from those sources in the form of ASCII text files. Excel can load those files expertly and you can use the combination of Microsoft Word and Excel to generate form letters, filtering data as you go if that is what is required. the filtering can also be done in the database before outputting the ASCII text file.
Securing your information
Encrypting and decrypting an Access database, assigning passwords, sharing databases, replicating a database, splitting databases, setting up a workgroup, maintaining a workgroup, preventing changes to a database, securing a database for distribution.
Case Study
Web integration. Using Access to publish web pages. First just HTML, but later publishing to a Microsoft SQL server.
Some useful expressions to use in VBA programmming.