Access MDA vesus SQL

[Guest]

i have created a database that will be used by 20-70 peronnel at any one
time. some users will be using forms, reports and queries at the same time.
i've created fe/be and have had 20 personnel using it with no accessibility
problems. i have read and researched but haven't found a reference that
states after XX users on a LAN environment upsize to SQL application.

my question: is there a rule of thumb that after XX amount of users with a
mda database need to upsize/upgrade to a SQL access application?
thanks for your time as i'm a novice and are self taught using acces.
regards douglas jones

 

[Guest]

update: my mistake shoud be mdb not mda database.

 

[John Vinson]

On Tue, 15 Mar 2005 18:45:02 -0800, "douglas jones" <douglas

i have created a database that will be used by 20-70 peronnel at any one
time. some users will be using forms, reports and queries at the same time.
i've created fe/be and have had 20 personnel using it with no accessibility
problems. i have read and researched but haven't found a reference that
states after XX users on a LAN environment upsize to SQL application.

my question: is there a rule of thumb that after XX amount of users with a
mda database need to upsize/upgrade to a SQL access application?
thanks for your time as i'm a novice and are self taught using acces.
regards douglas jones

Access is limited to 255 concurrent users; in practice, the number of
users that it can support depends much more on the design of your
database (queries, forms, etc.) and the robustness of your network
than on anything else. Far more read-only users - usually over a
hundred easily - can be accommodated than if all users are doing heavy
editing or data entry. A SQL backend can lead to DEGRADED performance
unless it's designed carefully.

You need to upgrade to SQL when *your experience shows* that a
properly optimized JET backend cannot handle your needs, whether due
to performance problems, frequency of corruption, or frequency of user
conflicts. This number might be 200 or it might be 21, depending on
your database (and your users' tolerance level).

John W. Vinson[MVP]

 

[david epsom dot com dot au]

My rule of thumb is that the data should be migrated
to SQL Server if the users can't handle downtime of
up to 1 hour, and loss of data back to the last backup.

This is only loosely related to the number of users.

Implementing robust backup and roll-over systems on
a file server is even more difficult than it is with
SQL Server.

(david)

 

[Rick Brandt]

i have created a database that will be used by 20-70 peronnel at any
one time. some users will be using forms, reports and queries at the
same time. i've created fe/be and have had 20 personnel using it with
no accessibility problems. i have read and researched but haven't
found a reference that states after XX users on a LAN environment
upsize to SQL application.

my question: is there a rule of thumb that after XX amount of users
with a mda database need to upsize/upgrade to a SQL access
application?
thanks for your time as i'm a novice and are self taught using acces.
regards douglas jones

I would add to the other replies that if you can get your organization to
install, set up, and administer a SQL Server box and your only involvment is
in *using* that box, then there is never a reason NOT to use SQL Server over
an MDB. Where the decision comes in is deciding to set up a SQL Server
"because an existing Access application has outgrown Jet".

IOW I wouldn't want to be the guy pitching the idea of a SQL Server box *as
a need* to management and how much better the application will be once the
data is moved to a SS box unless I really knew what I was talking about and
my arguments were technically sound. However; if they are already
considering it, I would certainly be an advocate for the idea.

 

[Guest]

thanks

doug

I would add to the other replies that if you can get your organization to
install, set up, and administer a SQL Server box and your only involvment is
in *using* that box, then there is never a reason NOT to use SQL Server over
an MDB. Where the decision comes in is deciding to set up a SQL Server
"because an existing Access application has outgrown Jet".

IOW I wouldn't want to be the guy pitching the idea of a SQL Server box *as
a need* to management and how much better the application will be once the
data is moved to a SS box unless I really knew what I was talking about and
my arguments were technically sound. However; if they are already
considering it, I would certainly be an advocate for the idea.

 

[Guest]

thanks!

doug

On Tue, 15 Mar 2005 18:45:02 -0800, "douglas jones" <douglas


Access is limited to 255 concurrent users; in practice, the number of
users that it can support depends much more on the design of your
database (queries, forms, etc.) and the robustness of your network
than on anything else. Far more read-only users - usually over a
hundred easily - can be accommodated than if all users are doing heavy
editing or data entry. A SQL backend can lead to DEGRADED performance
unless it's designed carefully.

You need to upgrade to SQL when *your experience shows* that a
properly optimized JET backend cannot handle your needs, whether due
to performance problems, frequency of corruption, or frequency of user
conflicts. This number might be 200 or it might be 21, depending on
your database (and your users' tolerance level).

John W. Vinson[MVP]

 

[Guest]

thanks

doug

My rule of thumb is that the data should be migrated
to SQL Server if the users can't handle downtime of
up to 1 hour, and loss of data back to the last backup.

This is only loosely related to the number of users.

Implementing robust backup and roll-over systems on
a file server is even more difficult than it is with
SQL Server.

(david)