Microsoft Office Access Architecture

Microsoft Office Access Architecture
Microsoft Office Access can be used as a standalone system on a single PC or as a multiuser
system on a PC network. Since the release of Access 2000, there is a choice of two
data engines† in the product: the original Jet engine and the new Microsoft SQL Server
Desktop Engine (MSDE, previously the Microsoft Data Engine), which is compatible with
Microsoft’s backoffice SQL Server. The Jet engine stores all the application data, such
as tables, indexes, queries, forms, and reports, in a single Microsoft database (.mdb) file,
based on the ISAM (Indexed Sequential Access Method) organization (see Appendix C).
MSDE is based on the same data engine as SQL Server, enabling users to write one application
that scales from a PC running Windows 95 to multiprocessor clusters running
Windows Server 2003. MSDE also provides a migration path to allow users to subsequently
upgrade to SQL Server. However, unlike SQL Server, MSDE has a 2 gigabyte
database size limit.
Microsoft Office Access, like SQL Server, divides the data stored in its table structures
into 2 kilobyte data pages, corresponding to the size of a conventional DOS fixed-disk file
cluster. Each page contains one or more records. A record cannot span more than a
single page, although Memo and OLE Object fields can be stored in pages separate
from the rest of the record. Office Access uses variable-length records as the standard
method of storage and allows records to be ordered by the use of an index, such as a
primary key. Using variable length, each record occupies only the space required to store
its actual data.
A header is added to each page to create a linked list of data pages. The header contains
a pointer to the page that precedes it and another pointer to the page that follows. If no
indexes are in use, new data is added to the last page of the table until the page is full, and
then another page is added at the end. One advantage of data pages with their own header
is that a table’s data pages can be kept in ISAM order by altering the pointers in the page
header, and not the structure of the file itself.
Multi-user support
Microsoft Office Access provides four main ways of working with a database that is
shared among users on a network:
n File-server solutions An Office Access database is placed on a network so that multiple
users can share it. In this case, each workstation runs a copy of the Office Access
application.
n Client–server solutions In earlier versions of Office Access, the only way to achieve
this was to create linked tables that used an ODBC driver to link to a database such as
SQL Server. Since Access 2000, an Access Project (.adp) File can also be created,
which can store forms, reports, macros, and VBA modules locally and can connect to
a remote SQL Server database using OLE DB (Object Linking and Embedding for
Databases) to display and work with tables, views, relationships, and stored procedures.
As mentioned above, MSDE can also be used to achieve this type of solution.
† A ‘data engine’ or ‘database engine’ is the core process that a DBMS uses to store and maintain data.
n Database replication solutions These allow data or database design changes to be
shared between copies of an Office Access database in different locations without having
to redistribute copies of the entire database. Replication involves producing one or
more copies, called replicas, of a single original database, called the Design Master.
Together, the Design Master and its replicas are called a replica set. By performing a
process called synchronization, changes to objects and data are distributed to all members
of the replica set. Changes to the design of objects can only be made in the Design
Master, but changes to data can be made from any member of the replica set. We discuss
replication in Chapter 24.
n Web-based database solutions A browser displays one or more data access pages that
dynamically link to a shared Office Access or SQL Server database. These pages have to
be displayed by Internet Explorer 5 or later. We discuss this solution in Section 29.10.5.
When a database resides on a file server, the operating system’s locking primitives are
used to lock pages when a table record is being updated. In a multi-user environment, Jet
uses a locking database (.ldb) file to store information on which records are locked and
which user has them locked. The locking database file is created when a database is opened
for shared access. We discuss locking in detail in Section 20.2.