How many "simultaneous users" can Access support?

[Mel]

1 - How many "simultaneous users" can Access support?

2 - Is the problem the backend computer resources or the Access code?

3 - Is there any tips for minimizing the potential issues?

4 - Other comments and tips welcome.

(We run Access on a peer-to-peer network. Back End on a machine we
call the Network Drive... but it is just running XP... now Windows
Server.)

Thanks for any help.

Mel

 

[Rick Brandt]

1 - How many "simultaneous users" can Access support?

The technical specification is 255. The ACTUAL limit is the number of users
before problems occur and that will vary from one application/environment to the
next. When all things are at optimum then 50 to 100 is not unheard of. 20 to
30 should be manageable in most cases if the application and network are not
terrible. Problem is many apps ARE terrible.

2 - Is the problem the backend computer resources or the Access code?

The back end is just a remote hard drive. If that machine has slow disk access
then it could be a problem or if the network connection between front end and
back end is not reliable that would DEFINITELY be a problem.

3 - Is there any tips for minimizing the potential issues?

IMO the two most important considerations are to design the app so that the
absolute minimum of bytes are pulled over the network and to use a split app
where only the data tables are shared and each user has their own local copy of
the file with forms, reports, etc.. Next in line would be to use very solid
network hardware and avoid wireless if you can.

4 - Other comments and tips welcome.

(We run Access on a peer-to-peer network. Back End on a machine we
call the Network Drive... but it is just running XP... now Windows
Server.)

Well that will immediately limit you to 10 simultaneous connections. You need a
server OS to go any higher.

 

[Douglas J. Steele]

The official limit is 255 simultaneous users, but I think most developers
would agree that's rarely ever attainable. A lot depends on how well
designed the database is, and what the usage pattern is (are most of them
only reading the database, or are they performing updates?). A poorly
designed database where everyone's trying to do updates can have problems
with only 2 concurrent users.

However, I believe there's a limit to the number of connections that can be
made to an XP workstation from the network, and I suspect that's far more
likely to be the bottleneck.

 

[Scott McDaniel]

1 - How many "simultaneous users" can Access support?

According to the documentation, 255 is the max. In practice, however, that number is typically much lower. A lot depends
on what you're doing with the application, the speed/robustness of your network and workstations, etc etc ...

2 - Is the problem the backend computer resources or the Access code?

It can be either, but in general the backend computer (i.e. the "server") is less important than the workstation and
network speed, at least as far as Access is concerned.

Access (actually Jet, the database engine that's included with Access) is a file server database. The "backend" is
little more than a file, and has no query parser, etc etc ... the workstation must do all the heavy lifting like parsing
queries, updating/deleting/adding records, etc etc, and to do that the workstation must pull the needed tables across
the network. Needless to say, when the tables begin to grow this can be a big task. Proper indexing can help to some
degree (if your query uses only indexed fields, then the workstation can use the index tables, which are much smaller)
but too much indexing can slow data entry - so there's a trade-off, to some degree and at some point even good indexing
won't help.

Compare this to a client server database like MS SQL, MySQL, Oracle, etc. In those cases, your application can send a
request to the database engine and the db engine will parse that request and return a recordset (or other object) with
only the requested records ... this can significantly reduce network load and make response times much faster.

Note that your Access application can use these server databases as well, so you can always keep your Access frontend
and migrate your data to a true CS database engine in the future.

3 - Is there any tips for minimizing the potential issues?

Check Tony Toews site:
http://www.granite.ab.ca/access/tipsindex.htm

specifically the Best Practices and Performane FAQ sections. He also has links to several other sites and MS KB articles
that give goo advice.

4 - Other comments and tips welcome.

(We run Access on a peer-to-peer network. Back End on a machine we
call the Network Drive... but it is just running XP... now Windows
Server.)

Thanks for any help.

Mel

Scott McDaniel
scott@takemeout_infotrakker.com
www.infotrakker.com

 

[David W. Fenton]

Access (actually Jet, the database engine that's included with
Access) is a file server database. The "backend" is little more
than a file, and has no query parser, etc etc ... the workstation
must do all the heavy lifting like parsing queries,
updating/deleting/adding records, etc etc, and to do that the
workstation must pull the needed tables across the network.

NO, NO, NO, NO.

If the table is properly indexed, only the needed portions of the
index pages will be pulled across the wire, and then only the data
pages storing the requested records will be pulled across the wire.

The only circumstance in which the whole table will be pulled is
when there are no criteria or all the criteria are on non-indexed
fields and there are no joins with other tables.

 

[Tom Wimpernark]

1 Access can't reliably support a half-dozen users

2 move to SQL Server

3 move to SQL Server

4 move to SQL Server

 

[Tom Wimpernark]

oh douglas

a poorly designed database, where everyones trying to do updates can't
support 2 users/

that is the most ridiculous thing i've ever heard in my life

 

[Tom Wimpernark]

you shouldn't use linked tables.. I mean-- what a joke

you should be using Access Data Projects, it is 10 times simpler to manage

things like index tuning wizard, database maintenance wizard--= it's just a
ton easier to manage SQL Server

MDB has been obsolete for a decade

 

[Tom Wimpernark]

bullshit dork

stop spreading lies, kid

NO, NO, NO, NO.

If the table is properly indexed, only the needed portions of the
index pages will be pulled across the wire, and then only the data
pages storing the requested records will be pulled across the wire.

The only circumstance in which the whole table will be pulled is
when there are no criteria or all the criteria are on non-indexed
fields and there are no joins with other tables.

 

[George Hepworth]

Aaron Kem.pf is posting under a new alias.

you shouldn't use linked tables.. I mean-- what a joke

you should be using Access Data Projects, it is 10 times simpler to manage

things like index tuning wizard, database maintenance wizard--= it's just
a ton easier to manage SQL Server

MDB has been obsolete for a decade

 

[George Hepworth]

Aaron Kem.pf is posting under a new alias.

oh douglas

a poorly designed database, where everyones trying to do updates can't
support 2 users/

that is the most ridiculous thing i've ever heard in my life

 

[George Hepworth]

Aaron Kem.pf is posting under a new alias.

1 Access can't reliably support a half-dozen users

2 move to SQL Server

3 move to SQL Server

4 move to SQL Server

 

[David W. Fenton]

Aaron Kempf playing games posting as <[email protected]>
blathered in
[nothing worth reading]

Haven't you got anything better to do with your time?

What a loser!