J
Jamie Collins
There is a distinction between MS Access and Jet. I'm not expecting you
to always explicitly *make* the distinction but please be aware of
which functionality is part of Jet and which is part of MS Access.
Broadly speaking, Jet is the database schema: tables (including data
types) and the data they comprise; CONSTRAINTs (UNIQUE, FOREIGN KEY,
CHECK etc); VIEWs and PROCEDUREs (querydefs), security (DATABASE
PASSWORD, GROUPs and USERs). MS Access is the front end: Forms and
Reports and their required infrastructure e.g. VBA plus some GUI
database management tools (table Datasheets, Query Builder, etc). MS
Access is a consumer of Jet e.g. even if you are using MSDE for your
data store, your MS Access-only elements will be persisted in Jet
tables. Jet does not require MS Access to be used as a fully
functioning database management system (DBMS). The distinction between
Jet and MS Access is not always so clear: Hyperlink is a data type
which only has meaning in MS Access (Jet sees it as MEMO with a flag);
MS Access has Autonumber, which can be an incrementing or random
numeric whereas Jet only has COUNTER i.e. limited to an incrementing
INTEGER. Microsoft enjoy blurring the distinction, which is understood
when you learn that Jet and its drivers/providers are available as a
download and the mdb format is freely distributable, whereas you must
pay to licence MS Access.
Jet 4.0, the most recent version, has been around since at least 2000
and Access2000, five years and three release versions of MS Access at
the time of writing, so you should be familiar with the feature list by
now. However, many, including well known MS Access MVPs, aren't so take
a look at 'Description of the new features that are included in
Microsoft Jet 4.0'
(http://support.microsoft.com/default.aspx?scid=kb;en-us;275561) in the
Knowledge Base. Key features, to whet your appetite, include a new data
type (DECIMAL), table-level CHECK constraints and vastly improved SQL
syntax. Having said that, those MVPs seemingly ignorant in this area
are familiar with the support for UNICODE introduced in Jet 4 and the
associated doubling of limits for MEMO and total file size: selective
memory, then? And remember, it's not that ADO was enhanced; more that
DAO was left behind. I don't want to spark off the DAO vs ADO debate,
you understand; it's largely a lifestyle choice but also horses for
courses: I will have no qualms about firing up DAO if and when I need
to do one of the few obscure things they never got round to coding into
ADO. And the implementation of Decimal isn't flawed. FLOAT (double
precision floating point, Double for short) is an inexact data type and
is not always appropriate e.g. a business requirement for money values
with greater accuracy than the 4 decimal places offered by the CURRENCY
data type that uses the inexact FLOAT/Double may incur the wrath of the
auditor. The isolated problem with DECIMAL, being that it can't sort
negative values in descending order, is easily worked around in the
front end. That TOP N fails for DECIMAL is merely confirmation that
this proprietary syntax is non-relational (i.e. you get different
results set when you change the ORDER BY clause) and uses a cursor
under the covers, whereas a simple, relational and portable subquery
will do. If DECIMAL was indeed flawed, we'd all be in trouble because
Jet uses it natively e.g. ever noticed what SELECT TypeName(0.5)
returns?
Learn to read and write SQL code. Honestly, it really isn't that hard.
Anyone who's ever used Excel's VBA Macro Recorder knows a machine can
write lousy code. Don't trust a Query Builder GUI tool to write your
business code for you.
Use SQL DDL (Data Definition Language) to create your schema: CREATE
TABLE, ALTER TABLE, CREATE PROCEDURE, CREATE VIEW, etc. GUI tools can
be fun at first but working from a script makes schema creation easy to
repeat during the design phase e.g. if you need to tweak, simply change
the script, DROP the objects (or delete the file) and re-run the
script. SQL DDL is much more 'portable' i.e. has a good chance of
being understood by users of other SQL products. And it makes newsgroup
posts a nicer experience e.g. no more of the likes of, 'I have this
table called Customers and it is has one column named person_ID which
always 10 characters and I have another column named ...' or, 'Open
this screen, click this button, right click the mouse and chose this
from the popup menu and type this into the textbox...' Wouldn't you
rather see 'CREATE TABLE Customers (person_ID CHAR(10) NOT NULL, ...)'
and 'ALTER TABLE Customers ADD CONSTRAINT ...'?
An autonumber isn't a good key (some would say it isn't a key at all).
Look for a 'natural' key, usually an industry standard (preferably an
international one) e.g. if you are modelling books or your cd
collection then take a look at what Amazon is using. Using a standard
key saves the effort of 'reinventing the wheel' and can help when
interfacing with other databases e.g. via online webservices when you
want to advertise your books for sale. A key such as the International
Standard Book Number (ISBN) has meaning built in e.g. region, publisher
etc. If you are generating your own key then at least incorporate a
check digit algorithm to minimize keying errors e.g. Mod 11 is simple
to implement using a CHECK constraint (Validation rule). Now consider
the autonumber in its usual form: an incrementing integer with no
meaning and unique to your database. Well, I suppose you can it's easy
to generate and use! If you prefer an autonumber for internal
efficiency (do you know how inefficient or otherwise a CHAR(10)
actually is?) then make sure you have a natural key as well because,
although a autonumber can help break duplicates (e.g. you have more
than one Jean Dupont) it may not be solving a problem unless you expose
the autonumber values ('OK, you are now Jean Dupont ID=561. You'll need
your ID to access your account next time you contact us'), which even
autonumber advocates agree is a no-no. If you don't expose them, how do
you break the duplicates when you come to query the data ('Forgotten
your ID? OK, are you the Jean Dupont with 1 million USD in your account
or the Jean Dupont with 1 USD?')
PRIMARY KEY has special meaning for Jet. In relational terms, a key is
a key and PRIMARY KEY has no special meaning. However, PRIMARY KEY has
traditionally taken on a meaning particular to each SQL product and Jet
is no exception. For Jet, the special meaning is that PRIMARY KEY
provides the only means of specifying the clustered index for a table.
The clustered index is non-maintained, meaning the index is only
'physically' rebuilt when the file is compacted; it is, however, still
maintained 'logically' between compacts. Some people have a problem
with the term 'clustered index' as regards Jet (don't make the mistake
on thinking 'not supported' means 'does not exist') so let me clarify:
PRIMARY KEY determines the physical ordering on disk. For best
performance, a table should be physically ordered on the columns of its
'primary' use i.e. those used in GROUP BY and BETWEEN clauses. Think of
a paper copy telephone directory, which can obviously have only one
physical order: if I'm using it primarily for search such as 'get
listings for people whose last name begins with the letter C' then I'd
want it ordered on last name because it would take me no time at all to
rip out the required pages. Similarly, it pays to choose your PRIMARY
KEY carefully and in accordance with its implications for physical
ordering on disk. If you are using PRIMARY KEY to mean a UNIQUE
constraint/index only then your database may be taking a performance
every time a table with a poorly chosen PRIMARY KEY is queried (pay no
attention to the message that tells you, 'A table must have a primary
key for you to define a relationship between this table and other
tables in the database,' it lies, it lies). And this is why an
autonumber is usually a poor choice for PRIMARY KEY: how useful would
you find a paper copy telephone directory physically ordered on
telephone number?
Be aware that a FOREIGN KEY (translation: a MS Access Relationship with
the power of referential integrity) has both an ON UPDATE clause and an
ON DELETE clause. The best approach is to always be explicit, even if
you choose NO ACTION for both. The most useful clause is CASCADE (don't
assume an standard key value will never change: think of a Social
Security Number change associated with a witness protection program or
merely a initial keying error). ON DELETE additionally has the option
of SET NULL, which even the MS SQL Server 2000 (MSSQL2000) product
doesn't have. Speaking of which, Jet has a better implementation of
CASCADE than MSSQL2000, to which anyone who has seen the dreaded 'may
cause cycles or multiple cascade paths' will attest. Such enviable
functionality in Jet is a great shame to waste.
Don't rely on your front end application (e.g. using an event in a MS
Access Form) to enforce data integrity: that's what Jet CONSTRAINTs are
for. CHECK constraints are as powerful in Jet as the best SQL products,
including MSSQL2000. The CHECK can either be at row level (i.e. can
reference itself plus other columns in the same row) or table level
(i.e. can reference other tables e.g. CHECK (this NOT IN (SELECT that
FROM OtherTable)). The only thing it can't do is reference other rows
in the same table, but then no currently available DBMS does. So there
are limits and, due to the absence of triggers in Jet, some front end
validation may be inevitable. Bear in mind, though, that all
applications, present and future, MS Access and otherwise, will have to
implement such front end constraints. For maintenance purposes, it's
best to have CONSTRAINTs in the database layer, if possible. Again,
don't let great CHECK functionality go to waste.
Using tbl as a prefix for you tables advertises you as a MS Access
user. Just be sure that is the message you want to send out.
In Ireland, the word is 'maths'.
Jamie.
--
to always explicitly *make* the distinction but please be aware of
which functionality is part of Jet and which is part of MS Access.
Broadly speaking, Jet is the database schema: tables (including data
types) and the data they comprise; CONSTRAINTs (UNIQUE, FOREIGN KEY,
CHECK etc); VIEWs and PROCEDUREs (querydefs), security (DATABASE
PASSWORD, GROUPs and USERs). MS Access is the front end: Forms and
Reports and their required infrastructure e.g. VBA plus some GUI
database management tools (table Datasheets, Query Builder, etc). MS
Access is a consumer of Jet e.g. even if you are using MSDE for your
data store, your MS Access-only elements will be persisted in Jet
tables. Jet does not require MS Access to be used as a fully
functioning database management system (DBMS). The distinction between
Jet and MS Access is not always so clear: Hyperlink is a data type
which only has meaning in MS Access (Jet sees it as MEMO with a flag);
MS Access has Autonumber, which can be an incrementing or random
numeric whereas Jet only has COUNTER i.e. limited to an incrementing
INTEGER. Microsoft enjoy blurring the distinction, which is understood
when you learn that Jet and its drivers/providers are available as a
download and the mdb format is freely distributable, whereas you must
pay to licence MS Access.
Jet 4.0, the most recent version, has been around since at least 2000
and Access2000, five years and three release versions of MS Access at
the time of writing, so you should be familiar with the feature list by
now. However, many, including well known MS Access MVPs, aren't so take
a look at 'Description of the new features that are included in
Microsoft Jet 4.0'
(http://support.microsoft.com/default.aspx?scid=kb;en-us;275561) in the
Knowledge Base. Key features, to whet your appetite, include a new data
type (DECIMAL), table-level CHECK constraints and vastly improved SQL
syntax. Having said that, those MVPs seemingly ignorant in this area
are familiar with the support for UNICODE introduced in Jet 4 and the
associated doubling of limits for MEMO and total file size: selective
memory, then? And remember, it's not that ADO was enhanced; more that
DAO was left behind. I don't want to spark off the DAO vs ADO debate,
you understand; it's largely a lifestyle choice but also horses for
courses: I will have no qualms about firing up DAO if and when I need
to do one of the few obscure things they never got round to coding into
ADO. And the implementation of Decimal isn't flawed. FLOAT (double
precision floating point, Double for short) is an inexact data type and
is not always appropriate e.g. a business requirement for money values
with greater accuracy than the 4 decimal places offered by the CURRENCY
data type that uses the inexact FLOAT/Double may incur the wrath of the
auditor. The isolated problem with DECIMAL, being that it can't sort
negative values in descending order, is easily worked around in the
front end. That TOP N fails for DECIMAL is merely confirmation that
this proprietary syntax is non-relational (i.e. you get different
results set when you change the ORDER BY clause) and uses a cursor
under the covers, whereas a simple, relational and portable subquery
will do. If DECIMAL was indeed flawed, we'd all be in trouble because
Jet uses it natively e.g. ever noticed what SELECT TypeName(0.5)
returns?
Learn to read and write SQL code. Honestly, it really isn't that hard.
Anyone who's ever used Excel's VBA Macro Recorder knows a machine can
write lousy code. Don't trust a Query Builder GUI tool to write your
business code for you.
Use SQL DDL (Data Definition Language) to create your schema: CREATE
TABLE, ALTER TABLE, CREATE PROCEDURE, CREATE VIEW, etc. GUI tools can
be fun at first but working from a script makes schema creation easy to
repeat during the design phase e.g. if you need to tweak, simply change
the script, DROP the objects (or delete the file) and re-run the
script. SQL DDL is much more 'portable' i.e. has a good chance of
being understood by users of other SQL products. And it makes newsgroup
posts a nicer experience e.g. no more of the likes of, 'I have this
table called Customers and it is has one column named person_ID which
always 10 characters and I have another column named ...' or, 'Open
this screen, click this button, right click the mouse and chose this
from the popup menu and type this into the textbox...' Wouldn't you
rather see 'CREATE TABLE Customers (person_ID CHAR(10) NOT NULL, ...)'
and 'ALTER TABLE Customers ADD CONSTRAINT ...'?
An autonumber isn't a good key (some would say it isn't a key at all).
Look for a 'natural' key, usually an industry standard (preferably an
international one) e.g. if you are modelling books or your cd
collection then take a look at what Amazon is using. Using a standard
key saves the effort of 'reinventing the wheel' and can help when
interfacing with other databases e.g. via online webservices when you
want to advertise your books for sale. A key such as the International
Standard Book Number (ISBN) has meaning built in e.g. region, publisher
etc. If you are generating your own key then at least incorporate a
check digit algorithm to minimize keying errors e.g. Mod 11 is simple
to implement using a CHECK constraint (Validation rule). Now consider
the autonumber in its usual form: an incrementing integer with no
meaning and unique to your database. Well, I suppose you can it's easy
to generate and use! If you prefer an autonumber for internal
efficiency (do you know how inefficient or otherwise a CHAR(10)
actually is?) then make sure you have a natural key as well because,
although a autonumber can help break duplicates (e.g. you have more
than one Jean Dupont) it may not be solving a problem unless you expose
the autonumber values ('OK, you are now Jean Dupont ID=561. You'll need
your ID to access your account next time you contact us'), which even
autonumber advocates agree is a no-no. If you don't expose them, how do
you break the duplicates when you come to query the data ('Forgotten
your ID? OK, are you the Jean Dupont with 1 million USD in your account
or the Jean Dupont with 1 USD?')
PRIMARY KEY has special meaning for Jet. In relational terms, a key is
a key and PRIMARY KEY has no special meaning. However, PRIMARY KEY has
traditionally taken on a meaning particular to each SQL product and Jet
is no exception. For Jet, the special meaning is that PRIMARY KEY
provides the only means of specifying the clustered index for a table.
The clustered index is non-maintained, meaning the index is only
'physically' rebuilt when the file is compacted; it is, however, still
maintained 'logically' between compacts. Some people have a problem
with the term 'clustered index' as regards Jet (don't make the mistake
on thinking 'not supported' means 'does not exist') so let me clarify:
PRIMARY KEY determines the physical ordering on disk. For best
performance, a table should be physically ordered on the columns of its
'primary' use i.e. those used in GROUP BY and BETWEEN clauses. Think of
a paper copy telephone directory, which can obviously have only one
physical order: if I'm using it primarily for search such as 'get
listings for people whose last name begins with the letter C' then I'd
want it ordered on last name because it would take me no time at all to
rip out the required pages. Similarly, it pays to choose your PRIMARY
KEY carefully and in accordance with its implications for physical
ordering on disk. If you are using PRIMARY KEY to mean a UNIQUE
constraint/index only then your database may be taking a performance
every time a table with a poorly chosen PRIMARY KEY is queried (pay no
attention to the message that tells you, 'A table must have a primary
key for you to define a relationship between this table and other
tables in the database,' it lies, it lies). And this is why an
autonumber is usually a poor choice for PRIMARY KEY: how useful would
you find a paper copy telephone directory physically ordered on
telephone number?
Be aware that a FOREIGN KEY (translation: a MS Access Relationship with
the power of referential integrity) has both an ON UPDATE clause and an
ON DELETE clause. The best approach is to always be explicit, even if
you choose NO ACTION for both. The most useful clause is CASCADE (don't
assume an standard key value will never change: think of a Social
Security Number change associated with a witness protection program or
merely a initial keying error). ON DELETE additionally has the option
of SET NULL, which even the MS SQL Server 2000 (MSSQL2000) product
doesn't have. Speaking of which, Jet has a better implementation of
CASCADE than MSSQL2000, to which anyone who has seen the dreaded 'may
cause cycles or multiple cascade paths' will attest. Such enviable
functionality in Jet is a great shame to waste.
Don't rely on your front end application (e.g. using an event in a MS
Access Form) to enforce data integrity: that's what Jet CONSTRAINTs are
for. CHECK constraints are as powerful in Jet as the best SQL products,
including MSSQL2000. The CHECK can either be at row level (i.e. can
reference itself plus other columns in the same row) or table level
(i.e. can reference other tables e.g. CHECK (this NOT IN (SELECT that
FROM OtherTable)). The only thing it can't do is reference other rows
in the same table, but then no currently available DBMS does. So there
are limits and, due to the absence of triggers in Jet, some front end
validation may be inevitable. Bear in mind, though, that all
applications, present and future, MS Access and otherwise, will have to
implement such front end constraints. For maintenance purposes, it's
best to have CONSTRAINTs in the database layer, if possible. Again,
don't let great CHECK functionality go to waste.
Using tbl as a prefix for you tables advertises you as a MS Access
user. Just be sure that is the message you want to send out.
In Ireland, the word is 'maths'.
Jamie.
--