When To Use An Access Database

A Microsoft Excel Spreadsheet and a Microsoft Access database are both amazing productivity tools, but they have very different purposes.

Which database

Database management system applications vary by size and capabilities. Our white paper on the difference between local and enterprise databases explains the difference. For this article, we are referring to a desktop database tool such as Microsoft Access. In fact, we will just refer to an Access Database from here on out.

Where to Put It

Desktop clients for line-of-business applications connect to an enterprise Relational Database Management System (RDBMS) on a remote server. This server might exist in the same building or the other side of the world. Access, on the other hand, stores each database as a separate file the same way a word processor or spreadsheet application does. Saving the file to the workstation is easy enough. A better choice is to keep it on a network drive so it can be backed up, and colleagues can share each other’s handiwork.

Where to Start

The beauty of a spreadsheet application is how intuitive it is to use and how quick it is to get started. Creating an Access database, however, requires some familiarity with database concepts such as tables, data types, primary keys, and relationships to other tables. Fear not. There are plenty of sources available for learning to use Access.

Not everyone in the office needs to be a database aficionado. One or two well-trained professionals can handle the database design and assist everyone else in how to use them. If your business has an IT department, likely someone there can help you out. If it doesn’t, or they lack the training and experience, the Database Design and Software Development experts at Vaillus IT are ready to help.

Here’s a career tip: being the one in your office that can make sense of all that data everyone else is collecting puts you in rock star territory.

Why use a Database?

The rule for when to use a database instead of a spreadsheet is this: Use a database when the data you are entering will not result in a useful outcome during this sitting. If you are routinely collecting data that will be occasionally summarized and analyzed, an Access database is your friend. Databases store data that has historical significance. The are the sensible “Source of Record”, the one place business critical data is kept and can be trusted to be correct. Spreadsheets, on the other hand, are useful for entering “throw-away” input to generate an output you need right now.

Do you find yourself having to download, copy and paste, re-key, and mash up spreadsheets to get some final result in a required format? Then spreadsheets are the wrong tool. Go with an Access database instead.

Any Way you Want It

There are a multitude of ways to output and analyze the information from a database on the fly. Spreadsheets are more static, and changing their format takes significant effort. Using a reporting tool or dedicated query language like SQL (Structured Query Language) provides endless options to sort, filter, summarize, charts and graph your database content.

Customize it

Entering data into a database is not as easy as keying it into the rows and columns of a spreadsheet. Some required software-Development provides an intuitive set of screens for entering in managing the data. Access has a built-in tool for constructing forms too.

Scaling Up To Custom Software Development

Over time, databases can grow very large storing historical data. As people create more Access databases over time, combining their data into a consolidated view of the organization makes sense. Microsoft Access does not lend itself well to these scenarios. The better solution is to move the data into an enterprise Relational Database Management System such as Microsoft SQL Server, Oracle Database or an open source option like MySQL.