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.

Chris Le Roy is the Managing Director of One-on-One Personal Computer Training and has over 20 years experience in developing systems in Microsoft Access and Microsoft SQL Server. Chris is a professional consultant and developer and consults for a range of large corporations throughout Australia as well as small business. Chris has also developed his own range of training kits to help individuals become Microsoft Office Specialists and to master Microsoft Access. To find out more about his organisation or to check out his training kits visit his website at http://www.1-on-1.biz or his online store at http://microsoftofficespecialist.1-on-1.biz

Microsoft Dynamics GP or former Great Plains has large selection of development and programming tools. As technical consultant you should decide on the customization scope, life time expectancy, future customization migration to Project Green/Microsoft Dynamics (without GP AX NAV SL), etc.

Microsoft Business Solutions or current name is Microsoft Dynamics subdivision of Microsoft is on the way of very rapid and substantial changes. In earlier XXI century Microsoft purchased Great Plains Software (Great Plains and Solomon), then a bit later Navision Software (Navision & Axapta). Then it produced very nice idea on standardizing the ERP modules: Financials, Manufacturing, Supply Chain/Distribution, Human Resources with the following seamless blocks interaction and integration. Imagine – you have Financials module from, say Great Plains, then you integrate it with Supply Chain modules suite from Axapta. Plus users (and consultants) will be prepared to future Dynamics through unification of interfaces. This seems very logical and nice for consultant, however if you develop customizations – you may raise a lot of questions. Let’s look at the reasonable questions and possible answers:

• Native Programming Languages: Microsoft Dexterity in the case of Dynamics GP or MorphX/X++ in the case of Axapta or C/SIDE in the case of Navision. If in your customization you would like to have full spectrum of Microsoft Dynamics GP objects and tools, you have to use something very close to Microsoft Dynamics GP original source code. Microsoft reopened Microsoft Great Plains source code program for MBS partners. Source code program allows you to see dexterity scnscript code and so deploy it in your customization via analysis and imitation. However the fate of native languages is not very clear. As you may say – Microsoft will have to move all the code to .Net (or its successor), something like C# or VB.Net coding

• eConnect and XML Web Services. As more and more objects are exposed through eConnect and XML Webservices (abstraction of eConnect classes and methods) – these seems to be natural tools. However we would be very conservative in the sense of Microsoft Dynamics module future. Imagine – you are deploying custom logic and in a few years Microsoft Dynamics replaces your database structure or the whole module, just leaving the interface similar. You customization might be coming deeper than interface level. Then what would be the implication to recode or upgrade it? Obviously if you program on the SQL level then – database restructuring by MBS itself will make your customization non-functional, but even if you work on XML web service level – it is not quite 100% guaranteed that XML Web service interface will not change or if the set of XML web services will be replaced with new set

• Dexterity – it will probably be in place till 2011 or something about 5 years. And all these years we, as software developers will have to keep our eye on the Microsoft plans. .Net tools are subject to .Net evolution and its pace. SQL scripting will probably be always good if you are lucky that MS doesn’t change database structure in the scope of your custom logic

Andrew Karasev is Chief Technology Officer at Alba Spectrum Technologies ( http://www.albaspectrum.com
http://www.enterlogix.com.br
http://www.ronix-systems.de ) - Microsoft Business Solutions Great Plains, Navision, Axapta, MS CRM, Oracle Financials and IBM Lotus Domino Partner, serving corporate customers in the following industries: Aerospace & Defense, Medical & Healthcare, Distribution & Logistics, Hospitality, Banking & Finance, Wholesale & Retail, Chemicals, Oil & Gas, Placement & Recruiting, Advertising & Publishing, Textile, Pharmaceutical, Non-Profit, Beverages, Conglomerates, Apparels, Durables, Manufacturing and having locations in multiple states and internationally.

We are serving USA Nationwide: CA, IL, NY, FL, AZ, CO, TX, WI, WA, MI, MA, MO, LA, NM, MN, Europe: Germany, France, Belgium, Poland, Russia, Middle East (Egypt, Saudi Arabia, OAE, Bahrain), Asia: China, Australia, New Zealand, Oceania, South & Central America: Mexico, Peru, Brazil, Venezuela, Colombia, Ecuador, Chile, Paraguay, Uruguay, Argentina, Dominican Republic, Puerto Rico