This is from my article originally published on Enterprise Features: http://enterprisefeatures.com/2011/05/access-is-underrated-your-hatred-of-microsoft-access-is-largely-unjustified/

At one time, Microsoft Access was a very popular database platform and still dominates the desktop database market. It’s not as popular as Excel, but as part of the Microsoft Office family, it’s still in many organizations.

It remains in use by my many people but is not as popular for a variety of reasons. However, it can still be very helpful.

Microsoft Access Strengths

Non-developers have the ability to create database solutions without resorting to professional developers. This offers

• Empowerment: Ability to create what you really want without going through someone else (people don’t use other people to write documents or create spreadsheets any more). People are creating huge unwieldy Excel spreadsheets that really should be databases. Access offers the next level up that end-users and power-users can leverage (learning .NET and SQL Server is too far a chasm).

• Connects to many data sources: Ability to get to data in other platforms like SQL Server, SharePoint, ODBC and other Access databases

• Multiuser support: automatically lets multiple people edit the same data without collisions

• Scalability: Access databases can contain up to 2 GB of data which is much more than people can type. Can also connect to SQL Server databases for unlimited sizes.

• Potential for Professional Solutions: Someone skilled with Access can take an existing solution and transform it to a very professional one

• Low cost: No need to get extra budget to do this.

Many organizations don’t like Access applications because people create many of them and some get dumped on IT departments who react with “Who created this crap?”, “We could have done this better on a more ‘professional platform’ if someone come to us earlier”, and “That’s it, no more Access databases!”

What we’ve found over the years is that this approach is wrong reflects a lack of understanding of the overall database strategy of an organization:

• Most Access applications (over 90%) are created and die in Access without ever needing help from centralized IT. That means the IT department never had to be involved in all these small projects.

• An IT department will require $50K-$100K to even consider an application development project. That’s fine but has some implications:

• Should people not create solutions worth less than that?

• If so, does that mean those business opportunities are given to competitors

• Sometimes a small opportunity turns into a big one because one tried. That $25K profit opportunity may turn into millions. Baseball analogy: Without a cheap way to profitably get an “at bat”, one would never know. Making lots of small singles is a legitimate strategy because it generates a run and every now and then, it can turn into a home run.

• We’ve seen organizations ban Access, then people went out and bought FileMaker. Not because FileMaker was better but because the problem didn’t go away (IT didn’t help create any of the solutions they needed, they just took away a tool).

• Bad applications are created on all platforms whether it’s Excel, Access, SQL Server, .NET, Java, Oracle, SAP, etc. Bad applications are hardly tied to technology.

Enterprise Solutions and Approaches for Leveraging Microsoft Access

Database/application evolution exists. It is subject to the random forces of Natural Selection. Solutions live and die based partly on their qualities but often based on externalities like the changes in the economy, government regulations, new customers, new products/services, competition, etc. To predict which 5% of this year’s new databases will need IT support 3 years from now is very unpredictable

Organizations should recognize end users and line of business managers can create a lot of solutions on their own and are best equipped to do so (like giving bullets to infantry).

These solutions should be considered tactical (special forces) not strategic (nuclear weapons) and should not be held to the same standards. Get things done quickly and moving on is the key to being nimble.

Given the inability of most IT shops to handle the workload already on their plate, organizations should be looking at ways to leverage the knowledge of information works by supporting Access in tied levels:

• Bronze: Make it easy to install and deploy Access databases while ensuring the system administrative functions (backups, etc.) are properly managed which is what IT departments are great at.

• Silver: Establish a training program and help desk to assist end users get their Access work done

• Gold: Offer programming assistance to enhance an existing Access application and give it back to the author

• Platinum: Take over an existing solution and offer full services to enhance or migrate it to another platform.

Considered properly, the Access databases created by workgroups should be considered a revenue model for the centralized IT staff. What they need is an understanding of the evolutionary forces in power, and adapt rather than resist change:

• People will constantly need new databases that should not require IT involvement.

• People will continue to create crappy solutions that need professional assistance.

Getting it into the budget and planning is what’s critical. Anticipate a small percentage of Access database will need professional help each year and provide it. The military provides “cover” when requested. IT departments should do the same thing. What one never does is blame the infantry for getting into the mess. They were just doing their job and following orders.

Luke Chung is president of FMS Inc. He’s written a paper on this topic, which you might want to check out: DATABASE EVOLUTION: MICROSOFT ACCESS WITHIN AN ORGANIZATION’S DATABASE STRATEGY. FMS also offers commercial products for Microsoft Access professionals and system administrators.

--LukeChung 16:17, 5 June 2011 (EDT)