Excel or Access?

I have a business application I want written, should I use Excel or Access ?

Good question – both Excel and Access are popular tools that businesses use and there are definitely situations where you should select one over the other.

In Excel the data and the ‘application’ is unstructured and open, anyone can add a new column, move things around, change calculations unless you spend some time ‘locking down’ your spreadsheet. It is great for ‘what-ifs’, doing data analysis but poor for real ‘applications’ where you need structure, conformity, validation and data quality. Many businesses have built processes around the completion and consolidation of Excel data – this needs care to work well and the potential for an administrative nightmare sorting out and/or consolidating the information is substantial. Only one person can work on an Excel spreadsheet at a time so sharing information can be a problem usually solved by again having multiple copies of the spreadsheet and consolidating.

Access is structured. Like any database it consists of tables of information with fixed columns. It has built in data entry forms, reports which are built by a developer and can easily be protected, if only because few users have the skills needed to access and modify them. It supports simple to highly complex programs to manipulate your information up to complete applications. Where the volume of data or number of users grows you can upsize the data storage and use MS SQL Server. Access supports multiple simultaneous users and manages conflicts where two people attempt to modify the same item of data at the same time. Access is ideal for small to medium scale custom business applications that do not need to be web based. If you build an application in Access you may not even have to buy Access for your users as there is a free ‘runtime’ version which will run Access applications but not let you build or modify the application itself.

To summarise – the choice depends on the following questions – how structured and fixed vs unstructured and fluid is your information, how many people need to access/edit this information, how much information will you be dealing with, does the data need extensive validation or do you need significant reports?