Database Definition
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.
Table Definition
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:
- Field Name - this can be any descriptive word or phrase.
Access allows you to use spaces in the name of your tables. Ensure
that within each table, field names are unique.
- Datatype - a rich variety of datatypes are available in
Access including Text (alphanumeric, up to 255 bytes long
- 1 byte per char), Memo (alphanumeric up to 64000 chars/bytes),
Number (integers or fractional - 1, 2, 4 or 8 bytes each),
Date/Time (8 bytes each), Currency (8 bytes each),
Counter (an Access autonumber value - 4 bytes), Yes/No
(boolean value - 1 bit each) and OLE Object (binary data
from another Windows application - up to 1Gb limited by hard disk
space). In addition to type specification, you can specify Field
Size (# significant chars or precision of numeric value), Format
(appearance when displayed), Input Mask (standard input format),
Caption (Message or title for field), Default Value
(if no value is supplied, this will insert predefined value into
field), Validation rule (check conditions), Validation
Text (Error Message if invalid data is supplied), Required
(Mandatory/Optional) and Indexed (unique with or without
duplicates to speed up search and retrieval based on this field)
- Description - this is text that will be displayed in the
status bar when this field is selected on the table or in a form
that uses this field.
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
Referential Integrity
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.
