Beyond Excel – Microsoft Office Access?

One of many Excel’s talents is its data storage; we can store a lot of data in Excel workbook. Excel 2010 64 bit can create a workbook with a size of up to 4 GB. However, Excel is not designed to be a database management system and have many limitations to handle enterprise grade business applications, e.g. each worksheet can only store up to 1,048, 576 row, it does not have robust data integrity and concurrent users support. Therefore, many power Excel users are looking for a better data management software and Microsoft Office Access is often chosen.

Access is a powerful desktop database management system and it is very suitable for desktop applications with a small number of users accessing it simultaneously. Microsoft Office Access is currently bundled with Microsoft Office Professional or you can buy a standalone version for US$ 139. It has 2 main components; a database engine and a rapid application development (RAD) tool that would generate a business application with minimum coding. Microsoft Office Access is definitely a very user friendly, powerful and flexible tool for small and medium business and its license cost is very reasonable.

Despite all these benefits, I have been recommending Microsoft SQL Server to my clients and a lot of people asked me why I prefer SQL Server. One very important reason is that they don’t have budget for an enterprise grade database management system (DBMS); they believe SQL Server is very expensive! Well, what if I tell you that it is free. Microsoft SQL Server has many editions to fit different needs and its Express Edition is FREE, we can download it from Microsoft. Nothing beats free! Yes, it is a trimmed down version but it is still more powerful than Microsoft Office Access in many aspects, e.g. it includes 10GB of storage per database when Access only supports 2 GB; it can utlilize up to 4 cores when Access can only use 1 sinlge core; it provides much better performance and scalability for multi users application. Another key benefit is that SQL Server has multiple editions so we will have a seamless upgrade path when our business grows, e.g. Express Edition supports 1 CPU (up to 4 cores) but Standard Edition can take advantage of 4 CPU (up to 16 cores). We don’t have to worry about if our DBMS can grow with our business.

Another advantage of SQL Server is its recent cloud deployment, i.e. SQL Azure. SQL Azure is a cloud database service provided and maintained by Microsoft. High availability, scalability and fault tolerance are all built-in and it has a 99.9% monthly SLA. We let the experts (who can be more expert than Microsoft when it comes to SQL Server) to manage our database platform. SQL Azure has no upfront cost; we don’t need to buy any new hardware nor software license. We just pay a monthly subscription fee that is based on our usage:

Database Size

Database Size Price Per Database Per Month
0 to 100 MB Flat $4.995
Greater than 100 MB to 1 GB Flat $9.99
Greater than 1 GB to 10 GB $9.99 for first GB, $3.996 for each additional GB
Greater than 10 GB to 50 GB $45.954 for first 10 GB, $1.998 for each additional GB
Greater than 50 GB to 150 GB $125.874 for first 50 GB, $0.999 for each additional GB

Data transfers measured in GB (transmissions to and from the Windows Azure datacenter)

All inbound data transfers, i.e. data going into the Window Azure platform datacenters, are free. Price for outbound data transfers, i.e. data going out of the Windows Azure platform datacenters, is shown below:

  • North America and Europe regions: $0.12
  • Asia Pacific Region: $0.19

With as little as a McDonald lunch per month, we can get an enterprise grade DBMS platform and supports. But if we think we can handle our own data backup, disaster recovery, we can stay with the free Express Edition which supports up to 10 GB of storage. Everything sounds great but let’s ask one fundamental question; is it easy to work with SQL Server within Excel? After all, we still want to use Excel to do our analysis. I have couple blogs about importing data into Excel, “Import Data Into Excel – Data Connection” and “Import Data Into Excel – VBA “. You will find that importing data into Excel is as easily as from Microsoft Office Access.

SQL Server can also help us to:

  • Simplify Development Efforts –friendly GUI, fully integrated with Visual Studio, and Performance tuning
  • Develop Sophisticated Applications – apart from desktop application, it is also ideal to support web applications and smartphone apps which require high scalability and reliability.
  • Deliver Better and Faster Information – empower users to access the information they need through various advanced business analytic tools, e.g. Reporting Services, Analysis Services, Data Mining….

Instead of answering why I prefer to use SQL Server, I always ask why we want to use a desktop database platform when we can have an enterprise DBMS. Why do you like to use Microsoft Office Access?

How about  rapid application development (RAD) tool that Microsoft Office Access provides?  It is also a great development tool and I will discuss if there is other better alternatives in another blog.

About Andrew Chan
Andrew Chan is an Business Consultant who gives you accurate, consistent and timely information so that you can make better and faster decisions. He is an Associate of Society of Actuaries with over 20 years of IT experience. Apart from strong analytical skills and proven technical background, he was also a former system director at Manulife who had extensive project management experience. If you are looking for someone to gather, consolidate, validate, visualize and analyze data, look no further! Andrew can provide the most cost effective business analytics solution so that you can explore, optimize, predict and visualize your business. Don’t guess on any decision, no matter it is finance, operation, marketing or sales! Always ask for evidence!

2 Responses to Beyond Excel – Microsoft Office Access?

  1. Shayeste says:

    I am not completely sure what your suggestion is. Are you saying to use SQL as the database and Access for developing interfaces?

    • Andrew Chan says:

      Shayeste,

      It is a great question. Personally, I would recommend to use Access when you intend to build a simple business application that will be used by a small team and you don’t expect it will have a lot of chance to grow. Otherwise, I will suggest my clients to use enterprise grade software that can grow with their business. Regarding developing business logic and interface, I use the same rule of thumb. Do you want the systems to grow with your business?

      Have you tried SQL Server?

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

%d bloggers like this: