As the most Access-savvy person in my office, I have been handed the
task of proposing a nationwide expansion of a project that I have
developed in Access. A brief overview:

Right now, about 25 people process about 5 transactions per day.
(Total, not each) The back-end database resides on a server not
located in my office, but nearby. The transactions are the results of
certain law enforcement activities. Pretty low volume for new
transactions, with about 10 users per day accessing or editing
existing records. The problem is, that the program is so successful
that is has been requested nationwide. The plan is to add 1 new city
per month, with each city contibuting approx. 10 new transactions per
day. The total number of cities using the software will be about 60.
As I am fairly new to this, what can you recommend ? Should I stick
with Access, or is it unsuitable for my needs ? Network issues are
not a concern, as it will reside on a server in Maryland, and the
nationwide WAN is already in place. Any hints or ideas would be
appreciated. Thank you.

The general feeling is that Access starts to fail at over 20
simultaneous users. However this depends on what the suers are doing,
network speed, user expectations,update vs. lookup, etc.

If I were you I'd stick to Access for the moment. Be sure to have the
file in Access 2000 format and use record level locking.

If things get bad you can move to SQL Server using the upsizing
wizard. The wizard does a lot of work for you.
If you think you will want to do that let me know and I'll send you
some tips.

Good luck
Ira Solomon

On Mon, 17 May 2004 00:03:52 GMT, user451 (AT) anon (DOT) com wrote:

I would be very careful if I were you. If all your national users are
meant to log into the access database then you will have big problems.

I have consulted for several firms who get users in different cities
to log onto a common database over a Wide Area Network. For the users
who work in the same location as the backend, there is no problems,
but for the rest of the country there is hell.A backend of about 2meg
can take over 2 minutes to load , forms can take very long times to
appear etc. Running queries remote from the backend, can be torture.

This of course was for companies who didn't have great bandwidth.
Access though is renowned for chewing bandwidth. This is exacerbated
by database owners who never compact their databases, which should
happen regularly.

Don't rely on access allowing users to access your database over a
WAN. Access should only be used when the users are all in the same
location.

On the other hand if your database will be used as seperate modules
then you shouldn't have many problems. Ie, city 1 people use the city
1 database, city 2 people use the city 2 database etc.

What I have seen happen in this case is that users request
modifications in one city, and the modifications don't get copied to
the other cities. Before too long the databases are looking totally
different and become incompatible with each other.

What I recommend is if that each city should have their own database
with thier own backend/front ends. Run batch updates to maintain a
master set.

Make sure everyone is running the same version of Access also.

I hope this helps!



Ira Solomon <isolomon (AT) solomonltd (DOT) com> wrote