Will speed be a problem for using access in the company?

[Guest]

Hi all,

I have created a database for the company to manager all the special
agreements we have with customers. There will be about 45 users - all read
only and 1 user has the access for amendment.

At the moment, the database is 5.37MB, but the data will grow with the time
goes by. If all the users use it at the same time, will it be very slow or
crash? Is there anyway I can avoid these kinds of things? I really don't want
to give up at this stage.

Any suggestion will be much appreciated.

Thanks and best regards
Joanna

 

[Stefan Hoffmann]

hi Joanna,

I have created a database for the company to manager all the special
agreements we have with customers. There will be about 45 users - all read
only and 1 user has the access for amendment.

This should not be a problem, you only need a fast network (>100MBit).

At the moment, the database is 5.37MB, but the data will grow with the time
goes by.

An Access database can grow to a size of 2Gb of data. Due to internal
limits you may run into problems a little bit earlier, e.g. compacting a
1.5Gb database may already fail.

If all the users use it at the same time, will it be very slow or
crash?

No. You should only use on trick:

Create a hidden form at startup. Create a recordset and open it. Close
the recordset and the form when closing the application.

Is there anyway I can avoid these kinds of things? I really don't want
to give up at this stage.

Depending on the importance of the data, you should consider using SQL
Server as a backend as it provides better mechanisms to backup your data.


mfG
--> stefan <--

 

[Joseph Meehan]

Hi all,

I have created a database for the company to manager all the special
agreements we have with customers. There will be about 45 users - all
read only and 1 user has the access for amendment.

At the moment, the database is 5.37MB, but the data will grow with
the time goes by. If all the users use it at the same time, will it
be very slow or crash? Is there anyway I can avoid these kinds of
things? I really don't want to give up at this stage.

Any suggestion will be much appreciated.

Thanks and best regards
Joanna

Step #1 split the database so each user has a copy of a front end on
their own desktop and that copy will do all the work while the back end only
serves as a warehouse of data. Be sure to backup-compact&repair often. The
real unknown is how good your LAN is. Access expects a quality solid
connection.

Access will accept 255 users at one time, but that is a real stretch.
At 45 you are close to the point where it may start causing issues. Only
experience will really tell. It is far less likely to cause any problems
with 45 users if they are on a good LAN and if they are not doing a lot of
work all the time. I had 30-60 people on mine with only maybe 10 who did a
lot of work on them (data entry) I experienced no problems related to multi
users.

Good Luck

Next you need to decide the split. What parts of the database will be
on the "server" and will be called the Back end database from now on and
which parts will be on each user's machine and will be called the front
ends. The back end should hold all data that is shared and may be changed
by the users. It should also contain all or most data that more than one
user will need access to and may be changed by you from time to time. Most
other data that does not change or that will only be used by that particular
user should be on the Back end databases on the users machines.

For example you may have all the sales made by a unit on the back end
along with the price list. The sales may been to be shared by everyone so
they all know what has been done or pending. The price list may not be a
field they will change, but you may need to change to assure everyone has
the same current price available.

Each individual machine may have something about your company like
addresses that does not change or even product descriptions etc. You may
want each user to be able to store personal information about customers like
their kids names or shared information about sports teams or you may want to
put this on the server so everyone will have this information.

This is an art form and a science to get this part of the planning
designed and will be an ongoing job and should include the users in the
planning.

Access works best if it does not need to move a lot of information over
the LAN which means static data is best kept on the front end databases.
Also kept on the front end machines will be most forms, reports queries etc.
This will allow the whole system to work faster and in some cases allow for
customization of some forms reports etc.

This may seem like a lot of work and off the point of the question you
were asking, but it is very important that this part of the job be done
first and right.

Next is the mechanics of setting up the back end on the server, dumping
in the data and putting the front end copies on each user's machines and
assuring that the links work. Access has a built in database splitter that
may make this part of the job (moving from a single database with all the
data and forms etc. to two databases a front end and a back end.) easier.
Look under the Tools menu for it.

You may also want to look into user level security to protect the
database and data before you finish.

I suggest you start by reading
http://support.microsoft.com/default.aspx?scid=kb;[LN];207793

Access security is a great feature, but it is, by nature a complex product
with a very steep learning curve. Properly used it offers very safe
versatile protection and control. However a simple mistake can easily lock
you out of your database, which might require the paid services of a
professional to help you get back in.

Practice on some copies to make sure you know what you are doing.

Splitting a database can be a big job, but done right everyone will
thank you and wonder how they did their jobs without it.

Note: back ups become more important here. If you LAN does not support
automatic backups you should provide a method of backing up the data, even
if that means you do it manually.

 

[ChrisM]

In message (e-mail address removed),

Hi all,

I have created a database for the company to manager all the special
agreements we have with customers. There will be about 45 users - all
read only and 1 user has the access for amendment.

At the moment, the database is 5.37MB, but the data will grow with
the time goes by. If all the users use it at the same time, will it
be very slow or crash? Is there anyway I can avoid these kinds of
things? I really don't want to give up at this stage.

Any suggestion will be much appreciated.

Thanks and best regards
Joanna

I have an access application running with about 20 users, but it is much
bigger than 5Mb and involves both reading and writing from all users and
there are no real issues with speed. Roughly how many database reads will
there be per user per hour?
Unless it is going to be hundreds, I doubt that speed will be a worry with
such a small database. If the database is going to grow a lot in the future,
it's worth making sure that it is all designed well now (all the right
indexes in the tables etc) or you might find it starts to slow down
eventually

The best piece of advice (which you may well already know) regards a
multi-user database is to split it up into back-end (all the tables) and
front-end (all the queries, forms etc etc with links to the tables on the
back-end database).
Stick one copy of the the back end on the server, and a copy of the
front-end on each client computer. Unless things have changed since I last
programmed access, this makes maintenance easier, and apparantly reduces the
possibility of corruption to the data.

 

[dbahooker]

I've had enormous performance problems with only 25mb of data and a
half dozen users

I maintained this database in 12 different offices; a total of about
50 users.

Access MDB was not reliable enough and the client was quite impressed
with the ADP solution (and the proper level of indexing... using SQL
Server Index Tuning Wizard)

 

[dbahooker]

CORRECTIOn

the best advice has nothing to do with spliting

the best advice is to move to SQL Server

 

[David W. Fenton]

This should not be a problem, you only need a fast network
(>100MBit).

You mean >=100Mbit, no? Gigabit networks are pretty uncommon still,
and not really necessary for any properly designed Access
application, including the one described. Of course, I would
consider an Access app that has trouble on a 10BaseT network to be
*not* properly designed, but I'm something of a purist on this
point.

 

[Joseph Meehan]

I've had enormous performance problems with only 25mb of data and a
half dozen users

I maintained this database in 12 different offices; a total of about
50 users.

Access MDB was not reliable enough and the client was quite impressed
with the ADP solution (and the proper level of indexing... using SQL
Server Index Tuning Wizard)

Sorry about Aaron - you get all types in a public forum!!

 

[Stefan Hoffmann]

hi David,

You mean >=100Mbit, no?

Of course, your right.

Gigabit networks are pretty uncommon still,
and not really necessary for any properly designed Access
application, including the one described. Of course, I would
consider an Access app that has trouble on a 10BaseT network to be
*not* properly designed, but I'm something of a purist on this
point.

This point is also true, but you have to consider the amount of data
which also uses this bandwith today. In many cases this will result in
some additional latencies, e.g. I have it seen in some installations
that running Outlook had a severe impact, closing it improved the speed
not drastically, but noticeable.


mfG
--> stefan <--

 

[SmartbizAustralia]

Would suggest you start with the data in one database and the
application in the other and link the files.
Get a few users and once your design is working move the data to SQL
Express.
As for having the front end app on the desktops, this can become a
support nightmare for updating the front end and users get used to
waiting 10 seconds for the app to load over the network.
Do note the one trick about keeping a connection open....

Regards,
Tom Bizannes
Microsoft Access Development
Sydney Australia
www.smartbiz.com.au

 

[Joseph Meehan]

Would suggest you start with the data in one database and the
application in the other and link the files.
Get a few users and once your design is working move the data to SQL
Express.
As for having the front end app on the desktops, this can become a
support nightmare for updating the front end and users get used to
waiting 10 seconds for the app to load over the network.
Do note the one trick about keeping a connection open....

While you may well be right, in my applications I have never run into
those problems. Properly planed front ends in the business environment I
had (I had the authority over the application and the users and a very good
LAN) those problems never developed.

 

[David W. Fenton]

As for having the front end app on the desktops, this can become a
support nightmare for updating the front end and users get used to
waiting 10 seconds for the app to load over the network.

I've never encountered any support problems whatsoever, and have
used everythign from an email link to upgrade, to batch files and
Tony Toews's AutoUpdater.

What problems cause support issues?

And how is it easier on a server, when you *still* have to give
every user and individual front end?

 

[aaron.kempf]

it's easier because on a server; you don't need to be constantly
copying QUERIES and TABLES

with ADP you can keep all your queries and tables IN ONE PLACE-- WHERE
THEY BELONG

 

[James A. Fortune]

Hi all,

I have created a database for the company to manager all the special
agreements we have with customers. There will be about 45 users - all read
only and 1 user has the access for amendment.

At the moment, the database is 5.37MB, but the data will grow with the time
goes by. If all the users use it at the same time, will it be very slow or
crash? Is there anyway I can avoid these kinds of things? I really don't want
to give up at this stage.

Any suggestion will be much appreciated.

Thanks and best regards
Joanna

I say Access is adequate, but do the following:

1) Split the database such that the data is on the server and the
amendment person has a special FE mdb to do the amendments.

2) Use one of the FE update tools to update the BACKEND to the read-only
user's hard drives daily.

3) Use one of the FE update tools to update a front end for the
read-only user's hard drives. This FE will link to the BE files on the
local computer.

This assumes that daily updates are sufficient for reading the
documents. If you can use this set-up, your database will fly. If not,
the fact that almost all of the users are read-only suggests that a
well-designed database will have no trouble delivering shared data to 45
or more users across the network.

James A. Fortune
(e-mail address removed)

 

[Tony Toews [MVP]]

Would suggest you start with the data in one database and the
application in the other and link the files.

But each user should still get their own copy of the FE.

As for having the front end app on the desktops, this can become a
support nightmare for updating the front end and users get used to
waiting 10 seconds for the app to load over the network.

If you use the Auto FE Updater utility then the new FE is copied to the client only
when it's been updated on the server. Visit
http://www.granite.ab.ca/access/autofe.htm

Do note the one trick about keeping a connection open....

And others at my Access Performance FAQ page at
http://www.granite.ab.ca/access/performancefaq.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
Tony's Microsoft Access Blog - http://msmvps.com/blogs/access/

 

[SmartbizAustralia]

Thanks guys for supporting FE on the desktops.

But really is it any faster once it's loaded into memory?

Also when you are dealing with 300 access users with 500 different
database systems, trying to maintain them on desktops for the
different users etc is not only a nightmare but a support issue.

Also for switching between testing and live versions I get the front
to update the links to the backend/s in the same directory.
How you do this with your FE on the desktop and the backend on the
server is beyond me.

Just give us constructive comments without just saying it is better.

Regards,
Tom Bizannes
Microsoft Access Development
Sydney, Australia

 

[SmartbizAustralia]

Why all the FE on the user's desktops?

Splitting the data/BE does help prevent corruption so what reason is
there for this separation except to save 10 seconds on loading?

Especailly with Access 2002/2007 versions, is this extra complication
going to help.

The main reason I am against this is that we have three enviroments
for each system, Developement, Testing and Production. So the front
end always checks and updates it's links to the BE in the same
directory.
This makes moving the updates to testing and then to production very
simple.

I saw some articles regarding errors with people updating filters, but
is this still the main reason why you would want to put the FE on the
user's pcs?

Regards,
Tom Bizannes
Sydney, Australia

 

[James A. Fortune]

Why all the FE on the user's desktops?

Splitting the data/BE does help prevent corruption so what reason is
there for this separation except to save 10 seconds on loading?

Especailly with Access 2002/2007 versions, is this extra complication
going to help.

The main reason I am against this is that we have three enviroments
for each system, Developement, Testing and Production. So the front
end always checks and updates it's links to the BE in the same
directory.
This makes moving the updates to testing and then to production very
simple.

I saw some articles regarding errors with people updating filters, but
is this still the main reason why you would want to put the FE on the
user's pcs?

Regards,
Tom Bizannes
Sydney, Australia

I have found that splitting the database is still the #1 tip I have
received from the Access newsgroups. The time and frustration it saved
me is one of the reasons I had as much time to post solutions as I did.
It makes the database more reliable and gives you more flexible ways
to connect to data. That's why I suggested splitting the database even
for the case where both ends are on a local computer. The fact that you
have three environments makes splitting the "code" and the data even
more logical. If you can do everything on each user's PC then you can
combine the FE and BE if you wish without any speed penalty, but I think
splitting is better even in that situation. For the case where many
read-only users are hitting the data, splitting definitely gives you
more flexibility. What if management wants another person to be able to
edit the data? You'll end up with a mess if you don't split.

In addition to avoiding corruption, having the FE on a local machine is
more efficient because Access can access (load into memory) the .mdb
file on the local hard drive much faster than it can access the .mdb
file on a network. Remember also that a shared FE .mdb file loaded into
memory is not run exclusively (hence the cause of corruption). Using
every possible way to keep network communication to a minimum is one of
the best ways to keep an Access database from slowing down. Keep in
mind that once the network is involved, new variables, such as the
amount of network traffic, affect the speed of the database.

Of course, you are free to discover the merits of splitting versus not
splitting on your own through trial and error, but many believe, that
time is better spent on other Access issues. If you have special
circumstances that suggest an exception to splitting then you have a
reason to consider keeping the database together.

James A. Fortune
(e-mail address removed)

 

[David W. Fenton]

But really is it any faster once it's loaded into memory?

Yes, because it's pulling the forms/etc. from a local file instead
of across the network.

Also when you are dealing with 300 access users with 500 different
database systems, trying to maintain them on desktops for the
different users etc is not only a nightmare but a support issue.

If you use something like Tony Toews's auto front-end updater, this
should be pretty easy, as when you roll out a new app, the shortcut
the users will utilize to launch the app takes care of the updates
for you.

Also for switching between testing and live versions I get the
front to update the links to the backend/s in the same directory.
How you do this with your FE on the desktop and the backend on the
server is beyond me.

You can easily write code that checks the location of the front end,
and if it's not the development front end's location, switch the
links.

Just give us constructive comments without just saying it is
better.

There is no question that a split application is vastly better in
all ways.

Whether each user's individual front end is on the server or on the
workstation is a different matter. I put it on the workstation,
since I consider it software just like MS Word (which you would very
likely never run from a server), but there's probably not much
difference in real performance.

 

[David W. Fenton]

Why all the FE on the user's desktops?

Because it's logical. You don't install MS Word on the server and
run it from there, and a front end is nothing more than a local
program that the user is running, just like MS Word.

Splitting the data/BE does help prevent corruption so what reason
is there for this separation except to save 10 seconds on loading?

The key point is not *just* splitting, but giving each user an
individual front end.

You could put all the individual front ends on the server if you
wanted, but I can't see any p oint in doing that.

Especailly with Access 2002/2007 versions, is this extra
complication going to help.

You need individual front ends and there's no difference between
distributing updates between putting the individual front ends on
the workstation or storing them all on the a server.

The main reason I am against this is that we have three
enviroments for each system, Developement, Testing and Production.
So the front end always checks and updates it's links to the BE in
the same directory.
This makes moving the updates to testing and then to production
very simple.

An automatic front end updater like the one developed by Tony Toews
takes care of this and makes it completely invisible to the users.

I saw some articles regarding errors with people updating filters,
but is this still the main reason why you would want to put the FE
on the user's pcs?

No, that's why you want each user to have an individual front end.
That's much more important than *where* those front ends are stored.