When is Access appropriate?Microsoft Access targets individual users or small teams that need to track and manage business information. Users familiar with Microsoft Office tools can easily develop a personal database or a small application in Microsoft Access without requiring any involvement of software developer. Microsoft Access based application works just fine when the information being tracked is ad-hoc and temporary. The ad-hoc query and reporting capability of Access enables users to get much needed information without any involvement of IT. Access is most efficient and effective when:
- The need is to track information for small and non-critical tasks.
- The team using the Access database is collocated.
- There is budget and time constraint.
- The need for database is for short term and does not need to be scalable.
- Security of data is not very critical.
When to upgrade?According to Microsoft most deployment of MS Access are for individual or small team applications used for limited time basis. Problem arises when the Access database grows beyond the need of the small team and starts catering to more users and departments in the organization. Access, being a file-server application, is not scalable. The performance and reliability of the Access database decreases drastically as the number of concurrent users increases. The following reasons are the key drivers for upgrading your existing Access database:
- Web Based: If there is a need to make the data available in a browser over the internet or intranet. In such cases consider migrating your Access database to SQL Server or Oracle database. You can rewrite the application using either:
- .NET: Rewrite the application in .Net and migrate the database to SQL Server, or
- Oracle Application Express (APEX): Migrate the database to Oracle and the application to Oracle APEX. Oracle provides tools to migrate your Access database and application to Oracle APEX.
- Scalability: Access database has limitation for both the database size and number of concurrent users. If the database size exceeds 2 GB and the number of concurrent users exceeds 20, then the application performance is not reliable and you run the risk of data corruption.
- Reliability and Availability: Reliability and availability of your database is critical if your Access database has outgrown its initial objective and has now become mission-critical. Access database, when used by multiple users, is not reliable and is susceptible to data corruption. Also, there is no reliable mechanism for performing database backups as Access requires all active users to terminate their connection before a database backup can be initiated.
- Security: Access does not provide fool-proof security mechanisms to restrict users to their role based access to data. A user can easily get around Access security to have complete access to the database.
- SQL Server: Rewrite your Access Application in .Net and migrate Access database to a more reliable, scalable and secure SQL Server database, or
- Oracle platform: Migrate your Access database and application to an enterprise level Web based application developed on Oracle APEX. Oracle APEX provides migration tools to easily migrate Access based application to Oracle APEX based fully web-based application.