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

[Joanna]

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

 

[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.

 

[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

 

[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.

 

[dbahooker]

LATENCY DUDE

waiting for the front end updater to run.. copying all the forms and
queries

MDB is just flat-out stupid

keep your queries and tables where they belong-- SQL Server

 

[Tom Wickerath MDB]

yes, speed will be a problem

Access MDB isn't fast enough or reliable enough for real-world usage

 

[Tom Wickerath MDB]

you don't need to split, you need to move to access data projects