MS Access vs MS SQL Server

MS Access is actually a combination of a rapid development UI tool and file system based relational database (JET).

Pros:
- Easy deployment, just copy the file out to the network and tell people the path to it. - Rapid prototyping and very good UI development tools for database type applications - Generally much cheaper to deploy, especially for small installations.

Cons - Maintenance - You have to lock out all the users when you want to do DB compaction, repair or other maintenance. - As a result of using a file based DB, it is more prone to data corruption with a large number of users or with flaky network connections. - Although the number is debated, you are going to max out the number of users you can support with a single Access DB much sooner than with SQL.

MS SQL Server is a Client-server relational database system, with no UI development tools built in.

Pros:
- Maintenance - Lots of tools for maintenance, can do most of it with users in DB. Thus, you get higher uptime.
- Enterprise scale - it is designed to support a lot more users and scales better to handle them.

Cons: - Expensive - For your 5 user app to keep track of a few hundred thousand items, it can be overkill.
- More complex - All the extra features introduce a learning curve. - No built-in UI development tools - You will need another dev platform to build a front-end and (arguably) reporting. Actually, Access can work as a front end for SQL, but for a number of reasons DBAs tend to hate Access users connecting to their databases.

Miscellany
- Although you may hear otherwise, don't make the decision based on the size of the DB, make the call based on features and size of your user base.
- Access actually is a pretty good UI tool for databases hosted in SQL Server. So the right answer might be "Both" depending on your needs.

The question is silly. SQL Server and Access aren't even the same type of product. It's like asking what's the difference between a hotel and a Mack truck -- the question doesn't even make sense.

The reason why is because SQL Server is a database engine only, while Access is a database application development tool. SQL Server is for storing data, while Access is for building applications to manipulate data.

Now, Access ships with a default database engine, Jet/ACE, and because of that everybody casually uses "Access" to mean the database engine. They are wrong and imprecise when they use Access and Jet/ACE interchangably, and it leads to all kinds of confusion if you don't specify which you mean. While you can't compare Access strictly speaking to SQL Server, you can certainly compare SQL Server to Jet/ACE, because then you're comparing database engines, not apples and furniture.

I won't go into detail, but will say that deciding which database engine is most appropriate is going to depend on the tasks you're asking it to do. It's much like buying a vehicle for transportation. If you're a single individual and just need a car to go to the supermarket and the mall, you're going to do just fine with a Mini Cooper. If, on the other hand, you are a business and need to transport 100s of boxes of inventory back and forth between your warehouse and store, you're going to need a Mack truck.

It would be silly for the individual to try to use a Mack truck just to go to the mall and the supermarket, and equally silly to try to transport your business's inventory using a Mini Cooper. Both can be done, but it's awfully inconvenient.

So, you have to decide what purpose you're using a database engine for and then choose the database engine that is the best fit for the purposes at hand. There are plenty of scenarios where Jet/ACE is more than adequate and will make things very easy for you.

There are also plenty of scenarios where it would unwise to start with it, and you should go with a server database engine like SQL Server. Jet/ACE is going to be inadequate for any number of scenarios, and SQL Server is going to be vast overkill for others, but you can't tell which until you examine the specific requirements of any particular scenario.

Last of all, Access is a great tool for building your front-end application either with a Jet/ACE database behind it or a SQL Server database. Indeed, it's quite easy to upsize from Jet/ACE to SQL Server if you've been careful to create an efficient Access application, and that's a very common scenario, i.e., start with your Access front end and a Jet/ACE back end, and as needs grow, you upsize to a server back end, and keep the existing Access application.

Access is intended more for end-user(s), as it's an all-inclusive package. It's mainly for one use at a time, but it does support multiple users, albeit in a clunky roundabout way. Remember, it is a tool in MS Office.

MS SQL Server is an actual SQL database. It's designed to sit on a server an act as the database for whatever front-end you wish to put on it. It's mainly for multiple users and intended to be centralized.