Multi User Capability

G

GOL

I have my database on our company network. Everyone works off of the same
copy (no front/back end copies of any sort) in a certain folder. Is there a
point where Access will not perform as well due to the overload of people
using it, or is this more of a network capability issue. I will be having at
the max 25 people simultaneously entering and editing data at the same time
at their own computers, running off of the database on the network. Will
this slow the program down, and if so, how do I get around this?
 
A

Allen Browne

You will have problems as different users attempt to apply filters or change
the OrderBy of the different instances of the same form that is open on
different machines, and then they close the forms and Access attempts to
save them while others are still using them.

Further, if anyone attempt to make *any* deisgn change to any object while
any other object is in use, you have a receipe for disaster. Apart from the
likely corruptions, this makes it nearly impossible to make any design
changes to your database if people are constantly using it.

Was there a reason you did not want to split the database and give each
workstation its own copy of the front end? That would solve all the
problems, as well as avoiding the network traffic caused by constantly
dragging the interface across the network as well as the data. If you had
performance issues with a split database, see:
http://www.granite.ab.ca/access/performancefaq.htm
 
G

GOL

Allen and Joseph,

The only reason I did not want to initially split the database was that I
was not familiar with it at all. I read up on it and I will try it but had
some questions first. The way I read, all static data should be on the
front-end and all dynamic data on the back end (tables). I also have tables
that will not change (are static). How do I get these on the front ends, as
the wizard automatically puts all tables on the back end. Also, if I am
correct, this will require me to set up a front end on each individual PC
that will be using the database? I won't simply make one front end on the
server and have everyone use it (defeats purpose, right?).

Another problem is that the database must be used in real time with other
users on different PC's. I read the Tony Toews program that updates the
database, but from what I read it only updates the front-end every time you
close and open it. I need it to update every few minutes at the latest to
keep up with current data (We are taking orders on one machine w/database and
processing on another within minutes, and initial order data is needed). The
way it currently is, all users use the one copy on the network, which allows
current data from all to be seen in real time by all. The increased users is
making speed an issue now, and the split database seems reasonable, I'm just
worried about the current data issue. Please explain better if I seem to
have gotten the wrong idea. If the split database does not allow constantly
updated data by all, does anything?
 
D

Douglas J. Steele

Don't worry about static data vs. dynamic data: unless your static data
tables are very large (i.e. thousands of rows), it shouldn't make any
difference.

Also, the frontends will be getting the updated data from the backend.
Current data will be seen by all.
 
A

Allen Browne

You can place a copy of the front end in each user's space on the server if
you prefer. This approach increases the network traffic because users are
still pulling the interface across the network, but it does avoid the
concurrency issues and that's the main thing. Tony Toews' auto-frontend
updater is a better solution.

Once split, all users share the single back end data file, so they all have
the same data. There is no need to re-serve the data to everyone else. For
example, if one user is entering an order, and another user is to process
that order a few minutes later, the 2nd user will need to Requery their form
(or close and reopen it) to see the latest orders.

If you have users who are at different sites (on a wide-area network, over
wireless/VPN/dial-up/broadband), you need to take a different approach. See:
http://www.members.shaw.ca/AlbertKallal/Wan/Wans.html
 
J

J. Clay

The update that Tony's program does is the application itself, not the data.
Since they are looking at the same Back End, they are seeing the same data.
If they need to be able to see updated data imediately, then you will want
to create a "Refresh" button, that will allow users to refresh data at the
click of a button rather than having to wait for an auto refresh.

In terms of the updater, I actually use a much more simplistic approach than
Tony's, but it works for our needs. On our Domain Login Server, I created a
login script that runs everytime someone logs into the network. I use this
script to force a couple of common netword shares to be mapped to a common
Drive letter throughout our company and I also use it to copy the Master
copy of our Access application to a common folder on each user's computer.
This way, if I put a new version of the application in the network folder,
the next time any user logs off/on the network, they get the most recent
version of the application.

Jim
 
T

Tony Toews

Allen Browne said:
You can place a copy of the front end in each user's space on the server if
you prefer. This approach increases the network traffic because users are
still pulling the interface across the network, but it does avoid the
concurrency issues and that's the main thing. Tony Toews' auto-frontend
updater is a better solution.
Click to expand...

FWIW the Auto FE Updater works with either each users FE being stored
on a server or on the client workstation.

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
 
Top