Can't you have more than 255 concurrent Access users programming .

[Guest]

Is it true or not that programming using unbound forms in Access, you can
have more than 255 concurrent users? This has been a heated discussion with
some of the other users in the help forums and they say Access is not an
Enterprise solution. I've been successful in having quite a number of users
concurrently using Access utilizing unbound forms and writing functions to
write/update/retrieve records (on almost a 24x7 basis). No one else seems to
agree. Their statement is that MS Access should NOT be used in a multi-user
environment but I use it this way all the time without problems!

 

[Douglas J. Steele]

255 concurrent users is an absolute limit, although I doubt very much that
you'd be able to get close to that number in practise.

Note, however, that this only applies to Jet databases. If you've got an
Access front-end going against a non-Jet backend (say SQL Server or Oracle),
you don't have this limitation (since each user should have his/her own copy
of the front-end).

 

[Albert D. Kallal]

In theory, if you closed all connections to the back end, and THEN let the
user edit the data, and then opened the back end, and sent the data back,
then likely you could have more then 255 users at the same time.

however, with a un-bound form, you likely still opened a reocdset, grabbed
the values, put them into the form. If you did not close that reocrdset then
you still got a connection..don't you? So, you save nothing unless you
actually close the connection.

However, what this means is that you are actually using MORE RESOURCES here.
if you use a bound form we get:

user opens table
one record is retrieved to the bound form (you use the where clause all
the time..right???)
users edits data via bound form
data is written back
user closes table

In your case you got:

user opens table
uses grabs data (record is retrieved)
user closes table
use now edits data on un-bound form
user opens table
We now have to find/retrieve record in the data base
now data is written back to disk
user closes table

So, in fact, you are opening and closing the table MORE with un-bound forms,
and in fact are creating MUCH MORE network traffic. (the record is
retrieved more the once here, and the HUGE COST of opening the table is done
two times).

So, while in theory you might get more then 255 uses, you would be putting a
MUCH higher load on the network then if you used bound forms.

Further, in actual practice, anything approaching 100 users is pushing
ms-access anyway. Some have managed to push it over 100 users, but in actual
practice, you will get no where even close to 100 users, and thus your
worries about 200 or more users is not even to be consider here.

Of course, if you are talking about a server based back end, like sql
server, then you can have 1000+ users..and still continue to use bound forms
with out any performance problems at all (assuming your application is well
written).

It is not clear if you are taking about a JET file share back end, or a
server based one. But, if you are using a server based system, then you can
certainly go over 255 users without any problem at all...

 

[david epsom dot com dot au]

There is no limitation on the number of read-only users,
if you put the database into a read-only folder, and take
it off line to do updates.

There is no limitation on the number of users if you use
pooled connections (as you might for a web site).

Access tracks the machine name (and access user name)
for each connection, and the list has a logical limit
of 255 connections. This would normally translate to
rather fewer users if all the users had persistent
connections: Access applications typically use more
than one connection per user.

Most of the people who criticize Access as unsuitable
for enterprise applications don't use Access, don't
know what they are talking about, and are laughably
ignorant in their justifications, but that doesn't mean
that the conclusion they parrot is wrong.

I myself would hesitate to use Access/Jet in any situation
where the user couldn't afford to be offline for an hour,
with loss of data back to the last backup.

(david)

 

[Guest]

In theory, if you closed all connections to the back end, and THEN let the
user edit the data, and then opened the back end, and sent the data back,
then likely you could have more then 255 users at the same time.

however, with a un-bound form, you likely still opened a reocdset, grabbed
the values, put them into the form. If you did not close that reocrdset then
you still got a connection..don't you? So, you save nothing unless you
actually close the connection.

However, what this means is that you are actually using MORE RESOURCES here.
if you use a bound form we get:

user opens table
one record is retrieved to the bound form (you use the where clause all
the time..right???)
users edits data via bound form
data is written back
user closes table

In your case you got:

user opens table
uses grabs data (record is retrieved)
user closes table
use now edits data on un-bound form
user opens table
We now have to find/retrieve record in the data base
now data is written back to disk
user closes table

So, in fact, you are opening and closing the table MORE with un-bound forms,
and in fact are creating MUCH MORE network traffic. (the record is
retrieved more the once here, and the HUGE COST of opening the table is done
two times).

So, while in theory you might get more then 255 uses, you would be putting a
MUCH higher load on the network then if you used bound forms.

Further, in actual practice, anything approaching 100 users is pushing
ms-access anyway. Some have managed to push it over 100 users, but in actual
practice, you will get no where even close to 100 users, and thus your
worries about 200 or more users is not even to be consider here.

Of course, if you are talking about a server based back end, like sql
server, then you can have 1000+ users..and still continue to use bound forms
with out any performance problems at all (assuming your application is well
written).

It is not clear if you are taking about a JET file share back end, or a
server based one. But, if you are using a server based system, then you can
certainly go over 255 users without any problem at all...


--
Albert D. Kallal (Access MVP)
Edmonton, Alberta Canada
(e-mail address removed)
http://www.members.shaw.ca/AlbertKallal

The advantages of using unbound forms though is that a lot of times we have users who will "sit" in a record. We've had problems in this situation with other users accessing the same tables especially in our Citrix type environment. You say that creating unbound forms uses more resources but from my experience, using bound forms seems to be more problematic. Thanks for the reply but I'm still not convinced that in a Citrix type of environment, programming using bound forms is the route to go.

 

[Guest]

Yes we do use SQL Server as a back-end but the question is if I were to
create an Access application using Access tables and programm the forms using
unbound techniques, could I conceivably get more than 255 users in the same
database? Thanks for the reply - I wonder if this has ever been tested and
the type of environment (we use Citrix).

 

[Douglas J. Steele]

AFAIK, bound vs. unbound forms won't make any difference.

 

[Tony Toews]

Yes we do use SQL Server as a back-end but the question is if I were to
create an Access application using Access tables and programm the forms using
unbound techniques,

I don't know why you'd want to go with unbound techniques. Bound
forms should still work.

could I conceivably get more than 255 users in the same
database?

Yes. There is one report of someone with 10,000 users in an Access FE
against a SQL Server BE.

I wonder if this has ever been tested and
the type of environment (we use Citrix).

FYI I specifically created the Auto FE Updater utility so that I could
make changes to the FE MDE as often as I wanted and be quite confident
that the next time someone went to run the app that it would pull in
the latest version. For more info on the errors or the Auto FE
Updater utility see the free Auto FE Updater utility at
http://www.granite.ab.ca/access/autofe.htm at my website to keep the
FE on each PC up to date.

In a Terminal Server or Citrix environment the Auto FE Updater now
supports creating a directory named after the user on a server. Given
a choice put the FE on the Citrix server to reduce network traffic and
to avoid having to load objects over the network which can be somewhat
sluggish.

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 epsom dot com dot au]

Yes we do use SQL Server as a back-end but the question is

You are limited to 255 users simultaneously using one or/w
Access MDB as a front end to SQL Server, but even in a
Citrix environment this would be an unusual application,
particularly now with large hard disks so cheap.

Firstly, using a shared FE file makes it more difficult
to use 'local' tables in the FE - and the flexibility that
comes from the use of 'local' tables in the FE is one
reason that people use an Access FE.

Secondly, it is rare to share files for or/w at all, even
using Citrix, an that should be avoided as much as possible,
just so that you stay in the main stream of common use.

Note that if you plan to have 255 Citrix users using
Access, you probably need to have them using the Access
Runtime version and license - otherwise they each need
to have an Access (Office Pro) licence: one license for
each workstation where the application is used.