When Should You Use Microsoft SQL Server Over Microsoft Access?

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 MS 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. MS 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 ms 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 MS SQL Server as the datastore and then use MS Access as the Front End. To join the two together I simply link the SQL 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. It can do incremental backups which means it can backup during the day rather than just once at night

4. Using the 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. It 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 ms access on its own is really dependent 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 MS Access, you should be developing a system where your data is stored in Microsoft SQL Server and then using MS 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.

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