Advantages and Disadvantages of Microsoft Access

Microsoft Access is a database application that integrates tables, forms, queries and somewhat arcane calculation techniques, none of which is for the novice. Unlike a word processor or basic spreadsheet, the end-user cannot just “jump in” for a fast start. It is probably the least intuitive computer application in the MS Office suite.

Besides the steeper “learning curve,” MS Access has the following disadvantages:

• Access needs more careful planning and setup time. It is really a “database management system” where the last step is data entry. With Excel, data entry begins immediately and most calculations and formatting can be done “on the fly”; not so with Access, where every field and table must be designed with the end product in mind. Although Access databases can evolve and grow, the process must be carefully managed so as not to spoil what has already been designed.

• In Access, mathematical calculations are done through queries and ad hoc table and reports design. Both methods can be cumbersome, and each requires detailed (and at times, arcane) database programming knowledge. Excel calculations, on the other hand, are far more straightforward and transparent. Also, Excel does a much better job at producing charts and graphs than Access.

• Table editing in Access is cumbersome and slower than in Excel. Moreover, "global editing" in Access is best done through special update and delete queries. These queries are not for the faint of heart and comprise somewhat of a “nuclear option.” Once executed, these queries can irretrievably adulterate or delete thousands of data records.
  

MS Access is a powerful database application and has significant advantages over a spreadsheet in tracking data. Some of these advantages include:

• With careful planning and thoughtful setup, Access gives the user a consistent “container” for data that can be reliably retrieved, manipulated and reported. By embedding "rules" that must be followed at the time of entry, a consistent and reliable end product is guaranteed. (For example, one rule might be that a field must always be a number.) Although the learning curve for Access queries, forms and reports design can be steep, it is worth the effort. Forms and reports that calculate totals, tax and discounts, for example, are the “bread and butter” of MS Access business applications.

• Despite the “dangers” of global editing through update and delete queries, which if mistakenly applied could wreck a database, these queries provide a tremendously powerful editing capability. Thousands of records can be revised, replaced, or moved elsewhere through one carefully executed (and reusable) query.

• Access is a relational database. This is where Access leaves Excel “in the dust.” A relational database allows the linking of individual tables by means of common fields. When tables like “Customers” and “Products” are linked, for example, each table can be maintained and updated separately, but linked into a new “joined” table for a history of product purchases, etc.

MS Access is a database management system that:

• is less intuitive at the design stage,

but

- leaves a spreadsheet “in the dust” by joining related tables that can be maintained and updated separately and joined for day-to-day business records, sub-forms and powerful reports.

• is slower in crunching numbers and does so far less transparently than a spreadsheet,

but

- can do useful calculations in a form or a report that you cannot get from a spreadsheet.

• is susceptible to disastrous and irreversible data destruction through instantly-executed update and delete queries,

but

- gives the user a tremendously powerful data editing capability that can be preserved for future use.

Smart Computing in Plain English - “Spreadsheet vs. Database”

Microsoft Office Access 2007 Product Overview

When is it time to move to MS Access?

MS Access: The Power of Action Queries