time serial question

[Jamie Collins]

RoyVidar wrote:

say you use a check constraint. Then in a couple of years,
there's a corruption issue

Say you omit the check constraint. How long before there's a data
integrity issue? How long before it's detected?

Jamie.

--

 

[RoyVidar]

RoyVidar wrote:


Say you omit the check constraint. How long before there's a data
integrity issue? How long before it's detected?

Jamie.

Why should that suddenly start happening now when it has never happened
before?

 

[Jamie Collins]

Why should that suddenly start happening now when it has never happened
before?

Because there is no constraint to prevent it and someone made a typo, a
bug was introduced into the front end code, someone accidentally
changed a value without also changing the calculation, an Excel user
was experimenting using MSQuey, etc.

Or are you asking me, 'Why have database constraints...?'

Jamie.

--

 

[RoyVidar]

Because there is no constraint to prevent it

Eh - so what you're saying is that the natural consequence of not using
a check constraint when working with Access, where said check
constraint
isn't even supported, break standard Access behaviour and runs at a
risk
of not being operative after maintenance, is that one does not use any
constraints or validation at all? Thanx <g>

 

[Jamie Collins]

Eh - so what you're saying is that the natural consequence of not using
a check constraint when working with Access, where said check
constraint
isn't even supported, break standard Access behaviour and runs at a
risk
of not being operative after maintenance, is that one does not use any
constraints or validation at all? Thanx <g>

I'm not sure why you've misinterpreted my point but you have.

In case it was not clear, I'll restate it: the natural consequence of
not using a data integrity constraint when one is available is a
needless risk of loss of data integrity.

I have a problem with your counter-argument in that it appears to be
based on a number of 'what ifs' e.g. what if there is corruption, what
if the maintenance person is not operating ANSI query mode on a schema
known to contain CHECK constraints, what if the maintenance person is
unfamiliar with the schema, what if they fail to consult the
documentation, what if they ignore an error message about DDL when
attempting to delete the table, etc. But let's say all those bad things
do happen and the CHECK constraint accidentally gets 'lost'. I don't
see how 'it might get lost' is a viable argument for not having
something in the first place.

I don't know what you mean by 'standard Access behaviour'. ANSI query
mode is available; as you pointed out, an ADO connection can also be
used; for me, both are 'standard behaviour'.

Jamie.

--

 

[RoyVidar]

I'm not sure why you've misinterpreted my point but you have.

In case it was not clear, I'll restate it: the natural consequence of
not using a data integrity constraint when one is available is a
needless risk of loss of data integrity.

No, I think it's the other way around. You have misinterpreted me. This
is an Access NG. People come here seeking answers to Access questions.
You provide Jet suggestions, some which aren't even supported by
Access,
and may have consequences on standard behaviour of Access. So, in the
context of this being an Access NG, the point is that it isn't check
constraint vs no constraint, it is check constraint vs what can be
achieved within the context/interface of Access.

I feel that when working within the context of Access, a good design (a
good design would also mean no values are dependent on column(s) in the
same row), indices, validation rules, relationship and usage of
relevant
form events will provide sufficient validation to ensure data
integrity.
Some of the features of Jet that are not supported by Access are
interesting, but when working in an Access context, I'm not sure I find
them interesting enough that it'd be worth utilizing them. If one
hasn't
experienced any integrity issues when utilizing what Access offers, why
fix what ain't broken?

I have a problem with your counter-argument in that it appears to be
based on a number of 'what ifs' e.g. what if there is corruption,
what if the maintenance person is not operating ANSI query mode on a
schema known to contain CHECK constraints, what if the maintenance
person is unfamiliar with the schema, what if they fail to consult
the documentation, what if they ignore an error message about DDL
when attempting to delete the table, etc. But let's say all those bad
things do happen and the CHECK constraint accidentally gets 'lost'. I
don't see how 'it might get lost' is a viable argument for not having
something in the first place.

You seem to recognize a faint possibility of loosing the check
constraint. I think the danger of loosing validation is less when
working without Jet check constraints. So, again, if one hasn't
experienced any integrity issues when utilizing what Access offers, why
fix what ain't broken?

Anyway, how common is it for Access developers to operate in ANSI mode?
How common is it for Access developers to utilize check constraints?

I don't know what you mean by 'standard Access behaviour'. ANSI query
mode is available; as you pointed out, an ADO connection can also be
used; for me, both are 'standard behaviour'.

Standard behaviour of Access? Out of the box? No ANSI mode, no non
supported Jet thingies, able to copy/paste tables at will, exporting/
importing tables between mdb files, both manually and through for
instance the TransferDatabase method of the DoCmd object (SaveAstText
is
perhaps stretching it a bit?), including definition, contents and
constraints/validation rules? He, he - setting the AllowZeroLength
property in table design view ;-)

 

[Jamie Collins]

You provide Jet suggestions, some which aren't even
supported by Access and may have consequences on
standard behaviour of Access. So, in the context of this
being an Access NG, the point is that it isn't check
constraint vs no constraint, it is check constraint vs
what can be achieved within the context/interface of
Access.

You know you can create a CHECK constraint in the interface of Access
while in ANSI-92 mode. Therefore, check constraints *can* be achieved
within the context (e.g. ADO) and interface (e.g. ANSI-92 mode) of
Access.

If we only had Access 2000 and the Jet 4.0 goodies were only available
via ADO I can see it would be difficult to convince a long-time Access
user to utilize them. However, we have Access 2002 and Access 2003 with
ANSI-92 mode (previously known as 'ANSI query mode').

Which of my 'Jet suggestions' are not supported by Access while in
ANSI-92 mode? I can't think of any.

how common is it for Access developers
to operate in ANSI mode?
How common is it for Access developers to utilize
check constraints?

This is a bit 'chicken and egg'. In this thread we have an Access MVP
who was not familiar with CHECK constraints in any context (the SQL
standards, Jet, Access interface, etc).

If more people were aware of the benefits of ANSI-92 mode, even if it
was to use a single SQL DDL statement against an ADO connection in the
VBE Immediate Window as a one-off to create vital database constraint,
then I'm sure more people would use it.

What the Jet 4.0 features need is an Access MVP champion (clearly I
suck at this job myself <g>). We have Michel Walsh Vanderghast Access
MVP but someone more evangelical could get the message across.

What percentage Jet 4.0 users are Access users? If Jet 4.0 is not for
Access users then who?

Standard behaviour of Access? Out of the
box? No ANSI mode, no non supported
Jet thingies,

Plenty of Access features are unavailable using out of the box settings
e.g. aren't VBA macros disabled by default? FWIW I get ANSI-92 mode out
of the box in Access2007; maybe the world is changing for the better
<g>.

Did you see the recent thread, 'What exactly is Microsoft Access
(seriously)?':

http://groups.google.com/group/micr...085af709c1/126c50ec56291669?#126c50ec56291669

I think the conclusion is, Access is many things to many people.

Using Jet 4.0 features in ANSI-92 mode is perfectly legitimate
*supported* behaviour in Access; this is NOT 'under the hood' stuff,
I'm not cracking the mdb format with a hex editor <g>! It seems the
best we can do is agree to differ on the significance of 'standard'
behaviour.

a good design would also mean no values are
dependent on column(s) in the same row

That is a misstatement, presumably unintentional. Consider the salary
history example I posted above: start_date and end_date are parts of a
single atomic fact and end_date cannot occur before start_date; a good
design would have a validation rule or CHECK constraint to ensure this
could never occur in the database. This can be achieved using an Access
row-level Validation Rule (which the pre-Access2007 help confusingly
describes as 'table' level), which would presumably meet your
requirements for standard Access behaviour.

setting the AllowZeroLength
property in table design view ;-)

You picked a good example. I think many people who use this setting
would be surprised it will not prevent:

INSERT INTO (my_text_col) VALUES ('');

Of course, what is required is something at the engine level e.g. a
validation rule:

<> ''

or

CHECK (LEN(my_text_col) > 0)

Even if people do not use ANSI-92 mode they should be aware of its
existence. For example, a rule to allow only alpha characters:

NOT LIKE '*[A-Z]*'

However, if I used an ADO connection to the database, I could execute
this:

INSERT INTO Test (my_text_col) VALUES ('123');

it would succeed and my data integrity is gone.

To avoid this, I could execute a one-off SQL DDL statement against my
database:

ALTER TABLE Test ADD
CONSTRAINT test__my_text_col__alpha_only
CHECK (
text_col NOT LIKE '%[!A-Z]%'
AND text_col NOT LIKE '*[!A-Z]*'
);

Now my data integrity is secure until the day when the constraint is
dropped (how to make a schema immune to the tampering of well-meaning
yet ignorant maintenance staff is another matter).

If one hasn't
experienced any integrity issues when utilizing what Access offers, why
fix what ain't broken?

I refer you again to my example salary history table, where the
'primary key' (i.e. the thing at the engine level that prevents
duplicates) can only be implemented via a (truly) table-level CHECK
constraint. Sure, the absence of a primary key does not in itself lead
to duplicates at some time in the future but there is a risk. On the
other hand, having a primary key guarantees there will never be
duplicates. Which would you recommend?

If you haven't experienced any integrity issues when defining your data
integrity constraints utilizing what Jet 3.51 and Access Forms offer,
the chances are you didn't ask someone like me to test it.

Put up defences now rather than wait for it to be broken for want of
defences later. Adding a belt won't break the braces.

Jamie.

--

 

[Jamie Collins]

FWIW I get ANSI-92 mode out
of the box in Access2007

From the online Access 2003 help:

About ANSI SQL query mode (MDB)
http://office.microsoft.com/en-us/assistance/HP030704831033.aspx

"The ANSI SQL query mode default for new Access databases in 2002 -
2003 and 2000 file formats"

Although that doesn't read like a proper sentence and this help as a
whole is notorious for inaccuracies, it does suggest that ANSI-92 mode
is already the out of the box setting.

Jamie.

--

 

[RoyVidar]

About ANSI SQL query mode (MDB)
http://office.microsoft.com/en-us/assistance/HP030704831033.aspx

"The ANSI SQL query mode default for new Access databases in 2002 -
2003 and 2000 file formats"

Although that doesn't read like a proper sentence and this help as a
whole is notorious for inaccuracies, it does suggest that ANSI-92
mode is already the out of the box setting.

Jamie.

When clicking the header you copy/pasted, you'll find which ANSI mode
is
default per the different file formats.

"ANSI-89 is the default setting for a new Access database in 2002 -
2003
and 2000 file format."

I thought ANSI-89 format denoted standard "Access dialect", and ANSI-92
what we've discussed?

I think this suggests ANSI-92 mode isn't an out of the box setting in
2002/2003. I don't know anything about 2007.

 

[Jamie Collins]

I thought ANSI-89 format denoted standard "Access dialect", and ANSI-92
what we've discussed?

Agreed, though I'd prefer 'traditional' (as per the Access 2003 help)
in place of your word 'standard' which has potentially misleading
connotations.

Apologies, the confusion is mine. I think I have been mistaking the use
of 'ANSI SQL query mode' to mean 'ANSI-92 mode'.

I think this suggests ANSI-92 mode isn't an out of the box setting in
2002/2003.

I think you are correct. It's a confusing issue <g>.

Jet 4.0 SQL syntax, although including some full SQL-92 features (e.g.
cascade update on referential actions and subqueries in check
constraints are notable), doesn't fully comply with entry level SQL-92
(Jet's proprietary UPDATE syntax is notable here).

Jet 3.51 SQL syntax doesn't always resemble ANSI-89 syntax (OUTER JOIN
is notable here).

Jamie.

--

 

[RoyVidar]

You know you can create a CHECK constraint in the interface of Access
while in ANSI-92 mode. Therefore, check constraints *can* be achieved
within the context (e.g. ADO) and interface (e.g. ANSI-92 mode) of
Access.

I wasn't aware of that. Where in the interface can you do that? Or do
you mean that the interface allows you to execute DDL to that effect,
in
stead of having to execute through ADO/OLE DB

If we only had Access 2000 and the Jet 4.0 goodies were only
available via ADO I can see it would be difficult to convince a
long-time Access user to utilize them. However, we have Access 2002
and Access 2003 with ANSI-92 mode (previously known as 'ANSI query
mode').

Which of my 'Jet suggestions' are not supported by Access while in
ANSI-92 mode? I can't think of any.

Swithching to ANSI-92 mode in an old Access app, in itself might answer
some of that question. This will break the "Access SQL dialect", but
whether or not that's a bad thing, belongs to another discussion ;-),
but it might also break existing queries. More "amusing", and unhealthy
for employment, is possible consequenses of executing queries doing
calculations, due to differences in execution between the modes.

Even in ANSI-92 mode, the behaviour when copying tables are different
when you're using the unsupported Jet feature check constraints than
when not. For those using such methods for maintenance, backup,
recovery, updates... this is broken when check constraints are
utilized.

I can't bring myself to call it supported by Access. Sure you can run
DDL, but in a visual/graphic interface like Access, I wouldn't call
such
settings supported unless you can find it somewhere in the UI and be
able to select it, alter or delete it. I haven't found such. Like, if
you fire off a check constraint on SQL server, then you will find that
constraint listed in the UI of most tools you use. For instance in SQL
Server Management Studio Express, it is a property/folder of the table,
in which you can add, alter and delete it through the UI (and, there's
a
wizard).

ADO is kind of "standard", and we were exposed to massive marketing
some
time ago, but one might question the success of this, since it seems
ADO
is frowned upon when working with Access/Jet, the preference is the
more
native DAO. In the following branch of a thread (where you also
participated elsethreads), some of these views and reservations are
expressed in relation to some ADO/DDL.

http://groups.google.com/group/comp..._frm/thread/60421a637345a4f9/31dc17a7dcd80b6c

Then I'm going to aim a bit low. I've read several of your replies in
these NGs, and it seems some of the suggestions you've given, might
break some of the reasons for choosing Access. Here's one sample with a
"groan" ;-)

http://groups.google.com/group/microsoft.public.access.tablesdbdesign/msg/df29dea99f93f956

If using check constraints, and implementing engine level validation
and
constraints means to toss bound forms overboard, then it also means
tossing Access overboard as frontend.

Bound forms are a core functionality or Access, it's what Access is
designed for, and what makes Access the brilliant RAD tool it is. If
going unbound is what one has to do to be able to use engine level
validation, then using these kind of constraints rules out Access as
front end. This is what I've been trying to say - I perceive it to be a
mismatch between your enthusiasm for engine level stuff and usage of
the
product for which this NG is dedicated. Stuffing everything into the
engine will either force users to become scripting masochists - and
hey,
aren't we living in the time of graphic intefaces now - or that they
just throw Access out with the bathwater. You are a bit more to the
point in this comment

http://groups.google.com/group/microsoft.public.access/msg/d3b7b020c625ff8b

<ot>I think you can do pretty much what you ask for there with ADO form
recordset within a transaction and 2002 and later versions, but you
probably know about that</ot>

So, unless there are interesting changes in the 2007 version, I don't
foresee a rush among Access developers to utilze check constraints.

If Jet 4.0 is not for
Access users then who?

Jet 4.0 is a very good backend database for a lot of different
purposes.
Connecting to a Jet database from Word, Excel, from systems built with
(Classic) VB, VB.Net, ASP, C#, different script technologies and all
the
things I've forgotten... There are quite a lot of Jet databases out
there where the main means of usage are through technologies that has
nothing to do with Access.

In that context, I too utilize features of the engine that aren't
supported by Access in default operating mode, since basically, in this
context Access isn't involved in normal day to day operation so
whatever
limitations Access might have aren't relevant.

That is a misstatement, presumably unintentional.

Yup - lack of language skills

You picked a good example. I think many people who use this setting
would be surprised it will not prevent:

INSERT INTO (my_text_col) VALUES ('');

It does prevent that on my setups (2000/2002/2003), unless I've
misunderstood.

I'd be happy to know under which circumstances it has problems. Here's
test code with the excact same result on 2000, 2002 and 2003, in the
latter two, regardless of ANSI mode ;-)

Sub ZLSPropertyTest()

Dim cn As ADODB.Connection

Set cn = CurrentProject.Connection
'cn.Execute "DROP TABLE zlsTest"
cn.Execute "CREATE TABLE zlsTest (" & _
"id Int Identity PRIMARY KEY, " & _
"testText Varchar(25) NOT NULL UNIQUE)"

With CreateObject("ADOX.Catalog")
Set .ActiveConnection = cn
.Tables("zlsTest").Columns("testText").Properties( _
"Jet OLEDB:Allow Zero Length").Value = False
End With

On Error Resume Next
DBEngine(0)(0).Execute "INSERT INTO zlsTest (testText) " & _
"VALUES ('')", dbFailOnError
Debug.Print Err.Number, Err.Description
Err.Clear
cn.Execute "INSERT INTO zlsTest (testText) " & _
"VALUES ('')"
Debug.Print Err.Number, Err.Description
Err.Clear

End Sub

Immediate pane shows

3315 Field 'zlsTest.testText' cannot be a zero-length string.
-2147467259 Field 'zlsTest.testText' cannot be a zero-length string.

 

[RoyVidar]

Agreed, though I'd prefer 'traditional' (as per the Access 2003 help)
in place of your word 'standard' which has potentially misleading
connotations.
Agree.

Apologies, the confusion is mine. I think I have been mistaking the
use of 'ANSI SQL query mode' to mean 'ANSI-92 mode'.

No, I should apologize, this was really a cheap shot from me, I've
"suffered" more or less the same confusion too, but couldn't resist
posting, sorry.

 

[Jamie Collins]

I wouldn't call [CHECK constraints] supported
unless you can find it somewhere in the UI and
be able to select it, alter or delete it.

Let's face it, the existing Access 'table design view' (including
Access 2007) is flawed in this respect anyhow.

You can only create one Validation Rule per field/column. In real life,
I tend to want to test many individual rules at the column level. For
example, validation for an email address includes length (<= 255), the
set of legal characters, the presence of exactly one commercial at, the
length of the 'mailbox' portion, and the length and pattern of the
'domain' portion. Writing one rule for each makes maintenance easier. I
want to give helpful feedback by stating which rule has not been
satisfied. Using a field/column level Validation Rule/Text mean I must
roll all rules into one long rule and give unhelpful feedback, both of
which makes maintenance harder.

And if one rule per field/column is a problem, record/row level
Validation Rule is even worse!

BTW why are record/row level Validation Rules referred to as table
level? This is most unhelpful. I can see that it is defined in the
table's properties. The Help does not help, either: "Unlike field
validation rules, record validation rules can refer to other fields in
the same table." Wrong mental model, of course. A row/record is made up
of columns, not the other way around, so it would read better as
"...other fields in the same record". What would a validation rule that
can refer to values in other records/rows be known as? Table level, of
course!

So if a truly table level Validation Rule was introduced in the UI,
where would they put it to make it distinct from a record/row level
one? If multiple Validation Rules were to be permitted, as they surely
must be, at each level the interface would need to be significantly
redesigned. Again, I don't think there are funds for this kind of
investment and, again, perhaps the assumption is that the UI will be
tailored to beginners, for whom one rule is fine, and make the more
advanced stuff is available via DDL, DAO, etc.

Are you implying you want to see a combined expression builder/query
designer GUI to build a column/row/table level CHECK constraint? That
would be quite a beast! Let's face it, the most powerful aspect of
Jet's implementation of CHECK constraints is the ability to use
subqueries and the existing Access query designer GUI sucks at writing
subqueries. I guess the decision was made that if you want CHECK
constraints you will have to be proficient in Jet SQL; also, I guess
there has been no cash to fund an improved query designer with improved
subquery support (I imagine the same argument: if you want subqueries
it is most likely you are proficient in Jet SQL anyhow).

Looking at constraints generally, the Access UI isn't very good at
revealing them, IMO. There is a quick visual way of seeing whether a
column is part of the PK but the same does not hold true for a UNQIUE
constraint (yet I am repeatedly reminded that Jet implements UNIQUE
constraints via indexes, something which is of minority interest).

Another aspect is that the Access UI lags behind Jet development even
though the development of the latter ceased 5+ years (and three
releases of Access) ago...

the following branch of a thread (where you also
participated elsethreads), some of these views and
reservations are expressed in relation to some
ADO/DDL.
http://groups.google.com/group/micr...esdbdesign/browse_frm/thread/2b8601b4391581d/

I'm glad you linked to that thread.

<OT> Did you note Allen Browne's 'you are dead to me' attitude? What's
my crime? Pointing out that Jet 4.0 features are indeed useable in the
Access UI! </OT>

As you too participated in this thread the, why didn't Allen Browne get
the backlash I'm getting here? The issue seems to be the same i.e.
obscurity of Jet 4.0 features due to lack of support in Access. Allow
me to quote from the Allen Browne article:

Cascade to Null Relations by Allen Browne
http://allenbrowne.com/ser-64.html

<quote>
There is now a way to get JET (the data engine in Access) to
automatically set the LetterID back to Null... Cascade-to-Null was
introduced six years ago, but has remained below the radar for most
developers...But what if someone else needs to rebuild the database at
some stage? Since the interface cannot show them that cascade-to-null
relations are in force, they may recreate the tables and have no idea
that your application relies on this type of cascade.
</quote>

The author goes on to propose a way of documenting the existence of ON
UPDATE SET NULL which is... well, I don't want to appear rude; suffice
to say it has 'kludge' written all over it <g>.

<OT> Why didn't I post my reservations about this article e.g.
'relations' in a SQL DBMS, that CASCADE and SET NULL are mutually
exclusive, the usage case being questionable, that elsewhere he's said
he considers ADOX to be so buggy it's unusable, etc? Well, I'm a
political lobbyist and I want to concentrate my efforts on getting
Allen Browne to review his years-old yet popular debunking of the
DECIMAL data type (see
http://groups.google.com/group/microsoft.public.access.tablesdbdesign/msg/039f62de8d903224
ad nauseam), one of the Jet 4.0 features that is better implemented in
Access. It would not help the cause to appear as though I've got it in
for the guy (I truly have not) or to dilute or distract from the main
issue.</OT>

To me, Mr Browne's words (above) sound a bit like something you've said
in this thread but with a different conclusion i.e. Allen Browne
concludes that lack of support in Access is something that can be
tolerated if the Jet 4.0 feature is desirable.

Jamie.

--

 

[Jamie Collins]

I think many people who use [AllowZeroLength]
would be surprised it will not prevent

I'd be happy to know under which circumstances it has problems.

Correction. The surprise is all mine.

Jamie.

--

 

[Jamie Collins]

Yup - lack of language skills

Please, then, restate your point. Either language - Jet SQL or English
- will do <g>; using my 'salary history' example would be ideal.

TIA,
Jamie.

--

 

[Brendan Reynolds]

You know, Jamie, if you could just stop trying to be such a smart-ass you
could be a valued member of this community. Otherwise, you're just going to
find your way into lots of kill-files along with the rest of the trolls.
It's up to you.

 

[Jamie Collins]

Swithching to ANSI-92 mode in an old Access app, in itself might answer
some of that question. This will break the "Access SQL dialect", but
whether or not that's a bad thing, belongs to another discussion ;-),
but it might also break existing queries.

I encourage people to write SQL to take account of, and preferably
service, both ANSI modes of operation. This is especially important for
data integrity constraints e.g. Validation Rules in Access; no use
writing constraints that can be circumvented by switching between ADO
and DAO connections.

More "amusing", and unhealthy
for employment, is possible consequenses of executing queries doing
calculations, due to differences in execution between the modes.

What did you have in mind here?

The only thing I can think of is native decimals e.g.

SELECT UnitPrice * 1.175 AS UnitPrice_incl_VAT
FROM Products;

will coerce the value to Double in Jet 3.51 and Decimal in Jet 4.0 (the
above is an example of sloppy casting in Jet 3.51, of course). However,
I think this is directly related to the engine/provider rather than the
ANSI query mode.

Jamie.

--

 

[Jamie Collins]

Jet 4.0 is a very good backend database for a lot of different
purposes.
Connecting to a Jet database from Word, Excel, from systems built with
(Classic) VB, VB.Net, ASP, C#, different script technologies and all
the
things I've forgotten

Where do these people go from advice about the Jet engine? Is there,
for example, a Jet 4.0 newsgroup? Should the people who post questions
that are *clearly* about Jet rather than Access be politely advised to
post their questions elsewhere? I don't think that is right; I don't
even seek to correct their usage of 'Access' (well, I don't anymore
<g>) unless I think there is value in making a distinction.

Jamie.

--

 

[Jamie Collins]

Thank you, Brendon.

If you would please take the time to point out where in my post I have
been a 'troll' or a 'smart alec' then I will strive to alter my
approach accordingly. I will listen to your comments without replying
myself.

Many thanks,
Jamie.

--

 

[Jamie Collins]

Then I'm going to aim a bit low. I've read several of your replies in
these NGs, and it seems some of the suggestions you've given <<snipped>>

Ah. Only after several replies to your long post did I spot this. Kinda
changes thing a bit. Is my face red...