Access front end to Oracle table user load

[Fed semi-technical user]

We have an Oracle database table set up with two Access front ends to input
data. The Access front ends resided on a network drive so all users went to
the same two files. On our local server we had about 30 users, but probably
only 5-7 concurrent users. We know the Access files limit the users so only
one can update a specific record. We occaisionally had to wait before saving
a record.

Now we are needing to have this all moved to a server farm. The files would
be operated on a network drive through Citrix. At the same time there is a
desire to increase users to 150 to perhaps 350. Concurrent users would
increase accordingly. Our IT personnel think Citrix can handle the load, but
don't think the Access front ends will handle it. What are the limits to
Access? Can it handle this load?

 

[John W. Vinson]

We have an Oracle database table set up with two Access front ends to input
data. The Access front ends resided on a network drive so all users went to
the same two files. On our local server we had about 30 users, but probably
only 5-7 concurrent users. We know the Access files limit the users so only
one can update a specific record. We occaisionally had to wait before saving
a record.

Now we are needing to have this all moved to a server farm. The files would
be operated on a network drive through Citrix. At the same time there is a
desire to increase users to 150 to perhaps 350. Concurrent users would
increase accordingly. Our IT personnel think Citrix can handle the load, but
don't think the Access front ends will handle it. What are the limits to
Access? Can it handle this load?

Each concurrent user should have *THEIR OWN* individual copy of the frontend.
Sharing a frontend is not a good idea at all! My clients who use Citrix set up
individual folders; a user logs on to their own folder and uses their own copy
of the frontend.

 

[david]

Each copy of Access is an independent program. It's like asking if Word can
handle 350 users: there are millions of Word users.

Access over Citrix is like any other program over Citrix. Can Citrix handle
350 Word users? As it happens, yes, Citrix can handle 350 users using Word.

350 users is not a small number of users, so the next question is if your
servers (probably Windows 2008) can handle 350 users. As it happens, yes,
Windows Server can handle 350 users.

Access is not a tiny program, so the next question is if your Servers
(probably Windows 2008) can handle 30 copies of Access running. Probably,
yes, if you buy enough memory. If not, you can buy more servers.

Now, to the real questions: Is your application designed to handle 30 users?

In your application, Oracle limits the users so that only one user can
update a record. That is normal. That is what a database system does.

It the job of an application developer to design an application where you
don't get two users trying to update the same record. The more users you
have, the bigger this problem can be. This problem doesn't go away when you
write your application using Access,C#,VB.NET,PowerBuilder or anything else.

It is easy to design queries or procedures that will lock up the entire
database. It is the job of the DBA to make sure that no queries or
procedures are in use that do that. If your DBA refuses to co-operate with
your developers, you need to get either a new DBA or new developers. In that
case, the DBA's will be lobbying for new developers, and using their
ignorance of Access as a justification for doing so.

Application design using Access does have some trade offs (notable
limitations with transactions, security, and GUI design), but 'load' is not
one of the limitations. In particular, the idea that Access "can't handle
more than N users" is just a myth.

Since I haven't talked to your IT personnel, I can't say if they are just
completely ignorant about Access, and have bought the myth.

Or want to get rid of your application and have it re-built because they
know it's not designed to scale to 30 users, and will never work properly in
it's present form.

Since you have to work with these people, you have to carefully try to work
out what the problem is. At the moment, what you have reported doesn't make
any sense in the terms you have described it. There is no 'limit to Access'
for the situation you have described, and the Access front end handles one
person per Citrix session, the same with 1 user or 1 million.

(david)


"Fed semi-technical user" <Fed semi-technical
(e-mail address removed)> wrote in message

 

[Tony Toews [MVP]]

Fed semi-technical user <Fed semi-technical

We have an Oracle database table set up with two Access front ends to input
data. The Access front ends resided on a network drive so all users went to
the same two files. On our local server we had about 30 users, but probably
only 5-7 concurrent users. We know the Access files limit the users so only
one can update a specific record. We occaisionally had to wait before saving
a record.

Now we are needing to have this all moved to a server farm. The files would
be operated on a network drive through Citrix. At the same time there is a
desire to increase users to 150 to perhaps 350. Concurrent users would
increase accordingly. Our IT personnel think Citrix can handle the load, but
don't think the Access front ends will handle it. What are the limits to
Access? Can it handle this load?

There was a reputable posting a number of years back of a Access FE
with t0,000 users world wide. If I recall correctly is was for time
keeping so each person would only be entering one or a few records per
day.

So long as each user gets their own copy of the Access FE and you are
using a database engine such as Oracle or SQL Server you can have as
many users as the server farms can handle.

Tony
--
Tony Toews, Microsoft Access MVP
Tony's Main MS Access pages - http://www.granite.ab.ca/accsmstr.htm
Tony's Microsoft Access Blog - http://msmvps.com/blogs/access/
For a convenient utility to keep your users FEs and other files
updated see http://www.autofeupdater.com/
Granite Fleet Manager http://www.granitefleet.com/