Quantcast

When Should You Use Microsoft SQL Server Over Microsoft Access?

Get the WebProNews Newsletter:
[ Business]

Two of the coolest tools that have ever come out from Microsoft are certainly Microsoft SQL Server and Microsoft Access.

Microsoft Access databases have certainly become more prevalent over the last 2 years as large corporations break the imposed ban on using the tool. Many of our large corporate clients and government clients have stymied development in Microsoft Access for varying reasons which can include security issues or simply the IT Managements belief that they don’t want to support the application.

Some of the reasons why IT Departments do not want Microsoft Access in their environments are quite valid. For example, the use of Access Databases in high security areas such as in the Education Department for storing student information, Department of Defence systems, Hospitals storing patient data are all valid reasons why data should not be stored in a Microsoft Access Database.

However, one of the key advantages that Microsoft Access has is the ability to build a software system to manage a range of services very quickly. The downside is that Microsoft Access on its own is very un-secure and can easily be lifted without any trace using a USB Stick or CDROM. But there are ways to secure Microsoft Access and to prevent data from being lifted.

First of all let us look at Microsoft SQL Server…

Microsoft SQL Server is the tool of choice for many corporate environments because it is a commercial database server. Its core role in the commercial world is to store data which is slightly different to Microsoft Access. Microsoft Access whilst it is a database, it is more so a database management system that allows you to build a fully interactive user interface that allows users to enter data and report on data where as Microsoft SQL Server simply stores the data within tables. It doesn’t have the ability to provide you with a front-end like Microsoft Access.

Microsoft SQL Server allows you to store large volumes of data which include items like photographs, video, text, numbers and much much more. Now whilst I’m sure everyone is saying, “But Microsoft Access can do that too”, you are right to a certain extent but Microsoft Access has very defined limits. Microsoft SQL Server is designed to handle terra bytes worth of data where as Microsoft Access can only hand around 1 Gigabyte of data without having issues.

Can You Use Microsoft SQL Server and Microsoft Access together …

The answer to this question is a resounding YES. In fact my preferred way of developing systems is to utilise Microsoft SQL Server as the datastore and then use Microsoft Access as the Front End. To join the two together I simply link the SQL Server tables to Microsoft Access via an ODBC connection.

This method is what I consider the best way to build a database system which requires a medium to high level of security and integrity. There are a number of reasons for this -

1. Microsoft SQL Server integrates security into the Microsoft Windows Active Directory Security System

2. Microsoft SQL Server can be setup to automatically back up

3. Microsoft SQL Server can do incremental backups which means it can backup during the day rather than just once at night

4. Using Microsoft SQL Server with the Active Directory environment means that your users only need to have one username and only need to logon once

5. Microsoft SQL Server databases cannot easily be duplicated or copied without the SQL Server DBA (DBA stands for Database Administrator) knowing

6. Microsoft SQL Server can handle Terrabytes worth of data where as Microsoft Access is questionable over 1 Gigabyte.

7. Microsoft SQL Server allows you to do some of the system processes on the server via Stored Procedures and DTS (Data Transformation Services) where as Microsoft Access requires the client to do all the processing

I am quite sure most small business owners will look at this article and say, “Well I can’t afford such a system”. Well let me tell you, you can. Microsoft many years ago introduced a software package called Microsoft Small Business Server. This package includes professional tools such as Microsoft SQL Server and Microsoft Exchange and they competitively priced this package for around $1500 Australian. The key reason they did this was so that small businesses would have the ability to access professional resources at an effective and cost efficient price rather than being disadvantaged.

You can buy servers in Australia with Microsoft Small business Server for around $3000 to $4000 dollars which is far more competitive than what it was a few years ago. It also means that by having Microsoft SQL Server available, the systems they can develop can be as professional as those organisations whom have multi-million dollar budgets.

The time to use Microsoft Access on its own is really dependant on whether you need your data to be mobile. If you do and the security of the data is not important, then using Microsoft Access as the data store is appropriate. For example, the other day a company who puts together mining information on key contacts, wanted to distribute their information in database format to people who want to know who’s who in the industry. In this case they developed their information in an Access database and distributed it to those who were prepared to buy it in this format. Security in their case wasn’t an issue because customers were paying for it, so it was appropriate to develop the system in Microsoft Access rather than any other format that might utilise Microsoft SQL Server.

If for instance, the data you are storing is in fact sensitive or is mission critical but the data needs to be mobile. For example you might need the data for a Financial Planner or Loan Mortgage Broker then in this case your data should be stored in MSDE. MSDE is in fact a cut down version of Microsoft SQL Server and by design it is far more secure than Microsoft Access plus it is much harder to copy the data.

The bottom line is this, if your data is important to your business then your principle data store should not be Microsoft Access, you should be developing a system where your data is stored in Microsoft SQL Server and then using Microsoft Access as the Front-End to manipulate the data. Further to this, you should be integrating your SQL Server logins with your Active Directory Security System. If you use this technique your data will be far more secure than if it is stored in Microsoft Access Exclusively.

Add to Del.icio.us | DiggThis | Yahoo! My Web | Furl

Chris Le Roy is a veteran of the software development having worked in the field for over 20 years and has been developing systems using Visual Basic 6, VB.Net, Microsoft Access and SQL Server, ASP, VB Script and Java Script. He currently runs a specialist Computer Training Centre in Townsville Australia, where people from all over the world, train to learn advanced techniques in programming in these applications. Many of his clients specifically focus on Microsoft Access development training. Chris has written a number of training programs, including Microsoft Access and they are available from his website at http://www.1-on-1.biz

When Should You Use Microsoft SQL Server Over Microsoft Access?
About Chris Le Roy
Chris Le Roy is a veteran of the software development having worked in the field for over 20 years and has been developing systems using Visual Basic 6, VB.Net, Microsoft Access and SQL Server, ASP, VB Script and Java Script. He currently runs a specialist Computer Training Centre in Townsville Australia, where people from all over the world, train to learn advanced techniques in programming in these applications. Many of his clients specifically focus on Microsoft Access development training. Chris has written a number of training programs, including Microsoft Access and they are available from his website at http://www.1-on-1.biz WebProNews Writer
Top Rated White Papers and Resources
  • n

    Greate article!

  • Guest

    What an information article.  Broken down nicely, it makes perfect sense.

  • Aby

    Quite Impressive article. Cleared all the doubts in mind regarding which one to use : MS Access or Sql server.

    Both databases are good in themselves, we should choose them as per the requirement of the application.

  • karthik

    my project is to create database for employees in .NET……

    now i got the database existing in MS ACCESS format, so how can i connect my Sql Server to access that database…….

    can u help me solving it………thanking you…….

  • J

    Very informative! thanks a lot!

  • Rob

    It is true that MS Access databases lack real security, however, corporate IT departments that I am experienced with, are paranoid about Access yet they ignore the use of excel spreadsheets which are used to process significant and often commercially sensitive information. Spreadsheets also suffer from hidden errors and poor data control, but they IT let the users do what they like with them.

    We had a budgeting system that ran for 12 years using Access V2 and then Access 2003. It was developed in house by engineering, because as per usual the accounting system was useless for doing maintenance budgets. The system cost about $50,000 in internal engineering time and cost virtually nothing to maintain. It was backed up regularly and never failed. IT finally had there way and ported it across to a proprietary system at a cost of $250,000 and far reduced functionality. They still can’t get it to print properly!

    MS Access may not be the answer for business critical, applications needing high levels of security, however it’s cost effective in many situations and is far better than excel which is constantly misused as a database and has no better security.

  • Brendan Bartley

    Can you tell me what tool is used to connect ms access to sql server

  • Riz

    I came to this page while searching about the uses of SQL. And I really found this article very useful, as it clearly gives a broad comparison of MS Access and SQL. It exactly answered my questions.

    Thanks to Chris Le Roy
    Riz

  • aru
  • Syed Zulfiqar

    kindly if the writer provide the information and the wat to connect the Access as front end to sql server as back end then i will be help full for many users
    Regard