Relational Database Management Systems (RDBMS) allow users to define and use data stored in tables for a huge variety of purposes. Microsoft Access is one of many products that allows the creation of commercial relational applications that integrate data, forms and reports with a 'friendly' GUI (Graphical User Interface). Users of Microsoft Windows will no doubt recognise that Access adopts many of the robust methods inherent in Windows applications, whilst allowing the designer a flexible development tool to cope with a wide variety of data and ways of presenting it.
This booklet is not meant to be a definitive guide (indeed, even the thousands of pages of manual that comes with the package fails to completely describe all situations the designer may choose to pursue). Rather, it is meant as a beginning for students - to point out some features, and more generally, ways in which the system works. This, hopefully, will provide the basis for exploration and informed decision making.
Although complex in nature, the process of application development remains fairly predictable. This is largely regardless of the tool used in implementation. There are a wide variety of conventions and command structures inherent in each application development tool - this document shall concentrate on Microsoft Access.
Most actions in Access are begun from the Database Window. From here it is possible to create, edit and use the six basic types of database objects available: Tables, Queries, Forms, Reports, Macros and Modules).
An Access Table is a container for data - functionally identical to an SQL Table. A Table is comprised of Tuples (records, rows) each of which is made up of fields.
An Access Query is a data retrieval, manipulation or modification request. Queries in Access can be composed or graphically constructed using the QBE (Query By Example) Grid.
A Form in Access is an on-screen layout format designed to display and present data from tables, queries and other forms. They provide the User-Computer Interface for most processes in an Access database system.
A Report provides hardcopy facilities for the database allowing output reports to be generated on printers than include standard wordprocessor features.
A Macro is an automated process in Access. They provide the facility to 'record' actions and 'play back' at will.
A Module is a section of code (in this case Access BASIC code) that is written to externally operate on Access databases.
Each database object type has construction, viewing and storage options, along with custom tools specific to that object type. This booklet will only concentrate on tables, queries, forms and reports.
It is possible to perform most actions using pull down menus, custom buttons and Wizards (automatic processes). Much of the power of this product, however, lies behind the database objects you create. You will probably choose to develop many of these objects manually because of the extra control you have over them when doing so.
A Database in Access is a container for database objects. Access maintains all data and access permissions in 2 files: .MDB = the database file and .LDB = Multi-User Administration File.
To Create a NEW DATABASE choose FILE then NEW DATABASE from the top menu. You will be prompted for a name and location, then will be presented with a blank database window.
To Create a NEW TABLE choose New from the Table Database Window then the New Table button. You will be presented with a blank field builder grid.
Each field(column) of your proposed table is defined with:
Defining Primary Keys for your tables involve highlighting the fields involved in the key and pressing the Set Primary Key button on the Button Bar. You can highlight multiple fields by click-dragging multiple field selector buttons if the are sequential, or control-clicking individual field selector buttons if they are not immediately adjacent to one another.
Secondary Keys are created by specifying Unique Indexes on those columns as part of their format specification. It is possible to view and edit indexes created by you by pressing the INDEX button on the button bar in table design view.
As you can imagine, the datatype of each field profoundly effects storage space required by your database. Suppose you wanted to store height measurements, 180.5cm as an example. As a number it would occupy 8 bytes, as text data it would occupy 5 bytes - the saving of 3 bytes (for 1,000,000 records and this is a saving of 3Mb). As text, mathematical operations like averages, sums and the like would be very clumsy so choose column types prudently.
Indexes should be created on all fields that are likely to be the basis of search or ordering as a general rule. These indices will greatly improve Access' performance when using these fields. Indexes cannot be placed on OLE or Memo Fields, neither can these be sorted.
It should be noted here that Access performs JOIN queries faster than SUB-QUERIES. This would appear to be a 'side-effect' of MS-SQL. Query performance can be sluggish on large tables, yet can be improved with prudent use of indexes
Foreign keys are defined by specifying relationships between defined tables.
To Define Relationships between tables choose Edit from the Top Menu then the Relationships from the Edit menu (or press the Relationships button from the Button Bar). You will be presented with a blank relationships window. Add the tables in your database, then pointing to the field that provides the Superset (the '1' pictured opposite) click-drag it onto a field that provides the subset (or foreign key - indicated with an 'ì' in the diagram opposite).
You will then be provided with a relationship builder grid where you can specify Cascade effects (delete and update), Referential Integrity Enforcement, nature of relationship (1:1 or 1:many) and Join Type. For foreign keys within the same table, multiple copies of that table can be added to the window and relationships dragged and dropped as before.
Relationships provide powerful control over you tables populations, and coupled with field descriptions can automate much of the validity checking that is in other products normally externally applied.
A query in Access, as with any flavour of SQL, can perform a wide variety of purposes.
A Select Query is a request to view information. The source of most of this information is usually fields from tables that exist in the database. The answers that are delivered by queries can take 2 tabular forms:
An Action Query makes changes to the database population by updating, deleting or adding tuples. A Data Definition Query creates or changes the structure of the database, or database objects contained within it.
It is possible to specify queries using either the QBE (Query By Example) grid, using a Query Wizard or by entering standard SQL. QBE and SQL will be described in this booklet, wizards will not due to their 'inflexible' nature and the lack of low level control they afford.
To Create a NEW QUERY choose New from the Query Database Window (or press the New Query Button on the Button Bar) then the New Query button. You will be presented with a blank Select Query builder grid, and be required to nominate tables or queries that are involved in the query (or press Close to get past this dialog). Once in to the query builder grid, the query can proceed manually (in SQL) or semi- automatically using QBE. Experience shows that it is faster to write queries using a mix of SQL & QBE
To compose a query using QBE:
To write a query using SQL press the SQL button on the Button Bar. You will be presented with a blank SQL window. Type your query, as you would using standard SQL.
Wildcards available for criteria in Access include ? (any single character) and * (any sequence of characters). Logical connectives NOT, AND, OR are available, as is the Set Operator IN.
To Execute a Query choose Query then Run from the Top Menu, or press the Run [!] button on the Button Bar or press the Datasheet button on the Button Bar.
To Rename a column for the purposes of display, use Select AlbName AS Recording in SQL (or Recording: AlbName in the QBE grid).
To display only part of a field, Access provides Left(string,n), Right(string,n) and Mid(string,start,n). In the MUSIC database for example, if you wanted the first 3 characters of the SerNum field from the Albums table, you could type Select Left(Albums.SerNum,3) in SQL, or place NewTitle:Left(Albums.SerNum,3) in the Field specifier of the QBE grid (NewTitle is a label for the new column in the answer table and can be anything except an existing field name).
To Format the answer recordset you can specify Field Properties by pressing the Properties button on the Button Bar after clicking in the field to be formatted, or directly massage the Datasheet view of the executed query. In addition, in Datasheet View, you have Font, Row Height and Column Height control in the Format menu off the Top Menu.
To write a query that prompts for Parameters, include the reference to the parameter (distinct from an existing feild name) as a criteria in square brackets: [Please Enter Artist:]. As an example, a query that lists AlbName of the prompted-for Artist could be expressed as illustrated left in QBE and right in SQL.
When run, the query places a prompt window on the screen requiring the user to enter information and click OK (or press enter).
After the user has responded, the query delivers an answer table in default format (i.e. with column names, records delimited by horizontal and vertical lines, with record selectors.
A query that prompts for the first letter of Artists and displays their Albums is as follows:
select AlbName from Albums where Artist like [Please Enter Artist's First Letter:] & '*';
A query that prompts for start and finish dates and displays Albums published between those dates is as follows:
select AlbName from Albums where PDate between [Start Date:] and [Finish Date:];
To Group records either press the SUM button on the button bar, or use a Group By clause in SQL.
Contract Webmaster:
Mr. Peter R. Whitehouse
Subject Coordinator - Information Technology Education
St. Joseph's College, Gregory Terrace
(c)Copyright 1996.
