INTRODUCTION TO MICROSOFT ACCESS

The Microsoft Office Access relational database management system is software to manage databases.

6.1. What is a database management system?

Microsoft Access is an example of a Relational DataBase Management System (RDBMS). It is positioned in the marketplace as an office productivity aid. As such, it has limited

resilience and recovery facilities, can be used by more than one person at the same time, but is NOT suitable for "mission-critical" high reliability or high performance applications - beyond a handful of concurrent users, Access runs out of steam!

Access - and other small scale databases, such as Filemaker Pro - offer the standard features expected of such programs:

6.1.1. Software which manages a database

6.1.2. Implements entities as tables, maintaining and enforcing relationships

6.1.3. Deals with all the component disc files

In Access, there is only one file per database. Bigger databases have much more complex file structures stored on disc.

6.1.4. Provides functions such as

♦ Table creation and structural updating

♦ Form-based insert, update and delete operations, on individual records and on complete sets of records

♦ Query and report facilities

Access provides powerful query and report facilities. When you define a query, what Access does on your behalf, behind the scenes, is to create and then run a query expressed in a powerful industry-standard programming language called SQL (Structured Query Language). You can in fact see the generated SQL if you use View / SQL View.

6.1.5.An approachable programming language

Access offers Visual Basic for Applications (VBA) and SQL.

6.2. Important facilities of more advanced DBMS

More advanced DBMS offer additional features, and are usually structured to operate in a so-called "client server" situation. In a client-server application, client programs running on PCs or other low-power computers present data to individual users. The data itself is managed and stored on a database server computer to which all the client machines are connected.

The clients may either be directly connected to the distant database server, or they may run a local database (usually Access) which connects to the distant database server (e.g. MS SQL Server, Oracle ….) using the Open DataBase Connectivity feature ODBC, or they may

present the data in the database by means of web pages. The architecture then looks

∗ MS Access does this, sort of ... an individual database may support a handful of users

♦ Depend upon a data dictionary (sometimes called a repository)

♦ Integrate with the CASE (Computer Aided Software Engineering) tool which created and maintains the data dictionary

♦ Implement resilience and recovery mechanisms These things include roll-forward and / or roll-back mechanisms so that complete transactions (only) are carried out. Such mechanisms are essential to prevent situations where, for example, money leaves one company’s bank account, but never reaches another company’s.

♦ Enforce security

Only privileged users should be able to see things like payroll data.

6.3.1. Other RDBMS

Examples of so-called "industrial strength" databases include

♦ The “Big Three”

These are the commercial databases at the heart of most large enterprises:

(a) Microsoft SQL Server

(b) Oracle Corporation ORACLE (c) IBM DB2

♦ Computer Associates INGRES

The first commercial-strength database management system, INGRES is now an Open Source product.

♦ MySQL

MySQL is a relational database management system which has more than 11 million installations.

MySQL is popular for web applications and acts as the database component of the LAMP, BAMP, MAMP, and WAMP platforms (Linux/BSD/Mac/Windows-Apache-MySQL-PHP/Perl/Python), and for open-source bug tracking tools like Bugzilla. Its popularity for use with web applications is closely tied to the popularity of the PHP programming language and the Ruby on Rails programming framework, which are often combined with MySQL.

♦ PostgreSQL

These systems are capable of supporting very large numbers of users and transaction rates measured in tens or hundreds every second. They are typically used to store so-called "corporate"

databases, and are "overkill" in the context of the personal and team productivity applications to which Access is well-suited.

6.4. Why we want business students to learn Access

We expect our students to become competent Access users and (to a limited extent) designers. We start off with Access for a number of reasons:

6.4.1. The relative ease-of-use of MS Access

Industrial strength databases, such as ORACLE, are harder to learn and less well integrated into the PC environment, whereas MS Access is easily accessible (sic!)

6.4.2. MS Access is easily obtained

It forms a part of the Microsoft Office Professional and Premium office suites (although it is included neither in the Small Business Edition nor the Student edition). MS Access is available in French on ESC Rennes student workstations for students who do not have a copy on their own personal machine. Alternatively a free copy can be obtained by means of the MSDN Academic Alliance membership of the School.

6.4.3.MS Access supports usable programming languages

MS Access supports and integrates with the two most widely-used programming languages associated with personal productivity aids and with databases. These two languages are Basic and SQL. In fact, MS Access provides Basic and SQL in a number of ways:

♦ Visual Basic for Applications (VBA)

The dialect of Basic supported by MS Access is VBA - the same language also used internally by several other Microsoft Office products, including Excel and the Visio business drawing package.

♦ Visual Basic (VB) itself

Access can act as the so-called "Jet Engine", providing database facilities to programs written in Visual Basic.

♦ SQL: Structured Query Language

SQL, Structured Query Language, is a database query

language that was adopted as an industry standard in 1986.

In their SQL standard, the American National Standards Institute ANSI declared that the official pronunciation for SQL is "es queue el". However, many database professionals have taken to the "slang" pronunciation sequel that reflects the language's original name, Sequel, before trademark conflicts caused IBM to insist on the ‘official’ pronunciation.

Access supports a reasonably-comprehensive subset of the ANSI SQL 92 standard. This provides the basis for a high degree of integration with other databases - so that, for example, an Access database can act as a client to an industrial-strength database running on a server computer.