Buck Woody Includes Microsoft Access as an SQL Server Alternative in His “Choosing the Back End” Guide

Buck Woody (@buckwoody) hosts the SQL Server Reference Guide blog on the InformIT Web site; see his bio at the end of this post. His Choosing the Back End post of 2/18/2011 begins:

You might think that as the SQL Server Guide host I would always recommend that SQL Server be used in every development situation. But there’s an old saying: "When all you have is a hammer, everything looks like a nail." Perhaps you’ve run into consultants who find that whatever problem you have, the solution is the same (theirs). That usually comes from being familiar with only a few products.

I’ve found, however, that each situation is unique. After close evaluation, solutions are normally dictated by several factors, some of them "weighing" more than others. I’ll cover a few of those factors in this article, to help you understand when to pick SQL Server as the back-end architecture. In fact, you may not need a Relational Database Management System (RDBMS) at all — you might be able to relax the requirements you have enough to use all kinds of storage for your applications. An RDBMS isn’t suited for every situation.

Buck continues with with comparisons of:

• Oracle
• Sybase
• IBM DB2
• Open Source RDBMS platforms: MySQL, PostgreSQL
• Microsoft Access
• OpenOffice Base

Here’s his commentary about Microsoft Access:

It might be a little strange to some to find Microsoft Access as a competitor to SQL Server, but in fact sometimes it is. Microsoft Access is a complete database, development and reporting environment which is highly integrated into the Microsoft Office suite of programs. It uses the "Jet" engine for storage technology, but it can also use SQL Server, Excel or even XML files as a back-end as well. In fact, it can use all of these at once.

The main strengths of Microsoft Access are its availability, its low cost and its ease of use. It seems almost everyone has access to Access, and it’s quite easy to understand, even for writing code. It has several built-in wizards, a graphical reporting tool and the ability to export included in the base product. There are hundreds of applications already written using Microsoft Access.

New developers are sometimes confused by Access — is it a database file or a program? The answer is both. An MDB file is an access database that you can use in VB or C# code, and it also has all the plumbing for the Microsoft Access program to store reports, programs and more right in the same file. That feature sets it apart from the other engines I’ll discuss.

Access’s primary drawback is its scalability. Granted, Microsoft doesn’t tout Access as an enterprise system, but some applications try to force it into that mold. The point is that Access just isn’t suited for more than a few concurrent users.

Another drawback is that Access doesn’t have a true transaction log, and it doesn’t allow you to create multiple files to house the data. For that reason, it isn’t as safe as an enterprise model requires.

Access doesn’t have great security, either. You can create users and restrict them to certain parts of the program, but it’s easy to circumvent.

Don’t write this product off, however. A modified version of the Jet engine was used in Windows NT’s naming database (WINS) and another version of it (Jet Black) was the early database engine for Microsoft Exchange.

You can think of Microsoft Access as inexpensive and feature-rich but having very low-scalability. It runs on all Microsoft platforms.

Buck’s a bit behind the times in his analysis of Microsoft Access, which applies primarily to Access 2003 and earlier. Here are my updates for Access 2007 and later:

• Access 2007 and 2010 use *.accdb (ACE) not *.mdb (JET) files.
• Access 2007 and 2010 also support SharePoint lists as a back-end data source. Access 2010 Web Databases require SharePoint Access Services as the back end.
• Access 2007 and later don’t support creating user accounts or securing data tables and programming objects. All Access databases can be password-protected; share and file security can limit user access to networked back ends.
• Blue Jet, not “Jet Black” was the foundation for Microsoft Exchange’s Extensible Storage Engine (ESE). See my Red vs. Blue JET Database API Confusion post of 7/23/2006.
• Access databases have limited scalability for updates; most Access developers consider about 10 users updating tables simultaneously to be the upper limit. However, up to 255 read-only users can be accommodated. See MSDN’s Microsoft Data Engine (MSDE) for Microsoft Visual Studio 6.0: An Alternative to Jet for Building Desktop and Shared Solutions for more details.

Here’s Buck’s OpenOffice Base review:

In my experience, the OpenOffice Product has been pretty good. That being said, the “Base” product within it, which is often touted as an Access replacement, doesn’t quite do the same thing, especially in the reporting and coding capabilities. You also can’t download just the “Base” product, at least in my experience (I could be wrong here). I’ve had to download the whole package, customize the install, and then install only the parts I wanted.

But if you are looking for an Open Source alternative (once again, this one is now owned by Oracle), there are forks available.

I don’t consider my limited experience with OpenOffice Base to be “pretty good.” See my posts about Open OpenOffice Base 2.0:

• OpenOffice Base 2.0 vs. Microsoft Access 2003 - Part I (10/15/2005)
• OpenOffice Base 2.0 vs. Microsoft Access 2003 - Part II HSQLDB Tables (10/23/2005)
• OpenOffice 2.0 Base Matches Microsoft Access? Surely You Jest.(10/30/2005)

According to Wikipedia’s OpenOffice.org Base article, Sun Microsystems released OpenOffice Base 3.3 on 1/25/2011. Oracle became the owner with its recent Sun acquisition. Wikibooks offers a very abbreviated OpenOffice User’s Manual: OpenOffice.org Base, which a reader defined “as [an] undeveloped draft or outline.”

Buck’s bio:

Buck Woody (blog | twitter) is a Senior Technical Specialist for Microsoft, working with enterprise-level clients to develop computing platform architecture solutions within their organizations. With over twenty-five years professional and practical experience in computer technology, he is also a popular speaker at TechEd, PASS and many other conferences; the author of over 500 articles and five books on databases; and teaches a Database Design course at the University of Washington.