When To Use MS Access

Creating your MS Access database is not as intuitive as say creating a spreadsheet in Excel but it does provide a richer set of tools and functionality which leads to when to use MS Access as your first choice?

How to use Microsoft’s desktop database application once it has been planned, designed and implemented makes managing data and reports a breeze! Will this require some training?

Let’s address when to use MS Access and below are some of the key points to consider the pros and cons when comparing say an Excel spreadsheet with a database system or the larger MS SQL Server database:

Most database applications includingan Excel spreadsheet can be set up to validate data inputing but with such a database; a table or SQL Server tables, you are forced to choose a specific data type from the day one.

How many records do you need to manage? In Excel, earlier versions had a fixed number of 65,536 rows and have been increased to over one million but this is a poor approach when trying to store tens of thousands of records due to the lack of indexing and memory waste. With an Access database it’s better suited for the larger volume data set and even more so for SQL Server as it’s scalable.

Looking up and connecting with other data sets (or recordsets) is a key consideration in when to use the desktop application or SQL Server versus Excel. In Excel, you use the VLookup and other lookup related functions calculating across worksheets and is very sluggish! With Microsoft Access (& SQL Server), you simply join tables to form a real RDBMS (relational database management system) and index the lookup fields.

What about the queries? Excel struggles here with limited filtering and reporting though the finished reports available (i.e. Pivot Tables) are strong. MS Access is much better and SQL Server has what is known as Views which are similar.

Muli-user capability? Not in Excel though you can share a spreadsheet but it’s not the same. Again, the desktop system and SQL Server are positioned for multi-user environments with the latter being very strong with security.

Attaching files and other links are available in MS Access (2007 & 2010) and SQL Server but not ideal for Excel spreadsheets.

MS Access Training, Excel or SQL Server?

If you choose Excel then limited or low level of training is required and can be covered in a day or two. With MSAccess, training is a must and depending on the level of designing a database and when to use MS Access in a live environment will vary but as a rule a minimum two days plus a day for groups of up to 6 delegates is suggested. As far as SQL Server, this is a complex application and requires a far greater time to master.

There are many resources to choose through to learning to code using VBA and various eBooks are a good way to start to gather the essentials. 

Tags: how to use access database, how to use ms access, MS Access, ms access database tutorial, ms access training, when to use ms access