Saturday, 5 May 2012

MS Access - Not 'Bad'


On my first day at work, my manager offered me a early Christmas gift, packed and wrapped. I opened up the packaging to find 'MS Access 2010 DVD'. "Oh boy !", I made a sarcastic sign.  Well the main reason was I never had found a great interest in Access, have always got in various con's of the software; rarely ever people talk about its pro's. Working with Access in the past few months, I have learnt that it is not bad after all. So many shocking truths that gave me a whole new look at Access. I am planning to mention a very few advantages of using the this software over the others available. I am not trash talking about the great open source Databases that are currently available; they are amazing and provide much better services and functionality, but I am just trying to make you all see what Access has to offer to us all. Access is one of the product where 'good things in small packages'.

# Inferior??
As with the crowd, I also believed that MS Access is nothing but an inferior Database, starting to use it I realized that Access is nothing but a simple User Interface to the real deal. Yes, Access just provides the functionality and service of connectivity to the Database server which is the JET engine. (A shocker right??)  The tables that you create and manipulate actually resides in JET, where Access just shows you a virtual reality of how it looks like. This functionality of creating a GUI helps even any novice user to get along with. Just imagine, if you are sitting in front of the computer and you wish to create a simple table, if you really do not know the exact syntax you will always face the error..  
" ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'dae) at line 1

Honestly, I will say 'Oh dear !' well, using an interface comes in handy here. Every single step is a guided tour that helps you feel more confident. You do not need to know the exact syntax, just a drop down box will list you what t needs to be Text/Date/Number...... Well that solves a great issue ! (NOTE: SQL server also comes with GUI tools to help you out)

# Size & Concurrent users matter !!
Yes, I totally agree that size does matter and the greater the size the better is the Database. JET engine allows you to store up to 2 Gig of information, though it is not a very big; for a small organization or a start up industry that is going to hold 20K records and cannot provide the funding for a 'brilliant' IT team this would do great wonders. Access provide support of accessing information from the table for around 255 concurrent users. If a company has just started with 50 employees I think this is just enough. 

# Front end development ??!!
Access uses VBA (Visual Basic Application) for precisely tune the already existing functions, in other words to 'override' them. Just as in any programming language you will be able to make it do what you want it to do. Although VBA is not a greatly sought after language, it still is good. Just because something is out of trend does not mean it will not do what it needs to do. In fact, when you understand what needs to be done in a simpler language, you will be able to transfer it across to other platforms. 

Access is highly prone to become corrupted. Yes, but this will happen only if the user is tampering with the data (or) if they are not going to use it correctly. However, Access has not always won this case, but in my next post I will post how to avoid corruption and steps to reclaim your corrupted database. 

# More to 'Access'
As we all know, MS packages often come with additional advantages such as Flowcharts, pie-charts, bar-graphs etc. This functionality is really useful to develop precise  reports that do not need a great detail to look into. On few clicks of a button, you will have perfectly described reports and diagrams that project the outcomes. This involves PivotTable, PivotChart. Querying in Access is more efficient than normal queries, you do not have to worry about the missing parentheses, appropriate punctuations. You can also forget the worry about how you are going to place the keywords.. 
" to define parameters? 
...will parameter be written on the first line or last?
Well the above mentioned are some of the great advantage, there are still more that can be achieved by using MS Access. It is not a waste of time after all, take it from a person who has used it and is very satisfied. Please feel free to Share and Enjoy !!


  1. Not bad, not bad. Keep up with the posts last one was a long time ago.

  2. Thank you Victor.. It has definitely been a very long time.. I will draft some posts in the next few days..

  3. Excellent Blog Paul. Keep on and please never give up on helping us.