Commerical App ....but no relationships??? Normal??

[Mal]

Hi,

As I gain knowledge through a lot of trial, error, and usenet posts..
I have a potentially odd question.

I am using a commercial access application.
It is a front-end / back end....multi-user app. with a lot of complex
functionality (as you would expect from a commercial app).

It doesn't have any relationships though.....is this odd???

Neither the back end, or the front end (linked, local and temp tables)
have any relationships defined.

Mostly a random question as I ponder database (and specifically
ACCESS) design issues.

Mal.

[Albert D. Kallal]

Two things:

1) I is possible that the ER window is not setup correctly, and the
relations exist..but you can't see them (you might want to drop some tables
into he diagramming screen, and see if any join lines appear. Also, give the
show tables option all a try).

So, often the tables and joins are setup..but just not DISPLAYED on the
relationships window.

Post by Mal
Neither the back end, or the front end (linked, local and temp tables)
have any relationships defined.

The above is horrible, and I find it hard to believe.

There are a number of good developers I know that do NOT rely on the
Referential Integrity options of the ms-access file data engine (JET).

What this means is that often some developers will actually do their own
child deletions, and then delete the parent record. So, if for example you
delete a customer, then all of the invoices are automatically deleted if you
use relations. Some developers will actually write the code to delete the
invoices and THEN delete the customer. The reasoning behind this is
increased reliability in case a index is bad (on the other hand, if that
index is bad..then some of those child records might be missed anyway!).
However, I 100% rely on the JET engine to do my child deletes, as it saves
me coding. Most good developers do. I never had a problem.

However, the fact that some developers want to write their own deleting code
DOES NOT IN ANY WAY negate that fact that the relationships need to be
defined, and enforcements should be set. In fact, as mentioned, I find this
VERY hard to believe.

About the only possible thing or reason here could be that the developers
want to hide the design. Since even if the developers write their own
deleting code...it still makes sense to enforce RI in case a mistake, or
some code does not the correct deleting.

Many of you don't realize, that the very Popular Simply Accounting package
actually uses ms-access database format, and the same ms-access JET engine.
They also choose NOT to enforce RI, and the relationships window does NOT
show any relations. I not sure why this is so, but it does NOT make sense as
a general course of development. I would love to hear the reasons behind
leaving out such a important detail, and some good designed commercial
products based on the ms-access database do NOT have relationships setup, or
any RI enforced.


Albert D. Kallal (Access MVP)
Edmonton, Alberta Canada
***@msn.com
http://www.attcanada.net/~kallal.msn

[XMVP]

Post by Albert D. Kallal
I would love to hear the reasons behind
leaving out such a important detail....

There's only ONE reason: Idiocy.

[Steve]

On Sat, 26 Jun 2004 22:30:02 GMT, "Albert D. Kallal"
<***@msn.com> wrote:

<snip>

Post by Albert D. Kallal
They also choose NOT to enforce RI, and the relationships window does NOT
show any relations. I not sure why this is so, but it does NOT make sense as
a general course of development. I would love to hear the reasons behind
leaving out such a important detail, and some good designed commercial
products based on the ms-access database do NOT have relationships setup, or
any RI enforced.

First of all, I always enforce RI via JET, regardless of where I place
other validation/business rules.

But, I have discussed this issue of where to place
RI/validation/business rules in database applications, and there is
some logic for taking RI out of the database and placing it in a
middle-tier or the front-end piece of the application.

Performance - you may want to detect an RI error prior to hitting the
database with a save/update/deletion.

Redundancy - you may not want to have RI rules duplicate in both the
database and the front-end/middle-tier. Therefore performance plus
eliminating rdundancy may push you to take the RI out of the database.

Consistency - you may want all the RI/validation/business rules
located together. I believe that it is better to push such rules
closer to the database (e.g. use stored procedures, triggers), but
performance and transportability may dictate pushng such rules out of
the database.

Transportability - using the database to only store data/indexes makes
it easier to move to other databases. Also, if the data and business
rules are combined in the server, there is an issue of upgrading the
business rules, and providing your customers access to the database.

OOP - base on my limited discussions with OOP developers, and my more
limited understanding of OOP, it appears that many of the business
rules that would be captured in the classes (e.g. properties and
methods) would be duplicated in the database (triggers and
procedures). This would be redundant and lead to maintenance and
other issues.

This is my best shot of defending a practice that I don't participate
in.

However, there is one application that I may work on whereby there are
significant "on-the-fly" calculations required. It may be better to
read all the data into arrays, and work off the arrays. Data may then
be save to the server using whatever data engine the client wants,
from ASCII to SQL Server. In this case, putting RI or any other rules
in the database would be useless. However, I doubt that I would use
Access to write this application, but I may store the data in MSDE or
JET to enable the client to pull off data, as required.


Steven R. Zuch, CPA
Principal
Cogent Management Inc.

[XMVP]

Post by Steve
On Sat, 26 Jun 2004 22:30:02 GMT, "Albert D. Kallal"
<snip>

Post by Albert D. Kallal
They also choose NOT to enforce RI, and the relationships window does NOT
show any relations. I not sure why this is so, but it does NOT make sense as
a general course of development. I would love to hear the reasons behind
leaving out such a important detail, and some good designed commercial
products based on the ms-access database do NOT have relationships setup, or
any RI enforced.

First of all, I always enforce RI via JET, regardless of where I place
other validation/business rules.
But, I have discussed this issue of where to place
RI/validation/business rules in database applications, and there is
some logic for taking RI out of the database and placing it in a
middle-tier or the front-end piece of the application.
Performance - you may want to detect an RI error prior to hitting the
database with a save/update/deletion.
Redundancy - you may not want to have RI rules duplicate in both the
database and the front-end/middle-tier. Therefore performance plus
eliminating rdundancy may push you to take the RI out of the database.
Consistency - you may want all the RI/validation/business rules
located together. I believe that it is better to push such rules
closer to the database (e.g. use stored procedures, triggers), but
performance and transportability may dictate pushng such rules out of
the database.
Transportability - using the database to only store data/indexes makes
it easier to move to other databases. Also, if the data and business
rules are combined in the server, there is an issue of upgrading the
business rules, and providing your customers access to the database.
OOP - base on my limited discussions with OOP developers, and my more
limited understanding of OOP, it appears that many of the business
rules that would be captured in the classes (e.g. properties and
methods) would be duplicated in the database (triggers and
procedures). This would be redundant and lead to maintenance and
other issues.
This is my best shot of defending a practice that I don't participate
in.
However, there is one application that I may work on whereby there are
significant "on-the-fly" calculations required. It may be better to
read all the data into arrays, and work off the arrays. Data may then
be save to the server using whatever data engine the client wants,
from ASCII to SQL Server. In this case, putting RI or any other rules
in the database would be useless. However, I doubt that I would use
Access to write this application, but I may store the data in MSDE or
JET to enable the client to pull off data, as required.
Steven R. Zuch, CPA

Ha ha ha ha. No wonder you're a CPA.

[David W. Fenton]

Post by Steve
But, I have discussed this issue of where to place
RI/validation/business rules in database applications, and there
is some logic for taking RI out of the database and placing it in
a middle-tier or the front-end piece of the application.

RI is part of the data structure.

It belongs at the engine level.

There is no valid argument for doing anything else, except one that
mis-defines what the data structure is.

[Steve]

On Sun, 27 Jun 2004 19:05:59 GMT, "David W. Fenton"

Post by David W. Fenton

Post by Steve
But, I have discussed this issue of where to place
RI/validation/business rules in database applications, and there
is some logic for taking RI out of the database and placing it in
a middle-tier or the front-end piece of the application.

RI is part of the data structure.

Agreed.

Post by David W. Fenton
It belongs at the engine level.

Agreed, except when there are valid reasons not too.

Post by David W. Fenton
There is no valid argument for doing anything else, except one that
mis-defines what the data structure is.

Wrong. Performance issues are clearly one of them. If you haven't
run into them, then you don't have any issues.

However, if one is using Access/Jet, I have never run into any project
that did not dictate using RI in Jet, except for the obvious one ...
which is RI can not be established via Jet between attached tables
stored in different MDBs.

Steven

Post by David W. Fenton
--
David W. Fenton http://www.bway.net/~dfenton
dfenton at bway dot net http://www.bway.net/~dfassoc

[David W. Fenton]

Post by Steve
On Sun, 27 Jun 2004 19:05:59 GMT, "David W. Fenton"

Post by David W. Fenton
There is no valid argument for doing anything else, except one
that mis-defines what the data structure is.

Wrong. Performance issues are clearly one of them. If you
haven't run into them, then you don't have any issues.

Performance is *never* a reason to sacrifice data integrity. Having
RI enforced in the app instead of by the database engine means
you're eventually going to sacrifice data integrity.

Failing to enforce RI at the engine level will, under all
circumstances, eventually lead to corrupt data.

[Larry Linson]

Post by David W. Fenton
Performance is *never* a reason to sacrifice
data integrity. Having RI enforced in the app
instead of by the database engine means
you're eventually going to sacrifice data integrity.

Yes, it is a time-honored axiom of the computer business: "If it doesn't
work correctly, it doesn't matter how fast it is".

[Tom van Stiphout]

On Mon, 28 Jun 2004 21:28:14 GMT, "Larry Linson"
<***@localhost.not> wrote:

Amen.
In addition to providing storage for data, a database's responsibility
is to preserve the integrity of that data. The business apps most of
us are writing need that MUCH more than a bit of extra speed.

-Tom.

Post by Larry Linson

Post by David W. Fenton
Performance is *never* a reason to sacrifice
data integrity. Having RI enforced in the app
instead of by the database engine means
you're eventually going to sacrifice data integrity.

Yes, it is a time-honored axiom of the computer business: "If it doesn't
work correctly, it doesn't matter how fast it is".

[Steve]

Part of my post that David snipped out:

"However, if one is using Access/Jet, I have never run into any
project that did not dictate using RI in Jet, except for the obvious
one ... which is RI can not be established via Jet between attached
tables stored in different MDBs."

Not only did he not place any notation that he was editing the post,
he did not address the fact that RI can NOT be enforced between
attacjed tables stored in different MDBs.


On Mon, 28 Jun 2004 21:02:39 GMT, "David W. Fenton"

Post by David W. Fenton

Post by Steve
On Sun, 27 Jun 2004 19:05:59 GMT, "David W. Fenton"

Post by David W. Fenton
There is no valid argument for doing anything else, except one
that mis-defines what the data structure is.

Wrong. Performance issues are clearly one of them. If you
haven't run into them, then you don't have any issues.

Performance is *never* a reason to sacrifice data integrity. Having
RI enforced in the app instead of by the database engine means
you're eventually going to sacrifice data integrity.
Failing to enforce RI at the engine level will, under all
circumstances, eventually lead to corrupt data.

Wrong. It has been done in many professional products over the years.
How many systems relied on Btrieve, or dBASE file formats, which
engines did not enforce referential integrity?. I did it in some of
my old dBASE/Fox systems.

And example of when to turn of RI in the database engine, are high
performance systems that are extremely calculation based, which loads
all the data into memory for updating and processing, and then dumps
it all back to the disk. RI MUST be maintained in the app; maintaining
it in the database is duplicative, and serves no purpose of all.

But as I said before, when working with Access/Jet, I have always
validated RI at the JET level unless I was unable to do so.

Steven

[John Winterbottom]

Post by Steve
"However, if one is using Access/Jet, I have never run into any
project that did not dictate using RI in Jet, except for the obvious
one ... which is RI can not be established via Jet between attached
tables stored in different MDBs."
Not only did he not place any notation that he was editing the post,
he did not address the fact that RI can NOT be enforced between
attacjed tables stored in different MDBs.

While he never actually answers any questions here, Mr Fenton nevertheless
fulfils a role; which is that of cdma-terrier. Sniffing his way in and out
of threads, he looks only for the opportunitiy to bite someone on the
ankle. Unfortunately this is where the metaphor breaks down, because you
can't just give him a good kick to send him on his way.

If you ignore him he will trot off to yap at someone else :-)

[rkc]

Post by John Winterbottom

Post by Steve
"However, if one is using Access/Jet, I have never run into any
project that did not dictate using RI in Jet, except for the obvious
one ... which is RI can not be established via Jet between attached
tables stored in different MDBs."
Not only did he not place any notation that he was editing the post,
he did not address the fact that RI can NOT be enforced between
attacjed tables stored in different MDBs.

While he never actually answers any questions here, Mr Fenton nevertheless
fulfils a role; which is that of cdma-terrier. Sniffing his way in and out
of threads, he looks only for the opportunitiy to bite someone on the
ankle. Unfortunately this is where the metaphor breaks down, because you
can't just give him a good kick to send him on his way.
If you ignore him he will trot off to yap at someone else :-)

Somebody should tell that to the Filemaker people.

[XMVP]

Post by John Winterbottom
While he never actually answers any questions here, Mr Fenton nevertheless
fulfils a role; which is that of cdma-terrier. Sniffing his way in and out
of threads, he looks only for the opportunitiy to bite someone on the
ankle. Unfortunately this is where the metaphor breaks down, because you
can't just give him a good kick to send him on his way.

Big difference: With a dog bite the worst you can get is rabies.

[David W. Fenton]

Post by Steve
On Mon, 28 Jun 2004 21:02:39 GMT, "David W. Fenton"

[]

Post by Steve

Post by David W. Fenton
Failing to enforce RI at the engine level will, under all
circumstances, eventually lead to corrupt data.

Wrong. It has been done in many professional products over the
years. How many systems relied on Btrieve, or dBASE file formats,
which engines did not enforce referential integrity?. I did it in
some of my old dBASE/Fox systems.

How can you ever know for certain that the data have not been
corrupted?

Without engine-level RI, you can never know that your data is *not*
corrupted. That state of uncertainty seems to me to be pretty much
equivalent to "eventually lead[s] to corrupt data."

It's been at least 10 years since there was a major db engine that
did not offer engine-level RI enforcement. The examples you give are
legacy systems from back in the days when we had no choice in the
matter.

I started my db programming days as a Paradox programmer, before
Pdox had engine-level RI, so I know *exactly* what can go wrong
without engine-level RI.

And I've worked with MySQL programmers who've put their RI
enforcement in the application (with a version of MySQL that did not
offer the InnoDB tables as an option). In one instance, the host of
the website upgraded the version of PHP and the behavior of the app
changed, causing it to insert records into a child table with a
foreign key set to 0. When these records were imported into my
Access app (with RI enforced), they failed, because they violated RI
(no 0 parent record).

Even though the application was enforcing RI, the data got corrupted
anyway, because the behavior of the application changed when running
under a different version.

To me, there is simply no scenario where data with relationships
should not have RI skipped.

None.

[Steve]

On Wed, 30 Jun 2004 18:12:22 GMT, "David W. Fenton"

Post by David W. Fenton

Post by Steve
On Mon, 28 Jun 2004 21:02:39 GMT, "David W. Fenton"

[]

Post by Steve

Post by David W. Fenton
Failing to enforce RI at the engine level will, under all
circumstances, eventually lead to corrupt data.

Wrong. It has been done in many professional products over the
years. How many systems relied on Btrieve, or dBASE file formats,
which engines did not enforce referential integrity?. I did it in
some of my old dBASE/Fox systems.

How can you ever know for certain that the data have not been
corrupted?

I can't know for certain. I can't know for certain that other
essential validation tests which can't be stored in the database are
not being violated either.

Post by David W. Fenton
Without engine-level RI, you can never know that your data is *not*
corrupted. That state of uncertainty seems to me to be pretty much
equivalent to "eventually lead[s] to corrupt data."

No, it is not equivalent. Or it means that every system that has ever
been designed using a database engine that does not enforce RI has
lead to corrupted data. How can you even attempt to prove that?

Post by David W. Fenton
It's been at least 10 years since there was a major db engine that
did not offer engine-level RI enforcement. The examples you give are
legacy systems from back in the days when we had no choice in the
matter.

I did not intend to imply that dBASE or Btrieve are modern db engines.
The point is, that RI was not required for such systems to work as
desired.


<SNIP>

Post by David W. Fenton
To me, there is simply no scenario where data with relationships
should not have RI skipped.
None.

Most. Again, I always place RI in JET, when I can.

Steven

[Steve]

Just as an aside, do people place as much of the validation tests as
possible at the db engine level, or pretty much keep such business
rules in code.

Steven

[XMVP]

Post by Steve
Just as an aside, do people place as much of the validation tests as
possible at the db engine level, or pretty much keep such business
rules in code.

That proves it: You're just as clueless as Fenton.

[Larry Linson]

Post by XMVP
That proves it: You're just as clueless as Fenton.

But, not at all in the category you claim for yourself in your e-mail
address. BTW, if you were "as clueless as Fenton", you'd be darn clever --
sorry that isn't the case.

[Lyle Fairfield]

<snips> BTW, if you were "as clueless as Fenton", you'd be darn clever --
sorry that isn't the case.

The new ideas and innovative solutions prsented by David Fenton in this
newsgroup are countless.

[Larry Linson]

Post by Lyle Fairfield
The new ideas and innovative solutions
prsented by David Fenton in this
newsgroup are countless.

On the other hand (pun intended), the new ideas and innovations presented in
this newsgroup by the troll and sockpuppets are easily countable, even for
someone who had a terrible accident and lost all the fingers on the counting
hand.

[Lyle Fairfield]

Post by Larry Linson

Post by Lyle Fairfield
The new ideas and innovative solutions
presented by David Fenton in this
newsgroup are countless.

On the other hand (pun intended), the new ideas and innovations
presented in this newsgroup by the troll and sockpuppets are easily
countable, even for someone who had a terrible accident and lost all the
fingers on the counting hand.

You are especially clever tonight, Larry.

May I take advantage of this acuity and ask:

Is this advertising?

*******************
*** begin quote ***

John,

I can rewrite your program for you for a very reasonable fee (guaranteed
not to be quite a ridiculous sum!!).

Contact me at my email address below.


--
PC Datasheet
Your Resource For Help With Access, Excel And Word Applications
***@pcdatasheet.com
www.pcdatasheet.com

*** end quote ***
*****************

[XMVP]

Post by Lyle Fairfield
Is this advertising?
*******************
*** begin quote ***
John,
I can rewrite your program for you for a very reasonable fee (guaranteed
not to be quite a ridiculous sum!!).
Contact me at my email address below.
--
PC Datasheet
Your Resource For Help With Access, Excel And Word Applications

Or this?

*** begin quote ***

John, email me to discuss options.
Use the email address spelled out in the signature below.

Allen Browne - Microsoft MVP.

[rkc]

Post by Lyle Fairfield
Is this advertising?
*******************
*** begin quote ***
John,
I can rewrite your program for you for a very reasonable fee (guaranteed
not to be quite a ridiculous sum!!).
Contact me at my email address below.
--
PC Datasheet
Your Resource For Help With Access, Excel And Word Applications
www.pcdatasheet.com
*** end quote ***
*****************
--
Lyle

It's more like ambulance chasing.

[Larry Linson]

Post by Lyle Fairfield
You are especially clever tonight, Larry.

Thank you, Lyle, your compliments are always appreciated.

Post by Lyle Fairfield
Is this advertising? . . .
<PC Datasheet / Steve Santus post snipped>

It is, arguably, a commercial solution to a question that was asked. It is,
arguably, advertising which is prohibited. I have privately informed Steve
that I thought he was "pushing the line" on advertising with long SIGs and
such responses. I think, here or elsewhere, I have publicly admonished him
in situations that were unarguably advertising.

All I can suggest, Lyle, is that if you can make no progress with him by
private e-mail, or public admonishment, or don't care to pursue either, you
can complain to his ISP. He gives enough decent technical answers that I
have not opted to complain.

All I can say is that he must need the work _very badly_ to be willing to
violate the rules and charter, or to come very close to violating them. I'd
be unlikely to hire someone who I could see was violating the rules... it
would make me wonder what other rules and/or regulations that person might
feel free to violate, as well.

The resident troll's example, tagging along after yours, however, simply
offered private contact... did not offer paid assistance.

[CDMA NEWS]

Post by Larry Linson
The resident troll's example, tagging along after yours, however, simply
offered private contact... did not offer paid assistance.

Private contact? Are you saying MVP Allen Browne offered a lap dance?

[John Winterbottom]

Post by Larry Linson

Post by Lyle Fairfield
You are especially clever tonight, Larry.

Thank you, Lyle, your compliments are always appreciated.

Post by Lyle Fairfield
Is this advertising? . . .
<PC Datasheet / Steve Santus post snipped>

It is, arguably, a commercial solution to a question that was asked. It is,
arguably, advertising which is prohibited. I have privately informed Steve
that I thought he was "pushing the line" on advertising with long SIGs and
such responses. I think, here or elsewhere, I have publicly admonished him
in situations that were unarguably advertising.

These ones went unadmonsihed Larry - they're all from 2004, perhaps you
missed them?

http://www.google.com/groups?hl=en&lr=&ie=UTF-8&safe=off&selm=gqXEc.21889%24bs4.15248%40newsread3.news.atl.earthlink.net

http://www.google.com/groups?hl=en&lr=&ie=UTF-8&safe=off&selm=kWzpc.8051%24zO3.2068%40newsread2.news.atl.earthlink.net

http://www.google.com/groups?hl=en&lr=&ie=UTF-8&safe=off&selm=VrQ0c.28332%24W74.24252%40newsread1.news.atl.earthlink.net

http://www.google.com/groups?hl=en&lr=&ie=UTF-8&safe=off&selm=m0n6c.7872%24CJ5.7274%40newsread2.news.atl.earthlink.net


Or do the rules only apply to some of us?

[Michael (michka) Kaplan [MS]]

Post by John Winterbottom
Or do the rules only apply to some of us?

Larry already stated the principle -- you have to balance the reply you make
with the level of helpful advice a person chooses to give when they are not
doing the thing in question. He has stated that he often sent mail privately
rather than publicly, so I am not sure what purpose a list serves unless you
have access to their private mailboxes and know the results in such cases?

I used to put a link to trigeminal.com in my sig which mentioned that I did
consulting but I honestly turned away over 95% of all jobs (in the end 99%?)
that were offered to me from newsgroup contacts -- the link was there for
the resources for developers, not the job prospects for me.

I am not saying "rules be damned" but the axe murderer and the jaywalker are
both rule breakers. Do you chase after both with the same level of passion?

[John Winterbottom]

Post by Michael (michka) Kaplan [MS]

Post by John Winterbottom
Or do the rules only apply to some of us?

Larry already stated the principle -- you have to balance the reply you make
with the level of helpful advice a person chooses to give when they are not
doing the thing in question. He has stated that he often sent mail privately
rather than publicly, so I am not sure what purpose a list serves unless you
have access to their private mailboxes and know the results in such cases?

Stephen Lebans, Duane Hookom, Tom Van Siphout, Lyle Fairfield, John Vinson.
Apart from being regular contributors to the access newsgroups what do these
people have in common? Give up? They've all complained about "PC" Steve.

Do you know how often he does this? Take a look

http://www.google.com/groups?as_epq=contact%20me&safe=off&ie=UTF-8&as_uauthors=PC%20Datasheet%20&lr=&hl=en

And while you're looking, note how many "admonishments" Larry made. I
counted zero but maybe you have better eyes than me, or more time.
Whatever - if Larry is emailing "Mr Sheet" it doesn't seem to be working.

In a few cases the slimeball, (Steve, not Larry), gives some lame answer as
a cover, but in most he drops even this minor pretence at legitimacy. It's
simply "I can help - contact me at my email address".

If we chase away the likes of Steve Dassin from the group, why oh why do we
allow this sleazy character here? Anyone?

[David W. Fenton]

Post by John Winterbottom
If we chase away the likes of Steve Dassin from the group, why oh
why do we allow this sleazy character here? Anyone?

Er, so far as I can see, nobody can chase anyone away.

If that were so, I'd be long gone, since I think I've been harassed
in the newsgroup an order of magnitude more than anyone else.

[Tony Toews]

Post by David W. Fenton
If that were so, I'd be long gone, since I think I've been harassed
in the newsgroup an order of magnitude more than anyone else.

One order of magnitude? No. Two or three orders of magnitude I'd say.

Tony
--
Tony Toews, Microsoft Access MVP
Please respond only in the newsgroups so that others can
read the entire thread of messages.
Microsoft Access Links, Hints, Tips & Accounting Systems at
http://www.granite.ab.ca/accsmstr.htm

[Larry Linson]

Post by John Winterbottom
If we chase away the likes of Steve Dassin
from the group, why oh why do we
allow this sleazy character here? Anyone?

John, if you feel I have been "falling down on the job", please feel free to
step in as "unofficial admonisher". It is true that I have been posting
enough elsewhere that I haven't been spending as much time here as I once
did. If you'd like, there are many, many copies of my 'gentle admonishment'
in the archives that you can feel free to use. If you do, be prepared for
Lyle, Don, and others to argue with what you are doing, for whatever
purposes they may have.

I think that my only admonishments to Steve Dassin were private, but I might
have made a comment or two in public. I've corresponded privately with Steve
on other subjects, too, and think you are seriously mistaken if you think
anyone could "chase him away" from where he did not want to be chased.

My only further suggestions are that (1) if you wish to append an
admonishment to each of PC Datasheet's posts that you find to be advertising
and offensive, I will certainly not flame you for it, and (2) if you think
the offense is sufficiently serious, politely complain to his ISP and news
server, including full headers and a reference to the charter in the FAQ. I
have not thought (1) would be productive and did not think his offenses
sufficiently serious for (2). After all, I did say they were _arguably_ a
commercial answer to a valid question.

Even so, you cannot 'chase him away'. He can ignore the admonishments
(though the people who might hire him may see them and think twice) and ISPs
are pretty easy to find.

PC Datasheet might make the case that it's not really different than
recommending Total Access Analyzer for listing unused objects in a database.
I regard it as different from a non-affiliated participant making such a
recommendation, but it's really very little different than the President of
FMS, Inc., doing so, as he has done here in the past.

Larry Linson

[Tony Toews]

Post by Steve
Just as an aside, do people place as much of the validation tests as
possible at the db engine level, or pretty much keep such business
rules in code.

I usually put them in code. I frequently find that sometime later I'll have to
enter a record programmatically for some reason or with very little data. And then
allow the user to fill in more data as time goes on.

So I now never use any field level validation other than RI.

Tony
--
Tony Toews, Microsoft Access MVP
Please respond only in the newsgroups so that others can
read the entire thread of messages.
Microsoft Access Links, Hints, Tips & Accounting Systems at
http://www.granite.ab.ca/accsmstr.htm

[David W. Fenton]

Post by Steve
Just as an aside, do people place as much of the validation tests
as possible at the db engine level, or pretty much keep such
business rules in code.

I actually put validation mostly in controls on forms, rather than
in fields, because I can do a better UI.

Actually, most often, I do both, and never give the table-level
validation rule a chance to fail. The table-level validation rules
are just there to prevent invalid data from being appended in some
automated fashion (or manually entered, or written by some other app
without my front-end validation).

But I admit that I'm a little sloppy on this -- I often leave the
validation rules out of the table (mostly because you can't use UDFs
there -- if UDFs were possible, I'd use them pretty rigorously).

[David W. Fenton]

Post by Steve
Not only did he not place any notation that he was editing the
post, he did not address the fact that RI can NOT be enforced
between attacjed tables stored in different MDBs.

I deleted what I was not replying to.

Tables stored in different MDBs are not part of the same data
structure, by definition.

And if your app has outgrown the limits of MDBs and forced you to
break the data into multiple MDBs, then you're storing your data in
the wrong format.

That's a design error, in my opinion, and that's the reason why you
can't uses RI, because your design is flawed in the first place.

[Steve]

On Wed, 30 Jun 2004 18:14:54 GMT, "David W. Fenton"

Post by David W. Fenton

Post by Steve
Not only did he not place any notation that he was editing the
post, he did not address the fact that RI can NOT be enforced
between attacjed tables stored in different MDBs.

I deleted what I was not replying to.

Then you should note that you snipped out part of the post.

Post by David W. Fenton
Tables stored in different MDBs are not part of the same data
structure, by definition.

Who's definition? Just because the tables are not contained in the
same physical file does not mean that they are not part of the same,
logical database.

Post by David W. Fenton
And if your app has outgrown the limits of MDBs and forced you to
break the data into multiple MDBs, then you're storing your data in
the wrong format.

That is not why I used two physical MDB's to store the data. It was a
specific design decision that best accomplished our goals.

Post by David W. Fenton
That's a design error, in my opinion, and that's the reason why you
can't uses RI, because your design is flawed in the first place.

You assume that my design was flawed without having no idea about the
design or the objectives.

No point to continue this.

Steven

[XMVP]

Post by Steve
No point to continue this.

You should've never started THIS.

As I said, ha ha ha ha. No wonder you're a CPA.

[Bernard Peek]

Post by David W. Fenton

Post by Steve
Not only did he not place any notation that he was editing the
post, he did not address the fact that RI can NOT be enforced
between attacjed tables stored in different MDBs.

I deleted what I was not replying to.
Tables stored in different MDBs are not part of the same data
structure, by definition.

That largely depends on your definition. Personally I try to work as
closely as possible to the logical data structure, which does not take
into account the storage location.

In principle your argument is correct, RI should always be applied as
close to the data as possible. The problem here is that Access doesn't
always permit it.

Post by David W. Fenton
And if your app has outgrown the limits of MDBs and forced you to
break the data into multiple MDBs, then you're storing your data in
the wrong format.

That's certainly one of the reasons why data may be held in different
places, but there could be any number of business constraints on where
and how data is stored.

Post by David W. Fenton
That's a design error, in my opinion, and that's the reason why you
can't uses RI, because your design is flawed in the first place.

That might be true, or it might not be.

[(Pete Cresswell)]

RE/

Post by Mal
It doesn't have any relationships though.....is this odd???

Have you done a "Show All Relationships" with the Relationships window open?

[Tom van Stiphout]

On Sun, 27 Jun 2004 00:08:27 GMT, "(Pete Cresswell)" <***@y.z> wrote:

Alternatively, execute this in the Immediate (Ctrl+G) window:
?currentdb.Relations.Count

-Tom.

Post by (Pete Cresswell)
RE/

Post by Mal
It doesn't have any relationships though.....is this odd???

Have you done a "Show All Relationships" with the Relationships window open?

[Tom van Stiphout]

On 26 Jun 2004 12:24:05 -0700, ***@hotmail.com (Mal) wrote:

If this is the out-of-the-box application, then no, this is very bad
and you should send it back for a refund.

If someone imported the tables in a new MDB at some point in time,
forgot the relationships, and you are now using that version, then
it's still very bad, but you can't blame the manufacturer.

-Tom.

Post by Mal
Hi,
As I gain knowledge through a lot of trial, error, and usenet posts..
I have a potentially odd question.
I am using a commercial access application.
It is a front-end / back end....multi-user app. with a lot of complex
functionality (as you would expect from a commercial app).
It doesn't have any relationships though.....is this odd???
Neither the back end, or the front end (linked, local and temp tables)
have any relationships defined.
Mostly a random question as I ponder database (and specifically
ACCESS) design issues.
Mal.

[John Winterbottom]

Post by Mal
Hi,
As I gain knowledge through a lot of trial, error, and usenet posts..
I have a potentially odd question.
I am using a commercial access application.
It is a front-end / back end....multi-user app. with a lot of complex
functionality (as you would expect from a commercial app).
It doesn't have any relationships though.....is this odd???
Neither the back end, or the front end (linked, local and temp tables)
have any relationships defined.
Mostly a random question as I ponder database (and specifically
ACCESS) design issues.

I've even seen this in some sql server and oracle apps. I think the theory
is that RI imposes a perf penalty. In reality this penalty is so slight as
to be insigificant. We *always* enforce RI and we do it as part of our
create table scripts. We also explictly name all referential and other
constraints according to our own naming convention. I wouldn't want to work
any other way.

[j.mandala]

Hi, Mal,

I actually ended up distributing an application without relationships
between the tables, but not for performance reasons. I must preface
this with the confession that I am a self-taught Access amateur, and
may be missing something.

I work at a college counseling center and originally distributed my
app to end users at other university counseling centers with
relationships defined between tables. They also had a license that
allows them to customize or alter the database any way they want,
(adding any sorts of objects they want), and particularly encouraging
them to write their own custom queries, etc. They are supposed to use
their own IT staff for support doing this.

Most of the users run it 'out of the box', without any changes. If
they stick with the front end, the referential integrity is enforced
by the forms through the use of combo boxes, lists, etc. You just
can't put in bad data. All my primary keys were autonumbers which were
used for foreign keys in other tables.

However, I was finding that other 'amateurs' would try to change data
in the underlying tables, often triggering all sort of cascading
deletes of all records relating to a client, all appointments of a
certain type, or any record linked with a particular staff member. I
removed the relations between the tables and have not received any
particular complaints since then. We have run the database at our site
for ten years and the front end seems to do a good job of keeping the
data intact.

Am I missing somethings in the way I am doing this?
Humbly,
Jim

[Bernard Peek]

Post by j.mandala
However, I was finding that other 'amateurs' would try to change data
in the underlying tables, often triggering all sort of cascading
deletes of all records relating to a client, all appointments of a
certain type, or any record linked with a particular staff member. I
removed the relations between the tables and have not received any
particular complaints since then. We have run the database at our site
for ten years and the front end seems to do a good job of keeping the
data intact.
Am I missing somethings in the way I am doing this?
Humbly,
Jim

You (or more likely your clients) could be missing some data.

If you remove a parent record with cascaded deletes then it also removes
the child records. If you remove it without cascaded delete then it
leaves orphan records. This might not be an improvement. Although it's
easier to fix the error (re-create the parent record) it can make errors
harder to find. The fact that you get fewer error reports is not in
itself a good thing. Far better to have fewer errors and more error
reports.

Ideally you would put some error trapping around the delete operation
"Do you really want to delete this record and 37,477,281 attached
records?"

[David W. Fenton]

Post by Bernard Peek
If you remove a parent record with cascaded deletes then it also
removes the child records. If you remove it without cascaded
delete then it leaves orphan records.

If you establish a relationship but do *not* turn on CASCADE
DELETES, you won't be able to delete the parent record unless the
child records have all been deleted first.

[David W. Fenton]

Post by j.mandala
However, I was finding that other 'amateurs' would try to change
data in the underlying tables, often triggering all sort of
cascading deletes of all records relating to a client, all
appointments of a certain type, or any record linked with a
particular staff member. I removed the relations between the
tables and have not received any particular complaints since then.
We have run the database at our site for ten years and the front
end seems to do a good job of keeping the data intact.
Am I missing somethings in the way I am doing this?

Yes. You can define relationships without defining cascading
deletes.

I don't allow cascading deletes on most relationships. It's only
when child records really are completely subordinate to the parent
that I'd cascade deletions, such as with items on an invoice.

Also, with Autonumber fields for your relationships, there's no
reason to have Cascade Update, since Autonumber fields cannot be
edited.

[Tony Toews]

Post by j.mandala
However, I was finding that other 'amateurs' would try to change data
in the underlying tables, often triggering all sort of cascading
deletes of all records relating to a client, all appointments of a
certain type, or any record linked with a particular staff member. I
removed the relations between the tables and have not received any
particular complaints since then. We have run the database at our site
for ten years and the front end seems to do a good job of keeping the
data intact.

I have an intense dislike for cascade deletes in Microsoft Access. And I don't like
cascade updates.
http://www.granite.ab.ca/access/cascadeupdatedelete.htm

Tony
--
Tony Toews, Microsoft Access MVP
Please respond only in the newsgroups so that others can
read the entire thread of messages.
Microsoft Access Links, Hints, Tips & Accounting Systems at
http://www.granite.ab.ca/accsmstr.htm

[David W. Fenton]

Post by Tony Toews

Post by j.mandala
However, I was finding that other 'amateurs' would try to change
data in the underlying tables, often triggering all sort of
cascading deletes of all records relating to a client, all
appointments of a certain type, or any record linked with a
particular staff member. I removed the relations between the
tables and have not received any particular complaints since then.
We have run the database at our site for ten years and the front
end seems to do a good job of keeping the data intact.

I have an intense dislike for cascade deletes in Microsoft Access.
And I don't like cascade updates.
http://www.granite.ab.ca/access/cascadeupdatedelete.htm

Actually, Tony, you can strengthen this statement:

If you are using an autonumber primary key in your tables then
cascade updates is a non issue because the user should never
even see the autonumber key.

It's not just that they'll never see them, it's that you can't under
any circumstances change the value of an AutoNumber field PK in a
record that has already been created, so, there can never be any
updates to cascade to related tables.

[Tony Toews]

Post by David W. Fenton

Post by Tony Toews
I have an intense dislike for cascade deletes in Microsoft Access.
And I don't like cascade updates.
http://www.granite.ab.ca/access/cascadeupdatedelete.htm

If you are using an autonumber primary key in your tables then
cascade updates is a non issue because the user should never
even see the autonumber key.
It's not just that they'll never see them, it's that you can't under
any circumstances change the value of an AutoNumber field PK in a
record that has already been created, so, there can never be any
updates to cascade to related tables.

Updated.

Thanks, Tony
--
Tony Toews, Microsoft Access MVP
Please respond only in the newsgroups so that others can
read the entire thread of messages.
Microsoft Access Links, Hints, Tips & Accounting Systems at
http://www.granite.ab.ca/accsmstr.htm

[XMVP]

Post by j.mandala
Am I missing somethings in the way I am doing this?

Yes, namely, you let the users into your tables.