It's very easy to fall into the trap of using a spreadsheet just because you are familiar with Excel. Here, we give objective advice including a simple Checklist on whether you are right to stick with your Excel spreadsheet or switch to Access for all or part of your application.
Excel spreadsheet or Access database?
|
Spreadsheets are not so good at processing data. Their very informality means they lack security and if more than one user needs to make entries, it is the equivalent of waiting your turn while the workbook is passed around the office.
Admittedly, databases are not as intuitive to design as spreadsheets but once set up, Access is very easy to use and you will soon wonder why you put up with spreadsheet input for so long. One of Microsoft Access's great strengths is its ability to share data and functionality with Excel. You can use Access input data and Excel to analyse it; in fact integration is so good that we often design integrated applications where it is not obvious whether you are in Excel or Access. A final point - you do not need to deploy Access throughout your organisation to benefit from using it. There are several strategies for avoiding this, so if deployment is an issue, ask PBMS for advice.
If you would like more information, see
Database Solutions in Microsoft Access
|
Spreadsheets
are intuitive,
easy to use and a great personal productivity tool for
making notes, doing calculations, and
presenting information.
Microsoft Access is a robust, fully-featured database and a natural choice for
handling data input, particularly in multi-user applications.
The built-in security, form and query design features save hours
of development time and it will pay for itself time and again.
It is also particularly good at creating Web and intranet based
forms.
SQL Server is Microsoft's enterprise database (Access's big brother) and can handle
hundreds of simultaneous users. Most users need not
concern themselves with this; it mainly affects the database
engine behind the scenes not the user interface.
Try this checklist to find if you might be better using Access instead of Excel.
If you can ring any of the 'Y' points in column A, you should consider using Access for your data input; ring any 'Y' points in column B then make sure your Access database is SQL Server compatible. If column C applies, you should be using Access to produce at least some of your reports. Remember that your data can easily be made available in Excel for further analysis.
|
Should you be using Microsoft Access or SQL Server instead of Excel? |
A | B | C |
| 1) Inputs | |||
| Do you enter or edit data in your spreadsheet manually? If Yes :- | |||
|
Is the data input in similar form each day / week / month? If Yes:- |
|||
|
Is the data input a mixture of text, dates & numbers? |
Y | ||
|
Does more than one person input / edit the data? |
Y | ||
|
Do you use lookup tables (Prices, Products, Customers etc) |
Y | ||
| 2) Processing | |||
| Does your data (manual or imported) need to summarised before use? | Y | ||
| Do you need to cut and paste input data before they can be used? | Y | ||
| Do you store your data in multiple worksheets (perhaps one per month)? | Y | ||
| 3) Data volume | |||
| Does your spreadsheet have more than 2,000 data records (rows) in total? | Y | ||
| Do expect more than 100,000 data records (rows) in total? | Y | ||
| Do you expect more than 10 users to input or edit data simultaneously? | Y | ||
| Do you plan to access data in mainframe databases (e.g. Oracle)? | Y | ||
| 4) Output (Reports and Files) | |||
| Do you need VBA or macros to prepare and format your reports? | Y | ||
| Do you want to publish the data on the Web or your company Intranet | Y |
Contact PBMS Consultants
For more information on data integration and custom software development in Excel and Access call Peter Barton direct on +44 (0)1494 815306 or E-mail PeterBarton@PBMSconsultants.co.uk.
PBMS Consultants