MS Access - its Advantages and Disadvantages

Like most reasonably good products, there are two camps of opinion about MS Access. There are many users who passionately dislike the product. Then there are those who thoroughly enjoy it. To properly evaluate the database software, one must separate the emotional elements from the evaluation and put things in proper perspective. Access is a fairly powerful engine that integrates well with other Office products. The interface is nice and intuitive. It does cost a bit of money however, on the order of $200 or thereabouts per desktop.

The first advantage that comes to mind is how familiar the interface is to most users. The Office 2007 interface style is familiar, so users who have other Office products will not be lost when dealing with Access.

click to enlarge

Access has become an industry standard in desktop use and the database engine is quite powerful. Integration with voice recognition features makes data entry and menu navigation quite easy. There are a large number of templates, including the ones you can download online, which makes creating new databases quite easy. The ability to customize them not only lets you get productive quickly, but you can also adjust things to fit your specific needs. Connectivity options are a strong asset; Access databases can connect to Excel tables, ODBC connectors, SQL Servers, and SharePoint Services sites for live data. Tables created in these sources can be linked and used for generating reports. These reports then give y

ou a better view/analysis of your data. Data harvesting from a large number of e-mail addresses is possible too.

Multiple reports can be created for the same set of data. You are able to use the grouping, sorting and filtering options to get more perspectives on the same data. Real time previews let you create the different versions of the report that you may need for various audiences.

click to enlarge

Access has been designed for desktop use, more like a personal database. It can support multiple users over a workgroup alright; the total number of users (usually around 50 or so simultaneous) is small however. That means Access is more useful for individual departments or the SMB (small and medium business sectors). Access also has difficulty dealing with databases larger than 2GB in size, though just to be safe one should limit usage to about 1GB.

As you scale up the size, performance becomes sluggish (almost to the point of unresponsiveness). Use of multimedia data, even your digital camera photos can eat up space very quickly. Until the 2007 version came along, even the way the images and other attachments used to be stored in Access databases caused a bloat. Though the attachment field in Access 2007 takes care of that, the overall 2 GB space could limit you very quickly. Many indicate that the SQL Server is a more real database, since it competes with enterprise-level databases like Oracle.

Another difficulty pointed out by many is how publishing anything other than static files is a problem with Access. It takes quite a bit of work to make data access interactive. You could use Sharepoint but that represents a significantly large investment. Many believe that the SQL in MS Access is not as robust as other databases. There's a very prevalent belief that Access is oriented towards developers rather than end users. This opinion has changed slightly with the introduction of Access 2007, but it's still there to a great extent.