blue bar
Logic Architect - bringing the power of database to Excel

Database options

For most Excel applications, Tables are quite adequate for storing the data. But Tables in Excel are not a database and there are limitations which can make them unsuitable for some types of development. Some of the most significant are:

A solution

The obvious solution to this is to use a true database product. For example, Microsoft Access is widely used either for a single-user system or a multi-user system where the users are networked. MySql is a common choice for a web-based database. However none of these choices provide the ease of use and reporting flexibility of Excel.

Our approach to this is to use Excel as a front end, and a database product as the back end just for the storage of the data. Most databases provide the necessary connectivity software, so that it is possible to use a common approach (ADODB) with different types of database. We have developed a database maintenance tool in Excel which can be used to maintain the data through forms or tables in Excel - which look very similar to what would be used if the data was stored locally. We can then use SQL queries for the analysis of the data, such as the creation of pivot tables.

A further advantage of this approach is that a solution can initially be developed based on Excel tables, then quite readily migrated to a back-end database later. It is only necessary to take care to ensure good initial design for this process to be quite straightforward.