Why Use Microsoft Access

The following was taken from a discussion group and I believe is a very good explanation of a few reasons why Microsoft Access remains such a dominant tool, even 30+ years after its introduction to the marketplace.

Access is really an application development environment. It includes not one but *two* database engines: JET, the database engine in .mdb files, and (with the newer versions) MSDE, which is simply SQL/Server.

An Access application can link to any ODBC compatible database server – Access/JET databases, SQL/Server, MySQL, Oracle, DB/2, Visual FoxPro, dBase, take your pick (they all have their own advantages and disadvantages). SQL/Server, MySQL, and Oracle are all very capable database engines – but, by themselves, they don’t have tools for building user interfaces (forms and reports). You can use Visual Basic, C++, etc. to do so, or pay for expensive tools to frontend them- but Access is inexpensive and has a very capable form and report design feature.John W. Vinson MVP

Microsoft Access is an “all-in-one” tool, whereas the other database engines require additional, specialized skills to make the “complete” package. Access provides easy access to the database engine (Jet for Access 2003 and earlier, ACE for Access 2007) for data storage and data integrity. Access also provides fairly easy connectivity to other database engines for data storage, data integrity and security. Access provides automated tools (wizards) to build the presentation layer, or “front end” (data entry forms, reports for data displays, custom coding in modules, et cetera). An expert in Access can be expected to know how to use (and can teach you how to use) all of these tools in Access.

The other database engines may or may not provide similar tools besides the data storage feature. MySQL provides only data storage. It doesn’t have tools to build the front end. Oracle provides many additional reporting tools and presentation tools in addition to data storage, but these are so highly specialized that it usually takes a minimum of two or three Oracle professionals to build an application and manage the data. Microsoft SQL Server now has additional reporting tools and custom coding (T-SQL, CLR, and GUI managers) available, but again, these are so highly specialized that it usually takes a minimum of two or three SQL Server professionals to build an application and manage the data.

Access can handle additional complexity as your skills grow, and then allow you to migrate the back end to an even more complex database engine without having to start over from scratch when your organization’s needs increase and your workforce expands.

Managing the other database engines compared to managing Jet is more complex by an order of magnitude. Oracle is even more complex than the others, so increase the complexity by another order of magnitude. Unless you plan to get formal training, don’t expect to “jump in” and just “wing it” with Oracle. With the other database engines you can “jump in” without formal training, but expect to make so many mistakes that you shouldn’t plan to use real data until you have at least six months to two years of experience, so formal training is also a good idea.Gunny

Depending on the version, bells, and whistles, Oracle is about $40,000 for one license. So if you don’t already have Oracle, there’s a steep cost there.

One person who can do both DBA and Developer duties in Oracle will be very expensive as extensive skills are needed. Add to that the extra time it takes to get an Oracle app up and running.

I’m an Oracle DBA by day and develop Access apps by night. All things being equal, I probably can bring in an Access database for about one-fifth the price of a similar Oracle app in half the time. Of course the Oracle app can easily handle hundreds, not dozens, of users plus Oracle databases don’t have a corruption problem. If one does corrupt, there’s multiple ways to recover the data or not even let the user’s know that there was a problem.Jerry Whittle