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:

• Excel is not a multi-user system. It provides some facilities for workbook sharing, but these are quite restrictive and many features of Excel are not available, including Tables. Excel Online can be shared by remote users, but again there are many restrictions.
• There is no data typing. Any data can be placed into any cell in a table. This causes problems when the table is used in SQL, as the software which processes the data can only handle a single data type in any field. It attempts to determine the data type by looking at the first eight rows of data, which means that it will fail to pick up non-text data types when these rows are blank in any column.
• There is no referential integrity - that is, if a field in a table contains a code which is the key of another table, there is nothing to ensure that this second table actually contains a record with that key. Validation can be used to check this initially, but there is nothing to stop the record on the first table from being changed or deleted.
• Table structures can be easily damaged by the user; validation will be removed if a user pastes a cell from elsewhere using a normal paste rather than paste values, and formulae can be overwritten by values. Protecting the worksheet can help, but tables cannot be extended on a protected sheet.
• Large tables make for large workbooks which are slow to load and save and can be slow to recalculate as there is no indexing.

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.