When the first spreadsheets appeared, they were a brilliant innovation ─ suddenly, a relatively non-technical person could perform data processing operations and produce reports which would have previously required the services of a professional Computer Programmer. Furthermore, having the user as ‘Solution Provider’ removed all of those communication problems arising from dealing with tech-heads who didn’t necessarily understand the business problems to be solved.
With a product like Microsoft EXCEL (the current market-leader), simple solutions can be produced very quickly and these can be ‘played with’ in the light of experience. For the advanced user, there is a background programming language (Visual Basic for Applications) which can be used to make the spreadsheet do pretty well anything you like.
The problems arise as the application becomes more complex and you end up with large volumes of inter-related data spread over several worksheets or even several workbooks. In this sort of situation, it is so easy to mess things up with a mismanaged copy/paste, a bungled insert/delete or erroneous data entry into a cell whose contents are based on EXCEL formulae.
Spreadsheets were basically designed to handle columns and rows of data, emulating the previous paper-based processing of, for example, accounts information. Although they can be made to do it, spreadsheets are not inherently designed to manage complex data relationships or to present different ‘views’ of the same data. In my experience, applications based on EXCEL tend to grow in a chaotic manner and inevitably reach a point at which a ‘Database’ approach will provide a more robust solution.
How So? (I hear you ask)
Firstly, a database stores the data separately from the formulae and code used to manage it.
Indeed, Microsoft ACCESS allows you to actually split the database into separate front-end and back-end files ─ the back-end containing inter-related data tables and the front-end containing the forms, reports, queries and code used to display and update the data.
This provides the ability to continue development of the front-end code, etc., without any danger of messing up the live data ─ if a new version causes problems, one can generally revert to a previous version while these are sorted out in a ‘test’ environment.
Splitting the database also supports multi-user working where the data resides on one PC and other users can access it via the local network ─ up to about 15 simultaneous users, according to Microsoft.
Significantly, the user need not be concerned with the physical placement of the data ─ new records just get stuck on the end of the relevant table with ‘queries’ being used to sort and filter the information to produce the desired ‘view’.
It is also a simple matter to add new data fields without disruption of the existing data or functionality ─ another significant advantage over EXCEL.
Although data tables can be updated directly, this is usually done via ‘Forms’ which can precisely control what the user is allowed to do, as well as managing the presentation of the data.
Microsoft ACCESS is quite well-integrated with the other modules of MS-Office so, for example, your ACCESS database can easily generate reports in EXCEL or automatically email them using OUTLOOK.
Naturally, people ask whether it is more expensive to use the ‘database’ approach ─ I generally find that development of a complex system is a bit easier in ACCESS but the real benefit comes when you want to make changes, which are very much easier and considerably safer.
About the Author:
Les King started programming computers at the age of 18, which was a very long time ago. In addition to an extensive background in the development of IT systems, he is a small-business entrepreneur who has successfully managed several enterprises including IT Staff Recruitment and Property Maintenance companies.
Not having the inclination to ‘retire’, he currently works from home, providing IT Support for small businesses and individuals whose needs are not catered for by the major consultancies.
This includes on-site, telephone and remote assistance support for Windows-based PCs. He also specialises in the development of ACCESS database and EXCEL spreadsheet applications.