Sunday, October 28, 2007

Advantages and Disadvantages of Using Microsoft Access

I've answered this question in some form or another far more times than I care to count.  Most often it's a question of "why do I need a fancy Web application when I can just build this myself in two days in Access.  I mean, the data's already in Excel."  So I figured I'd post out what I threw together, I know I've missed some points.

Overview

Microsoft Access is an ideal solution for relatively small datasets and a limited number of users. From the Microsoft Web site:

“As a desktop database, Access is well suited for small, departmental applications. These applications may start as one user’s project. For example, an employee realizes that productivity can be increased if a paper-based process is automated with an Access application. Other users in the department recognize that they can take advantage of the application if additional features are added. As more features are added, more employees run the application. As time goes by, more and more Access applications are deployed for different business units.

With increased use, the limitations of a desktop database become apparent. Access security, performance, and disaster recovery features are not robust enough to manage an enterprise-level application. Because you need a new solution, you decide that the applications should be migrated to SQL Server.” (Source http://www.microsoft.com/technet/prodtechnol/sql/2000/deploy/bldsysarch.mspx)

Additional Sources:

Multi-User Support

While Microsoft specifications allow for up to 255 simultaneous users, multiple sources have found that no more than 12 users can successfully use a networked Access application before the delay in connections through the file system pose a problem. Also note that users must be attached to the local LAN and have access to the shared networked drive hosting the Access application. This is not a problem for small teams in one location, but once teams are spread across multiple locations the application access slows down significantly for all users.

The maximum concurrent user count listed by Microsoft assumes that the data is stored in one Access Database while access to the data comes from a separate Microsoft Access interface, such as an Access Application Project. In this instance the Access application resides on a user’s machine while the Access database data resides on a shared network drive to which all users need Read/Write access.

While security is often not a consideration when deploying an Access database application, note that complete security can only be attained through folder security permissions. While users and roles can be defined in an Access database, it has been found relatively simply to circumvent these permissions and gain full access to the system. For secure access network administrators will need to grand and deny access to shared folders to the specific users who can access the database.

More inherent in Microsoft access is the inability to easily use profile information about users. It is not trivial to store profile specific information about a user. For example, if a user is in one project, for them to see only their data in their queries, instead of data from all projects, data must be pulled when a user logs into the system from a table and stored globally. Then all queries must code in access to this property. Web application tools such as ASP.NET provide profile information built-in, and adding these filters to stored procedures is fairly trivial.

Most government organizations create a standard version of Microsoft Office across the organization. All users have the same version and service pack of Office, allowing for simple deployment of the application. An Access database or Access Database Project can connect to a remote Microsoft Access database and retrieve information.

Whenever a modification is made to the underlying database or user interface a new version of the Access Database must be loaded on the desktops of all users of the system.

Scalability

For relatively small data sets Access works very well for providing flexible information retrieval. It provides simple functionality to create pivot tables and generate graphical reports. Connections to the database are made using Jet or ODBC database connections. These connections work in small levels, but have more overhead when sending and receiving information.

While up to 255 simultaneous connections are supported, this works in an environment where all users are on the same LAN (local area network). Once users become distributed across multiple locations, connections to the database slow down dramatically. This is primarily due to the fact that all connections to the database must be made through a mapped drive connection. Connecting to a remote mapped drive is much slower than native SQL Server or other database connections.

Microsoft Access also has a file size limitation of 2 GB of data. While there is very little possibility of exceeding the file size in many cases, the amount of time needed to retrieve data for a query or report becomes exponentially long as the number of rows in a table increases. When a table exceeds tens of thousands of rows processing time for a query becomes minutes instead of seconds.

Very often to retrieve information formatted in a way that is usable multiple nested queries are needed. Generally 3 or 4 nested queries are needed to retrieve the information in a usable format. In some cases one query is run to generate a table, after which another query is run to generate another table. This is done so that the final query can run quickly, but requires multiple steps by a user to generate the data in a usable format that can be retrieved in minutes instead of hours. This also adds a very large amount of work to make changes to a query or add additional data, as all underlying queries must be modified.  Confusion can easily occur as unused tables exist in the database, often where only the database creator will know which tables to use at which time.

Reporting

As long as a strong understanding of SQL queries is understood and deep understanding of the underlying tables, reports can be created to extract and display information, including the ability to export the data to Excel or many other formats easily.

These reports use the underlying queries to generate and format their data. They work very well, though not always quickly for large calculations or formatting. Generally a more robust reporting tool such as SQL Server Reporting Services, Crystal Reports or another application will retrieve this information faster and provide the same graphical and export options. The disadvantage to these solutions is that quickly creating a new report is not as easy as it is in Microsoft Access.

Data Import

Importing data into Microsoft Access is relatively simple for simple amounts of data. Performing large data imports and formatting of final data is more difficult in Microsoft Access than it is in more robust database tools such as SQL Server and Oracle. This is generally due to the modified version of SQL available in Microsoft Access. Fewer functions exist for formatting, extracting and updating data into disparate tables. Often to extract a specific set of data, such as a list of locations, the database administrator must create a query for that one action and run it to populate a location table. More robust tools allow for multiple queries to run at one time, and provide transactional processes, so that if any part of the import fails all changes are rolled back to a state before the import began.

Remote Use

For a user to use the system from a machine away from their office, they will need to have the same version of Microsoft Access installed. While this is not often a problem in government organizations, as mentioned above, it does require that users have access to the shared folder wherever they are located. Web based applications allow a user to access the system from any location that has Internet access.

Web applications also allow for seamless updates to the system. If a new field is added to a form or report, the code is placed only on the Web server, and the new form appears to the user the next time they access or refresh a page. In an Access application, the new code must be sent to all users of the system. Without additional coding in the Access application to ensure the latest version is always used, it is possible that a user could use outdated code to update the application. This can allow for invalid data to be entered or invalid reports to be generated.

User Accessibility

While Microsoft Office programs now meet (as of Office 2003) the governments’ standards for Section 508 accessibility, there is no guaranty that applications created in Microsoft Access meet those standards. If forms are used in the access application, more care must be taken in providing keyboard access and linking labels to fields for screen reader access.

While additional coding is also necessary for Web applications, many mature Web application languages, such as ASP.NET 1.1 and 2.0, provide simple methods to make applications accessible. Most user controls generate Section 508 compliant code by default, while others provide properties to make the control compliant.

9 comments:

Anonymous said...

Tom! I was searching on the internet for options to upgrade an existing access db. It's encouraging to know you walk the walk with God. Keep up the blogging! I appreciate your openness towards your faith. Rock on! I've been as well. You can find out more at my blog - http://jesuskrak.livejournal.com/ -

Anonymous said...

Excellent objective write up.

Anonymous said...

Thank you very much that really helped a lot :)

rana said...

thanks for this info

Anonymous said...

Very helpful

Alok said...

Excellent. Very succint. Very balanced.

Ronnie said...

Thank you for this helpful information....

Anonymous said...

I have read the site carefully. I found here lots of comment about MSDS and link. Now I am sharing a site where you find all necessary data and information about
msds book guidelines
Thanks.

Online Msds said...
This comment has been removed by the author.