Tips for Deciding Which Database to Use with a Small Application Using MS Access

I built a number of small database applications using MS Access because the users wanted to be able to have access to the data and application to do advanced reporting, data manipulation or revise the system if needed. Most of the time, the MS Access Database served the needs of the application, but in some cases, the MS Access Database couldn't meet the long term needs of the users.

Use MS Access to Prototype Applications and to Manage Software Upgrades

Regardless of the final database used, MS Access is one of the easiest databases to learn, program, and develop database prototypes. The three main problems that small applications built using MS Access occur if there will be shared data, if record locking conflicts occur, and if response time decays. These three problems occur if there are more than one simultaneous user or if the data storage requirements increase over time or if multiple applications use the same data.

Even if I knew beforehand that one or more of the tables might be implemented in an external database later, I first built the system using the MS Access database for the purpose of testing and training.

If the MS Access Database Response Time is Slow

Users will tolerate delays in using an application as long as it doesn't get worse. But in systems where the data is saved for a long time, as the number of records increases, the response time may slow, especially if the application requires complicated record joins. If possible, handling the increase data by archiving older data, or deleting unneeded data can improve results as can correct set up of additional keys. However, if there are significant delays to viewing the data (to the user and an external database is available), choosing to move the data to an external database with more power can help.

If Record Locking Occurs Regularly with MS Access Database

Once a system has more than one simultaneous user, these users may eventually try to access one or more records and find the record locked. The database will allow the first user to modify the record, but the second access will likely receive an error message. If this happens frequently, users will find the response time slows and they might find the error message frustrating or confusing. Sometimes when there is more than one simultaneous user, an external database can improve the usability of the system.

If A Data Table is Shared by Other Applications

If data used by your application is created, updated, deleted or modified by other applications, users will end up having a problem with managing the data and keeping it current. Data tables of this type i.e. for records like employee, business, or other commonly updated and shared data should be put in a better performance database, with strict controls for the type of record rights each application and user has to the data. The underlying definition of this sort of table should not be modifiable by users. Application developers should insure that there is a single point of creation, modification, or deletion by any application. Calls to a single shared external database table by all of the applications may improve results.

Work Needed to Implement Using External Databases for a MS Access Application

Calls to external databases from MS Access were often via nonstandard SQL that were subject to change whenever MS Access changed to a new version. Similarly, if the external database changed, the MS Application might have to change. MS Access Developers should pay attention to changes that occur with MS upgrades.

Usually, the work needed to be done to set up use of an external database from a MS Access application involved the following tasks: table set up in the external database, port of the data to the new table, set up of authorization rights for users and developers, authentication of the data, and modification, testing and release of a new application with the embedded external database calls. For this reason, using an external database should provide the users additional needed benefits before implementation is pursued.