To Split or not to Split

[Keith Wilby]

Keith,

At no point have I advised anyone not to split their database. I thought I
had made it fairly clear in my original post that I am advising that the
database should be split...

Ed Metcalfe.

That's no doubt true Ed but I still think that the statement "it's rarely
*necessary* to split the database" is wrong, that's all I'm saying, I'm not
trying to be confrontational.

Keith.

 

[David W. Fenton]

For the likes of the David Fenton's etc (whom I respect very
greatly and agree with BTW), it must be assumed that he has
actually TESTED the various options. Yet the averrance to
suggesting testing this or that alternative, might suggest they/he
never tried anything else themselves other than the "official
line"?

I have never created an Access application that was unsplit in
production use.

But I've taken over a lot of them when I was hired to fix the
problems the users were encountering.

So, yes, I've got plenty of experience with unsplit databases --
just not ones designed by *me*.

 

[Chris Mills]

in message

I have never created an Access application that was unsplit in
production use.

But I've taken over a lot of them when I was hired to fix the
problems the users were encountering.

So, yes, I've got plenty of experience with unsplit databases --
just not ones designed by *me*.

And very much appreciated, David.

My experience (of taking over unsplit databases) is limited to one in A2.0 (a
point-of-sale critical one for a retail outlet). There were no problems at all
(that is, there were plenty of problems, but none directly attributable to
being unsplit, or let's say I never proved such!)(it was split, for a reason
you would need to ask the deceased Batchelor of Engineering programmer, who
taught me a thing or two otherwise, by functions ie departments)

Since I don't write unsplit databases, my experience of that is naturally
limited. Slightly related, is that I HAVE run an A97 database, Front-End
SHARED on a server amongst 1/2 doz. NO PROBS. I don't recommend it, I just did
it on one site for the hell of it (and easier during initial "volatile"
development!)

Of course, you mentioned a big change after A97 (ie A2000 onwards). So far as
I've read, that's to do with the bad practice of modifying a design on-line ie
whilst live (barely possible, and thinks: where are the backups even if
possible?) But this is the first I can recall reading that such causes
"corruptions" (as distinct from just whilst trying to modify the design of a
live database!)

And whilst we're at it, let's mention (with the greatest respect and interest,
no sarcasm), Tom Wickerath backed up by posts from Peter Miller, that memo or
large object fields are apparently more prone to corruptions because the
actual data is contained on the "heap". That may be so. Some of my programs
are 90% memo fields, for reasons which are irrelevant here (well, because my
program is so general-purpose that it doesn't put any operator-limit within
reason), and, I claim, I suffer corruptions like anyone but not so much as,
for instance, Tom would claim experience of!

So. Are the untoward corruptions because they were unsplit, or split but
shared FE, or had too many memo fields, or were running A2000 or later, or on
a WAN, or whatever else you say it suffered from? You must be very old to have
experience of all of them!

(My most memorable anecdotal experiences of corruption is this: one or several
of my customers starts experiencing them perhaps daily, but no others do so
it's not the program or "layout" per se. It's...it's...never provable but
either a) they have a bad operator who aborts out of the program b) a subtly
corrupted FE or BE which nevertheless passes all tests c) a bad network or
errant PC or something. Anyway, I solved those sites through no fault of my
own, eventually they stabilised, so they either fired the errant personnel,
changed their computer or network, and I never did find out why they
stabilised because they were remote sites so I couldn't analyse it even if I
knew how. They already had all the std Access layout recommendations. Perhaps
some of the PC's weren't SP'd, but anyone claiming experience would at least
know that asking by telephone results in "honest lies".

Now, I don't for a moment recommend unsplit, (Tony Toews came up with the only
interesting reason), and neither do I read does Ed. But at the same time, it
seems to be the least of my worries. Are you saying the sites you took over
were suffering from corruptions which you solved by splitting? Did anything
else change concommitant with those changes which might have relevance? (eg
did you improve the code as well or PC's or network or something?) It is sooo
easy to be misled, including myself of course. Such as blaming corruptions on
unsplit with no adequately controlled experiment. Anecdotal evidence I
ackowledge as important however, because I have no direct evidence either way
either. If you do, then you haven't presented it beyond a generality.

My purpose, is that this stuff worries me, as I'm sure it does all Access
programmers, and that is all I mean.
Chris

 

[David W. Fenton]

[]

Since I don't write unsplit databases, my experience of that is
naturally limited. Slightly related, is that I HAVE run an A97
database, Front-End SHARED on a server amongst 1/2 doz. NO PROBS.
I don't recommend it, I just did it on one site for the hell of it
(and easier during initial "volatile" development!)

A97 was more tolerant of a shared front end, but I never did it
except as a stop-gap.

Of course, you mentioned a big change after A97 (ie A2000
onwards). So far as I've read, that's to do with the bad practice
of modifying a design on-line ie whilst live (barely possible, and
thinks: where are the backups even if possible?) But this is the
first I can recall reading that such causes "corruptions" (as
distinct from just whilst trying to modify the design of a live
database!)

Well, you *can't* modify a live A2K+ front end unless you have
exclusive access to it. This is because of the monolithic save
model, where the whole Access project (the forms/reports/etc.) are
saved in a BLOB field of a single record in a system table, as
opposed to A97 and earlier, where each object was a separate record.
Two people can't be editing a single record simultaneously unless
you use optimistic locking and Microsoft chose PESSIMISTIC locking
for the Access project. Thus, simultaneous editing of the Access
project is PROHIBITED at the program level.

I shudder to think what would have happened if they'd chosen
optimistic locking. Of course, I'm skeptical of the benefits of the
monolithic save model in the first place -- seems like a sour grapes
response to a problem that could have been solved in another way.
For one, I don't edit front ends live, since my users don't share
them, so it seems to me that they could have kept the old save model
and required exclusive access for editing the Access project records
and gotten the stability they claim the monolithic save model was
implemented to provide without any of the horrid downside of it
(speed, increased dangers of corruption).

And whilst we're at it, let's mention (with the greatest respect
and interest, no sarcasm), Tom Wickerath backed up by posts from
Peter Miller, that memo or large object fields are apparently more
prone to corruptions because the actual data is contained on the
"heap". That may be so. Some of my programs are 90% memo fields,
for reasons which are irrelevant here (well, because my program is
so general-purpose that it doesn't put any operator-limit within
reason), and, I claim, I suffer corruptions like anyone but not so
much as, for instance, Tom would claim experience of!

It's quite well-known that memo field data is not stored with the
record it belongs to but that only a pointer to the data page where
the memo's data is located is stored with the record. That point can
become easily corrupted, and that's the source of instability with
memo fields. The easiest way to work around it is to not edit memos
in bound controls.

So. Are the untoward corruptions because they were unsplit, or
split but shared FE, or had too many memo fields, or were running
A2000 or later, or on a WAN, or whatever else you say it suffered
from? You must be very old to have experience of all of them!

I don't know. It depends on the contexts.

If you split, but share the front end, you're not getting most of
the benefit of splitting, so I don't see why anyone would do that
(except extreme laziness, which is the only reason I've ever done
it).

Memo corruption has little of nothing to do with the splitting
issue. It's entirely a multi-user and network stability issue.

(My most memorable anecdotal experiences of corruption is this:
one or several of my customers starts experiencing them perhaps
daily, but no others do so it's not the program or "layout" per
se. It's...it's...never provable but either a) they have a bad
operator who aborts out of the program b) a subtly corrupted FE or
BE which nevertheless passes all tests c) a bad network or errant
PC or something. Anyway, I solved those sites through no fault of
my own, eventually they stabilised, so they either fired the
errant personnel, changed their computer or network, and I never
did find out why they stabilised because they were remote sites so
I couldn't analyse it even if I knew how. They already had all the
std Access layout recommendations. Perhaps some of the PC's
weren't SP'd, but anyone claiming experience would at least know
that asking by telephone results in "honest lies".

It's very easy for PCs to revert to non-stable versions of
Access/Jet. Most of my A2K and later apps have code that logs the
Access and Jet versions at sign-on. I don't prevent them from
running if they don't have the correct ones, but if something goes
wrong, I check that log table first to see if anyone is running with
inappropriate software updates. This is almost always the source of
corruption that pops up in an environment that has historically been
stable.

Now, I don't for a moment recommend unsplit, (Tony Toews came up
with the only interesting reason), and neither do I read does Ed.
But at the same time, it seems to be the least of my worries. Are
you saying the sites you took over were suffering from corruptions
which you solved by splitting?

Some were. One Access 2 site was running unsplit with a shared
Access 2 database when they converted everybody to Windows 2000.
They had terrible problems (partly due to not knowing to install the
Jet 2.5 service pack, but mostly to incompatibilities in Win2K).
Splitting and putting the front end on everybody's workstation ended
the corruption immediately.

Another client was using A2K and had the same problem, unsplit and
shared. Splitting and putting front ends on the workstations fixed
the problem.

Did anything
else change concommitant with those changes which might have
relevance? (eg did you improve the code as well or PC's or network
or something?)

In these kinds of situations, which I consider emergencies, the
first thing is stability. I split and distribute front ends to all
the users. At that point, the problems simply go away. No further
work is needed to figure out if there are code issues.

It is sooo
easy to be misled, including myself of course. Such as blaming
corruptions on unsplit with no adequately controlled experiment.
Anecdotal evidence I ackowledge as important however, because I
have no direct evidence either way either. If you do, then you
haven't presented it beyond a generality.

It's not that split apps never corrupt. It's that the most likely
cause of corruption in an unsplit app is that it's unsplit and,
consequently, shared. I don't believe in sharing a front end, so I
never even contemplate splitting and sharing.

My purpose, is that this stuff worries me, as I'm sure it does all
Access programmers, and that is all I mean.

I believe there are absolutely no valid objections to splitting and
giving an individual front end to each user. Given that this is the
optimal environment, I don't do any experimenting with any of the
other options, as I consider it a complete waste of time. Why
investigate something that I can prove is conceptually prone to
failure in some cases, even if it might very well work in many?