Take the 2-minute tour ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

These are two offerings from Microsoft - MS Access and MS SQL Server.

What type of users/uses is Access meant for? Apart from the degree of usage (in terms of number of users), are these two products the same?

share|improve this question
add comment

4 Answers

up vote 4 down vote accepted

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.

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.

share|improve this answer
1  
A note about the cost of SQL Server, if the need isn't too great, SQL Server Express may work as it's free. There are limitations to it though (microsoft.com/sqlserver/2008/en/us/express.aspx). But it does provide a good step up from Access when the MDB file reaches that daily repair-and-compact requirement to keep it running. –  Agent_9191 Aug 17 '10 at 1:57
    
Your Pros/Cons of Access make no sense except if you're using WORST PRACTICES for distributing your app. No one with any sense distributes an Access app except split into front end (forms/reports/etc.) and back end (data tables only). Your Pros/Cons completely disappear when following best practices. –  David W. Fenton Aug 17 '10 at 21:51
    
@David - Perhaps I was unclear. By easy deployment I basically meant that it is easier to deploy an app to another party. That is, you don't have to walk them through installing SQL Server or licensing it, which can be a pain for a user that just wants to get up and running on a small system quickly. I don't think anything I said implied that you WOULDN'T spit it into front/back end, but thanks for the clarification. –  JohnFx Aug 18 '10 at 14:50
add comment

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.

share|improve this answer
    
"Actual SQL database"? Kind of a nebulous term there. By the description following I think you meant "Client-Server Database Platform" –  JohnFx Aug 16 '10 at 22:37
add comment

In addition to the answer by Force Flow, they also provide different tools and a different UI.

Access has a way to make tables, generate queries using a wizard, make reports using a wizard, make macros, etc. Compacting and other maintenance tools are built-in. It is an all-in-one little database.

SQL Server has more power and less hand-holding. You need to know databases and SQL to use it properly, and it needs proper maintenance.

share|improve this answer
add comment

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.

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.