Sunday, May 30, 2010

Access Northwind used in real life

With an out-date inventory management system, keeping on top of what is flowing in & out of the company became a chore.  Coming from a techie background, my desire to use the latest and greatest took a back-stage to rational mind to use some practical to get the result quickly.  After all, my goal was to have some simple dashboard, which we can easily see what customer orders are coming in and what purchase order we are making.  When necessary, we need to be able to tracing our purchases back to their purpose, which in most cases, it is for a customer order.  It is also important to collect as much information as possible while remain as non-intrusive as possible.  This means customizing the workflow to match the business with the proper prompts and proper validation at the right time.

With all that in mind, the old days playing with Access 97 and their Northwind example came back to mind.  Although the Widget set from Access was fairly restrictive, it was sufficient to satisfy the simple requirement of this application.  The single file-base database was fairly insecure, but it is something I am willing to give up for now in exchange for a rapid development model.  One pleasant surprise was its ability to support concurrent user, and so far we have as much as 3 people access the database at the same time without too much problems.

After 3 weeks of hard work, the application went live on April 5th 2010.  A good chunk of the time was spend migrating the data from an old Fox Pro database an Excel Spreadsheets into the database.

Access proved to be a good choice.  We have been using the application for the last month or so, and adding more features and customization as we go.  The latest module is a service report module which allow us to collect data correlating service calls by our support team back to the customer.  In the beginning, it will seem like a chore.  Hopefully over time, it will allow us to gain better insight in our business and better serve our customer.

I was very tempted to move the database to a SQL Server in the near future, and my justification is better security and better support for multi-user.  The first issue regarding security is valid, but I am not sure it justifies the time and investment required at this stage.  The second issue is simply my techie side talking, since the Access database should be able to support the current load without any problem.

Microsoft publish a great document to help user rationalize the decision of when to migrate from Access to SQL server. The document is simply named "When to migrate from Access to SQL Server"  Inside the document, it has a fairly nice pie chart illustrating how many application will actually need upsizing:

SQL Server Salesmen are probably not too help with the Microsoft employee who wrote the document :)