Splitting DB for multi-user environment - slow performance

[Steve D]

Hi,

I understand that in order to allow for a multi-user db on a network the db
should be split (BE/FE). When I implemented this on our server the db ran
very slow. Is this the norm? Can anything be done to improve in this
situation? I had read that splitting the db should improve performance.

 

[[MVP] S.Clark]

Did you put the FE on the user's local machine? If not, then you are
missing the best performance gain, which is not having to pass the object
across the network, only the data.

You can use Total Access Startup to administer deploying the latest version
to the local machines.
http://fmsinc.com/products/startup/

--
Steve Clark, Access MVP
FMS, Inc
http://www.fmsinc.com/consulting
Professional Access Database Repair
*FREE* Access Tips: http://www.fmsinc.com/free/tips.html

 

[Jerry Whittle]

When you compared speed between the split and un-split databases, was the
un-split version out on the network? If not, you might be comparing apples (a
split database on a network) to oranges (a single mdb file on your hard
drive). Also are we talking about a delay that can be measured with a
stopwatch or sand in an hour glass? Remember that true measure isn't one
person using the database; rather, it's how it runs with a dozen people in it.

Access could be used as a network stress testing tool. If your network has a
weakness, Access could find it. You may need to get network people involved.
Novell by chance?

Sometimes it helps to have a persistant connection to the BE tables. You can
do this by creating a bogus table with some records in the BE. Next create a
form based on this table in the FE. Have it open up first before the
Switchboard and go hidden. That way a connection is kept between the FE and
BE.

 

[Tony Toews]

I understand that in order to allow for a multi-user db on a network the db
should be split (BE/FE). When I implemented this on our server the db ran
very slow. Is this the norm? Can anything be done to improve in this
situation?

The three most common performance problems in Access 2000 or newer
are:
- LDB locking which a persistent recordset connection or an always
open bound form corrects (multiple users)
- sub datasheet Name property set to [Auto] should be [None]
- Track name AutoCorrect should be off

If the problem is for everyone when starting up the MDB then it likely
needs a decompile.

For more information on these, less likely causes, other tips and
links to MS KB articles visit my Access Performance FAQ page at
http://www.granite.ab.ca/access/performancefaq.htm

I had read that splitting the db should improve performance.

No, that certainly was not my experience. "Performance is worse after
splitting - My personal experience aka How to speed up complex forms
and reports with many records each with subreports.
http://www.granite.ab.ca/access/splitapp/performance.htm

However the benefits of splitting greatly out weigh the disadvantages.
One being that you can work on the FE without disturbing the user.
Another being reduced chance of corruption.

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

 

[chemicals]

:

Sometimes it helps to have a persistant connection to the BE tables. You can
do this by creating a bogus table with some records in the BE. Next create a
form based on this table in the FE. Have it open up first before the
Switchboard and go hidden. That way a connection is kept between the FE and
BE.
--

Doesn't the switchboard already function as the bound form in the example
above? The switchboard is always open...?

 

[Tony Toews [MVP]]

Doesn't the switchboard already function as the bound form in the example
above? The switchboard is always open...?

If a switchboard driven by tables then those tables might be in the
front end and not the back end.

I also have never used the switchboard manager and prefer to use
command buttons on unbound forms.

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]

If a switchboard driven by tables then those tables might be in
the front end and not the back end.

If you use the Switchboard Manager wizard, it *must* be in the front
end.

I also have never used the switchboard manager and prefer to use
command buttons on unbound forms.

Well, that leads to maintenance problems. The problem that the
Switchboard Wizard is trying to solve is actually a real one. I
mostly don't use the Switchboard Wizard any longer, but I have
plenty of active apps out there that do.

 

[Tony Toews [MVP]]

Well, that leads to maintenance problems.

How? If I rename a form or add a form I have to change a few lines of
code behind the form.

The problem that the
Switchboard Wizard is trying to solve is actually a real one.

Not really. The command button wizard can pretty much do everything
that the Switchboard Wizard does. At least to my knowledge.

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]

Well, that leads to maintenance problems.
The problem that the Switchboard Wizard is
trying to solve is actually a real one. I
mostly don't use the Switchboard Wizard any
longer, but I have plenty of active apps out
there that do.

One can only rely on experience, observation, and intuition on the issue of
Switchboard Manager -- it's a matter of opinion and personal preference.

My experience and observation of user-created databases using SM, and the
posts in the newsgroup indicate that it is more trouble than it could ever
be worth, and that it causes, rather than solves, maintenance problems.

My analysis of the SM is that it is a complex solution to a simple problem,
that it is inflexible, that it is easy for a user to inadvertently make
changes that break it, and break it they do, with unpleasant regularity. In
fact, sometimes I think there is a reason that Switchboard Manager has the
same initials as Sado-Masochism.

I'm not aware of any particular maintenance _problems_ caused by
Switchboards created with unbound Forms and Command Buttons. As with any
part of a database applications, there are certain situations which will
require maintenance... a good search and replace utility will simplify
those. And, if it seems the appropriate place, you have a lot more freedom
to add other controls, e.g., Combos and Text Boxes.

Larry Linson
Microsoft Access MVP

 

[David W. Fenton]

How? If I rename a form or add a form I have to change a few lines
of code behind the form.

But what about adding new buttons?

Not really. The command button wizard can pretty much do
everything that the Switchboard Wizard does. At least to my
knowledge.

But it dynamically uses a small amount of form real estate. What I
do in my non-Switchboard menu forms is create a hidden tab so that I
can have multiple pages of buttons. But you still have to drop
buttons onto the form and write the code form them when you add
functionality. The Switchboard wizard allows you to add
functionality without having to touch the actual design of the form
or the code behind it.

 

[David W. Fenton]

One can only rely on experience, observation, and intuition on the
issue of Switchboard Manager -- it's a matter of opinion and
personal preference.

My experience and observation of user-created databases using SM,
and the posts in the newsgroup indicate that it is more trouble
than it could ever be worth, and that it causes, rather than
solves, maintenance problems.

My analysis of the SM is that it is a complex solution to a simple
problem, that it is inflexible, that it is easy for a user to
inadvertently make changes that break it, and break it they do,
with unpleasant regularity. In fact, sometimes I think there is a
reason that Switchboard Manager has the same initials as
Sado-Masochism.

I'm not a user -- I'm a programmer. And I've never done anything to
break a Switchboard.

I really don't think that your criticism has any validity at all. By
your logic, there shouldn't be a command-button wizard for opening a
form, since the user could change the name of the form and it would
break the code that was written by the wizard.

I'm not aware of any particular maintenance _problems_ caused by
Switchboards created with unbound Forms and Command Buttons. As
with any part of a database applications, there are certain
situations which will require maintenance... a good search and
replace utility will simplify those. And, if it seems the
appropriate place, you have a lot more freedom to add other
controls, e.g., Combos and Text Boxes.

Well, I guess I'm just a bloody idiot, but I find adding buttons to
my menu forms fussy and a lot of work -- substantially more work
than running the Switchboard Manager.

Of course, the Switchboard has a limitations o 8 buttons per
switchboard, but I'm not sure that's unreasonable, as anything more
than that is going to be pretty darned hard to navigate.

The Switchboard is *data-driven*, and that's a *good* thing. It
means that you change what is displayed on the switchboard form by
changing the data in a table (through a handy user interface). I
think that's vastly preferable *in concept* to a form with command
buttons that have to be manually created and managed through edits
to the form and its code itself.

In reality, it's not flexible enough for me, so I create my own menu
forms with buttons on them, and deal with the maintenance issues.
But *conceptually*, the Switchboard Manager is a very good idea, and
I don't know that I could do any better than Microsoft did.

 

[Tony Toews [MVP]]

But what about adding new buttons?

Just run the command button wizard.

But it dynamically uses a small amount of form real estate. What I
do in my non-Switchboard menu forms is create a hidden tab so that I
can have multiple pages of buttons. But you still have to drop
buttons onto the form and write the code form them when you add
functionality. The Switchboard wizard allows you to add
functionality without having to touch the actual design of the form
or the code behind it.

To me I don't see much practical difference between the two
approaches. But then I'm rather comfortable working in VBA.

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]

Just run the command button wizard.

For what? What if I don't want to open a form? And does the command
button wizard have some functionality for making room for the button
that I'm unaware of?

To me I don't see much practical difference between the two
approaches. But then I'm rather comfortable working in VBA.

Between *which* approaches? Table-based and form-based?

 

[aaron.kempf]

Tony;

if you were 'rather comfortable' with anything you should SHUT THE
**** UP AND LEARN SQL SERVER

MDB IS FOR BABIES; this split 'front-end' crap is a bunch of horse
shit

ADP is _EASIER_ for developers; faster for developers, DBA and end
users.

**** YOU AND **** YOUR MDB _CRAP_ mofo

-Aaron

 

[John B. Smotherman]

Tony-

I've also noticed what seems to be a degradation in performance, and was
looking at your list:

The three most common performance problems in Access 2000 or newer
are:
- LDB locking which a persistent recordset connection or an always
open bound form corrects (multiple users)
- sub datasheet Name property set to [Auto] should be [None]
- Track name AutoCorrect should be off

I've changed the Track name AutoCorrect to OFF, and I'm in the process of
making a persistent recordset connection, just to eliminate the first one in
the list.

I've tried setting the subdatasheet Name property to [none], and although I
save the changes to the table design, when next I open it in design view the
name property is back to [Auto]. Is there someplace else I should change this
other than the individual table designs?

Thanks.