[AccessD] Max quantity of simultaneous users

Jim Lawrence accessd at shaw.ca
Mon Dec 10 11:53:30 CST 2007


I confer with Drew on this... one more point is to avoid the 'ORDER BY' when
ever possible, if the order required is simple you can SORT your recordset
or leave the ORDER BY clause to the last outer-most statement.

A good point Drew, about not editing the data. Using tags and flags can
control the display. Once data has been saved it should never be removed
from the system. It can be flagged as deleted or archived. If ever forensic
auditing is needed a complete is crucial.

Can not improve on Drew's assessment, otherwise.

Jim   

-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Drew Wutka
Sent: Monday, December 10, 2007 8:59 AM
To: Access Developers discussion and problem solving
Subject: Re: [AccessD] Max quantity of simultaneous users

Depends on your database design.  Access will physically allow up to 255
users, though I'm sure you knew that.  That many people using one file,
however, can get problematic.

Where I work, we have several live Access based applications.  And their
user count varies.  The ISFE is our Help Desk package.  It has an Access
2000 format .mdb as the backend, with a VB 6 front end.  Number of
concurrent users...right now, ~140.  Another big database is Glovia
Reports, basically a report engine for our Oracle based ERP package.
Glovia Reports can get brought down to it's knees if more then a dozen
people are in it.

Not to gloat, but I designed and built the ISFE.  Glovia Reports, on the
other hand, was built long before I got here, and I rarely do anything
but provide emergency support for it.

What's different between the two?

First BIG block for GR (Glovia Reports) is that some 'reports' are
actually macro based.  These macros run various queries of the data in
Oracle, and build 'temp' tables in the database.  It does this using
make table queries.  After these queries are run, the macro then opens a
report based off of these temp tables.  In Access 97, this ran just
fine.  In the Access 2000 format, if someone has a report open, using
these tables, it prevent anyone from running the make table queries.
(and it gives them a goofy message...says they don't have permission,
instead of saying the tables are locked).

Query upon Query.  The folks that write these reports are not expert
query writers.  They know the data, but are confused with subqueries and
pass through queries.  Thus, this database has tons of queries, which
are usually 5 or 6 levels deep.

One complete database, GR is one database, including both local tables,
linked Oracle tables, forms, queries, reports, macros.  All in one .mdb.

In conclusion, here's some tips on how to maximize user connections:

1 - Split the database.  Make a Backend with just the data, and a Front
end with the interface.
	1a.  Using a Non-Access Front End can increase performance and
max users too.  An unbound approach is also helpful, but hands down, to
get the absolute max number of users out of an Access .mdb, put it
behind a web based interface.  In theory, you will max out your IIS
server before you'll ever max out the number of users in the .mdb.

2 - Use Unbound thinking.  The theoretical difference between bound and
unbound, is that bound is a solid 'always connected' state, where as
unbound is 'catch and release'.  If User A opens a data entry form, that
is bound to it's data source, you are using up a connection to the
database (and locking parts of that table) the entire time User A has
that form open.  If User B opens a data entry form that is unbound, the
connection and locking is reduced to the times that User B is actually
reading or writing data. 

3 - Normalize your data and INDEX it.  A properly normalized and indexed
data structure will provide data to the users much faster.  Faster data
retrieval means less connection and locking time.

4 - Avoid Editing data whenever possible.  For instance, in an inventory
system, instead of editing a 'On Hand Count' field, create a transaction
table.  Positive numbers for putting something into inventory, negative
for taking it out.  Sum that table for the On Hand Count.  Sometimes
editing data is the best route, but trust me, in a good database design,
you will find very few cases where data is actually edited.  

Those are the biggies.  IMHO.

Drew

-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Rudi
Adriaenssens (De Beukelaer)
Sent: Monday, December 10, 2007 9:34 AM
To: Access Developers discussion and problem solving
Subject: [AccessD] Max quantity of simultaneous users

Hi,

Has anyone an idea of the reasonable maximum simultaneous users in an
access database?
We have a setup of a back end database containing all data into one mdb
(about 60 tables) and a separate front end mde for every user (11) with
no actual data. The front end is into a separate directory for each user
on the same server.

All works fine, and from time to time some users ask specific extra
functions who are made if possibly.
Since a few weeks we have some problems, usually some error like "can
not write data".

Thx in front for your reply.
 

Rudi Adriaenssens
De Beukelaer
Boomsesteenweg 77
B-2630 Aartselaar
Belgium
########################################################################
################
De Beukelaer mail server message:

This e-mail message is privileged and confidential and property of De
Beukelaer company.
If you are not the intended recipient please delete the message and
notify the sender. 

This e-mail message has been scanned for Viruses and Content.
########################################################################
################

-- 
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com

-- 
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com




More information about the AccessD mailing list