25 users and growing trying to decide db/tracking program to imple

[BrianC]

We're a larger insurance agency, trying to implement a agency management
system. I have used MS Access to this point, but as we've recently gone from
3-4 users to over 20 ... I'm worried that we will experience issues with
Access. I'm by no means an access expert, but looking for advice. We have
also always used on a local area network, but now have multiple locations and
will have to run over a WAN ... any advice/help?

 

[Graham R Seach]

Brian,

Access will easily handle many more than 20 simultaneous users, with several
provisos: Firstly, that the system is split into a frontend (containing all
the user elements like forms, queries, reports and so on), and a backend
(containing only the tables). Each user should have their own local copy of
the frontend. Secondly that it was designed/developed correctly, using
proper multi-user techniques and with optimised queries. Thirdly, that the
network is stable. Since you're talking about a WAN, and assuming you're not
using Terminal Services technologies, you would be wise to consider upsizing
the backend to something more stable/tolerant, like SQL Server (more
probably MSDE or Express, which are both free). If you do have Terminal
Services, then you can run it very well over a WAN.

Regards,
Graham R Seach
Microsoft Access MVP
Sydney, Australia

 

[(PeteCresswell)]

Per BrianC:

We're a larger insurance agency, trying to implement a agency management
system. I have used MS Access to this point, but as we've recently gone from
3-4 users to over 20 ... I'm worried that we will experience issues with
Access. I'm by no means an access expert, but looking for advice. We have
also always used on a local area network, but now have multiple locations and
will have to run over a WAN ... any advice/help?

Distinguish between Access-the-front-end-development-tool and
Access-the-back-end.

I'm guessing you have an MS Access front end linked to an MS
Access back end (aka a "JET" back end).

Any capacity/concurrent user limits will be found in the back end
only, and the back end can be migrated to an industrial-strength
database like SQL Server without totally re-writing it.

That is not to say that migrating it would be trivial.
I'd guesstimate a max of 30% of the man hours used to write the
original app to do it right.

Somebody may chime in that they can do it in two man hours. I
can't dispute that, but my experience is that it's usually a good
idea to make more changes than just re-linking tables to SQL
Server. YYMV.

 

[David W. Fenton]

That is not to say that migrating it would be trivial.
I'd guesstimate a max of 30% of the man hours used to write the
original app to do it right.

I think that depends on how the original app is written. Since about
1998, I've been writing all my Access/Jet apps with upsizing in
mind. In other words, I never bind a form to an entire table, or do
any of the things that the Access examples encourage that are bad
practice in a client/server environment.

The result is not just that the app upsizes very easily, but that it
actually runs a lot more efficiently *before* the upsizing.