size limit

[JHipp]

How big can an Access db get before the user should think
about upgrading to somethiing like sql server?

Is there a size limit? 10MB, 100MB 500MB?

Is there a prudent number of objects (tables, queries
etc) limit? 100, 500, 1,000?

Is there a prudent number of users limit? 5, 20, 100?

Many thanks

Jonathan

 

[Albert D. Kallal]

It is difficult to come up with a particular number. It really depends on
how well the application is written, and how well of a user environment
everything is run under.

If after compacting a mdb file, (no forms, reports, but just tables), you
have a file in the 500 meg range or more, that is getting rather large.
However, 500 megs is not too big for a single user when no network is
involved.

The same goes for the number of users, it will much depend on how reliable
the pc's on the network are, and how well the application is written.

however, when you get beyond 20 users, then likely the value of the data,a
nd the value of a work stoppage failure gets quite high. I mean, one person
working and entering data represents one person day of data. However, if
you have 20 users, and a file is damaged, you now loose 20 persons days of
productivity. You have weigh this value.

On a good network, with stable pc's, you can easily run 20 users, but the
value of that many users means that sql server can also be justified. I
can't imagine a company reaching 20 users with 20 computers. At 25
computers, you generally be close to having a one person FULL TIME support
that many pc's. Hence, the risk of data loss means that sql server can be
justifed with ease.

 

[JHipp]

Thank you Albert for that reply.

Do you know of a spot where I might find a collection of
opinions like yours? I'm trying to persuade a client to
upgrade, but they keep quoting the table at

http://www.microsoft.com/sql/techinfo/planning/SQLAccess.a
sp


which says you can go to 2GB. I know you can't, but they
don't want to fork out for sql server. Instead they wast
every other day repairing their 350MB database.

Thanks again for your help.

 

[Kevin @ 3NF]

Can you give some specifics as to your database? # of users, anticipated
size? current performance?

SQL Server does have two really sweet features....security based on NT login
or SQL Server username, and the ability to backup during operations and
restore to a certain point in time. I have a client I can bring back to
within 5 minutes of when a problem occurred. This client did have 15 users
on an Access back-end, which was corrupting 2-3 times a day, forcing a
server reboot to clear it. SQL Server went live late June, and hasn't had a
problem yet.

Sell them on stability, scalability, speed (if properly designed) and
security. That assumes of course that your environment warrants SQL Server.

You can take an Access database pretty close to 2GB, but repair and compact
is going to be a nightmare when you get to that point. It takes a while at
350, as I'm sure you've noticed...