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 surface trade in 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.