To Split or not to Split

[Guest]

I have a database that only 1 person will be editing data. However about 60
people will have permission to certain forms. My question is if only one
person will be editing and everyone else will just be able to view data, is
it still necessary to split?

 

[Ed Metcalfe]

Matt,

It's rarely *necessary* to split the database, but I would still recommend
it.

Splitting the database makes releasing code updates far more straight
forward. Performance can also be improved by installing the database
frontend on each user's local drive (this means queries, forms, reports,
code, etc. do not have to be loaded across the network).

Ed Metcalfe.

 

[Guest]

Isn't there a way to update one form instead of changing 60 different forms
if I split?

 

[Guest]

Nevermind, i already know the answer to that question. I would make the
change to the FE and update each users FE with that of the new one.

When manually splitting a secure database, I want to copy the original mdb
and that will be my BE. Should I give it a .be extension or .mdb or doesn't
it matter?

 

[Joan Wild]

It doesn't matter what you name it.

 

[Tony Toews [MVP]]

Splitting the database makes releasing code updates far more straight
forward.
Agreed.

Performance can also be improved by installing the database
frontend on each user's local drive (this means queries, forms, reports,
code, etc. do not have to be loaded across the network).

Actually no. Performance is worsened. In A97 some reports went from
20 seconds to 20 minutes. However there are things you can do to
get much of that performance back.

But the benefits of splitting far outweigh the downside.

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

 

[Tony Toews [MVP]]

I have a database that only 1 person will be editing data. However about 60
people will have permission to certain forms. My question is if only one
person will be editing and everyone else will just be able to view data, is
it still necessary to split?

See the "Splitting your app into a front end and back end Tips" page
at http://www.granite.ab.ca/access/splitapp/ for more info. See the
Auto FE Updater downloads page
http://www.granite.ab.ca/access/autofe.htm to make this relatively
painless.. The utility also supports Terminal Server/Citrix quite
nicely.

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

 

[Ed Metcalfe]

Actually no. Performance is worsened. In A97 some reports went from
20 seconds to 20 minutes. However there are things you can do to
get much of that performance back.

But the benefits of splitting far outweigh the downside.

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

Tony,

I'm confused. I realise that accessing linked tables may be slower than
accessing tables stored in the frontend, but surely loading forms, reports,
code, etc from a database on the local drive is quicker than loading them
over a network connection.

It's been a long time since I've used Access 97 (is the performance decrease
with split databases resolved in later versions) but I don't recall *ever*
having a form take 20 mins to open. I also don't recall there ever being
that much of a difference in performance between split and unsplit
databases.

Is this issue one that only affects bound forms perhaps? If so I wouldn't
have seen it (I don't use them)...

Ed Metcalfe.

 

[Tony Toews [MVP]]

I'm confused. I realise that accessing linked tables may be slower than
accessing tables stored in the frontend, but surely loading forms, reports,
code, etc from a database on the local drive is quicker than loading them
over a network connection.

Yes, having a FE MDB/MDE on the local system is faster than having it
on the server. However, in my experience based on what I saw in A97,
splitting the MDB made for much worse performance.

Also a client didn't want to copy the FE MDE onto the Citrix server so
I setup the Auto FE Updater to copy the FE MDE from the distribution
folder to a user defined folder on the same server. Then Access on
the Citrix server was indeed pulling the forms, etc, across the
network. While I'm sure the performance was worse than having the FE
MDE on the server it wasn't that noticeable.

It's been a long time since I've used Access 97 (is the performance decrease
with split databases resolved in later versions) but I don't recall *ever*
having a form take 20 mins to open.

These were reports not forms.

I also don't recall there ever being
that much of a difference in performance between split and unsplit
databases.

There was but it was really noticeable on complex forms which had lots
of subforms and combo boxes.

Is this issue one that only affects bound forms perhaps? If so I wouldn't
have seen it (I don't use them)...

I seldom use unbound forms so I couldn't tell you.

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

 

[Ed Metcalfe]

Yes, having a FE MDB/MDE on the local system is faster than having it
on the server. However, in my experience based on what I saw in A97,
splitting the MDB made for much worse performance.

Also a client didn't want to copy the FE MDE onto the Citrix server so
I setup the Auto FE Updater to copy the FE MDE from the distribution
folder to a user defined folder on the same server. Then Access on
the Citrix server was indeed pulling the forms, etc, across the
network. While I'm sure the performance was worse than having the FE
MDE on the server it wasn't that noticeable.


These were reports not forms.


There was but it was really noticeable on complex forms which had lots
of subforms and combo boxes.


I seldom use unbound forms so I couldn't tell you.

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

Thanks Tony.

 

[David W. Fenton]

It's rarely *necessary* to split the database, but I would still
recommend it.

I would say in 99.999% of cases, it's ABSOLUTELY NECESSARY to split
the database. The only exception would be the single-user database
that is used by only one person who is also the database's designer
(i.e., nobody else is making changes to the front end).

Deploying *unsplit* should be the *exception*, as split apps are the
standard way to do it.

 

[David W. Fenton]

In A97 some reports went from
20 seconds to 20 minutes.

I never saw any such thing, Tony.

But, yes, splitting *can* worsen performance compared to an unsplit
A97 MDB that was being shared on a server. But with later versions
of Access, that wasn't even an option. A97 could cope with sharing a
front end (split or not), but no later version Access can do it at
all without very frequent corruption problems.

 

[Ed Metcalfe]

I would say in 99.999% of cases, it's ABSOLUTELY NECESSARY to split
the database. The only exception would be the single-user database
that is used by only one person who is also the database's designer
(i.e., nobody else is making changes to the front end).

Deploying *unsplit* should be the *exception*, as split apps are the
standard way to do it.

David,

I agree.

I didn't really explain myself very well. What I really meant was it is
rarely *essential* to split an Access database. It is certainly almost
always a good idea to.

Ed Metcalfe.

 

[Tony Toews [MVP]]

I never saw any such thing, Tony.

<shrug> We had 100 page reports with each line having a one or two
line subreport. That was particularly nasty. I moved the data from
the subreport onto the main report and turned the fields off or on
depending and got the report back to 20 seconds.

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]

I agree.

I didn't really explain myself very well. What I really meant was
it is rarely *essential* to split an Access database. It is
certainly almost always a good idea to.

And I'm DISAGREEING with you -- it *is* essential in all but the
tiniest number of instances.

 

[Keith Wilby]

And I'm DISAGREEING with you -- it *is* essential in all but the
tiniest number of instances.

FWIW I agree with David. For the amount of effort involved to split you
might as well anyway since, in my experience, the chances of corruption are
significantly decreased. I think that "it's rarely *necessary* to split the
database" is very bad advice.

Keith.
www.keithwilby.com

 

[Ed Metcalfe]

FWIW I agree with David. For the amount of effort involved to split you
might as well anyway since, in my experience, the chances of corruption
are significantly decreased. I think that "it's rarely *necessary* to
split the database" is very bad advice.

Keith.
www.keithwilby.com

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.

 

[Chris Mills]

FWIW, a primary reason for me to split databases, is nothing to do with
performance, whether it works, (all of which are probably true, hard to prove,
and I accept as true...)

It's because, I can't replace the program with an updated one if live data is
also mingled in with it!

This is probably why you get some people trying to modify programs "on-line"
(which with my capacity for mistakes would be disastrous!)

I inherited one site with mixed program/data. I had to test what I would do at
my site, then go to their site, get everyone out, and hopefully remember the
mod I had tested.
-----
For this reason, I split even a single-user database. OTOH I have had multiple
users share a networked Front-End (A97) with no dire problems, but whether
that is recommended or not is a separate issue from splitting! (I don't
recommend such, I just used a guinea pig customer to try it)(not theory,
actual tests!!!)
-----
I note that Ed, in his first brief reply, correctly identified relevant
issues. Then it got sidetracked by Tony Toews mentioning a potential
performance downside of splitting. Then it got further sidetracked as to
whether "rarely necessary" was an appropriate wording (probably not because it
implied "advisable"). Ed has made it abundantly clear, I think, what will or
may work vs what is recommended best practise.

I have indicated above, that even if unsplit databases work, they are clearly
inconvenient. And since A2000, modifying live programs has become (either
dangerous or impossible), whether or not the program is mixed up with data or
just a split shared Front-End.

No complaints from me, Ed!
Chris

 

[Ed Metcalfe]

No complaints from me, Ed!
Chris

Thank you Chris.

 

[Chris Mills]

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"?

It's an established scientific principle, that anyone "following" should also
TEST for themselves, not merely take established dogma. Otherwise, we would
still be following Newtonian gravity rather than Einstein's General Theory of
Relativity...

EVERYONE should take the established "facts", or theories, and test them again
for themselves. Just occasionally, someone might question whether it's needed
(say) to remove Admin from the Admins group, for a recent independent example.
(there is no very good factual reason!)

Good on ya for kicking up a minor stormdust, Ed. Even if completely wrong, the
OP sure got some opinions!

(my own experience of "access corruption", though more often than I would
like, I am unable to relate to some of the purported causes. That's because,
like David Fenton, I use the recommended layout most times, and therefore have
little statistical data on the "unrecommended methods". All but one or two of
my 500 or 600 sites use recommended methods, and therefore my nbr sites means
nothing in respect of alternatives!)

(We must presume that David, etc, has extensive experience of "bad practise"!)

Chris