What are the Limitations of Access - in terms of:
- Number of users,
- Simultaneous connections
- File size?
It is vital to note that a well written Access application on a good quality network will handle 12 users with blinding speed and no problems. A poorly designed application on can reduce a network with 5 users to a grinding halt.
This said, if you're currently working with Microsoft Access, you might find it necessary to upsize your database (and any associated applications) to SQL Server if it becomes too big. Microsoft's rule of thumb for determining whether you should upgrade from Microsoft Access to SQL Server 7.0/2000 is 1. More than 50 concurrent users 2. and/or a database that's greater than 100 MB.
Of course, all rules are meant to be broken, and this one is no exception. But, if your database's performance is suffering, and more importantly, your users are complaining, you might be better off upsizing to SQL Server.
Apart from this, there are several key reasons to upgrade to SQL Server :
1. Reliability & No Corruption Issues - SSW has never seen SQL Server become corrupt. On the other hand, we have seen hundreds of corrupt (and often unrecoverable) Access backend databases. If you are having persistent corruption issues, now would be a good time to move to MSDE or SQL Server.
2. Security - ability to restrict users on every object. SQL Servers security model is a lot more comprehensive than Access's. In addition, Access security can be compromised because it is file based - users must be given access to the data file in order to use it. Therefore they can copy it and take it home, and have a crack at it all night long...
3. Speed - SQL Server is a server, not a file based database. Let me give you a simple example. A company has 1 million records in it's Accounts Receivable table (Access .MDB file). An Access app wants to display for the user the sum total of the accounts receivable - Just one calculated field - To do that, Access must transfer the entire table over the network and perform the math on the workstation. Many megabytes of traffic which can bring a network to it's knees for a few minutes. Just to show the one calculated field. With stored procedures, instead of dragging all of the data the SQL Server, all the hard work is performed on the server. Only the answer is returned. In addition, a stored procedure based app can help you
4. Connections - Access can handle only about 10 concurrent connections. Any non-trivial web site runs on SQL Server - need I say more? 5. Scalability - Once your site gets really hot, which will happen over time and/or a successful marketing campaign then SQL Server can be scaled up by using "clustering". This basically means more boxes can be helping the load - handle more and more users at very fast response times.