Microsoft Access offers a variety of different ways to how a database can be used. There are six general types of applications which a database will naturally fall under. They are:
• Personal or One-to-one use
• Small-business (SME) use
• Departmental use
• Corporation-wide use
• Front-end only use (client/server databases)
• Web use
Of course the above uses can be merged into each other but at the very beginning when planning your Access database, you need to know the scope and general use – hence the above categories.
At a basic level, you can use Microsoft Access to develop simple, personal database-management systems (which can also be relational – RDBMS). Some users automate everything from their music CD collections to their home finances and is very easy to use with the built in templates and where applicable wizard tools that make MS Access look like a product that anyone can use.
With the prompts for a new database and after answering a series of questions, you have a finished application with a switchboard that enables you to easily navigate around your database application, load data-entry screens, run reports, and with all the underlying tables that support them.
If you’re an end user and don’t want or need to spend too much time learning the intricacies of Access, you’ll be satisfied with Access as long as you’re happy with a wizard-generated or template driven personal application. Of course, you can still make some modifications to what the wizards or templates have generated, hopefully without any problems occurring from your tinkering!
However, it’s when you want to substantially customise a personal application without the proper knowledge in place that problems can or more accurately will happen.
Which type of Microsoft Access Database is deemed the best fit?
MS Access is an excellent application for developing a quick and simple to use database that can manage data processes of a small business. The ability to create simple to build macros and add the more advanced Access VBA code modules allows power users and developers to create code libraries of reusable functions and procedures, with the ability to add code behind a form or report to create powerful custom front-ends workflows.
The main limitation of using Access for developing a customised small-business application is the time and money involved in the development process. Many people will use Microsoft Access database wizards to begin the development process but find they still need to customize their applications in ways they can’t accomplish on their own workflows.
This can be exponential for the small-business (SME) owner who often experiences problems on an even greater scale than say an individual or personal user because of the demands of a small-business application are usually much higher than those of a personal application.
Many solicitors, accountants and other professionals have called the author after they reached a tipping point or dead end in the development cycle. They’re always dismayed at how much money it will cost to make their application usable (to their original expectations).
I’ve seen many examples where for instance a small firm of accountants who built a series of forms and reports to automate their office processes and struggled when it came time to generating invoices, enter payments, and produce financial reports.
Although at first glance these processes seem simple enough, on further examination the accountancy firm realized that the wizard-produced or template driven reports and forms did not provide the level of sophistication necessary for their billing system.
Unfortunately, the accountancy firm did not have the time or programming skills to add the necessary features. So, in using Microsoft Access as a tool to develop small-business applications, you must be realistic about the time and money involved in developing anything but the simplest of applications.
Access is however, perfect for developing applications for the larger department based organisations. Most departments in larger corporations have the development budgets to produce well-designed applications.
Unfortunately, most departments also usually have a champion or guru who is more than happy to help design forms and reports. This gives the department a sense of ownership because it has contributed to the development of its application. If complex form, report design, or VBA coding is necessary, larger corporations usually have on-site resources available that can provide the necessary assistance. If the support is not available within the organisation, most companies are willing to outsource to obtain the necessary expertise – namely myself!
For the higher level application use of Microsoft Access, the options and products start to be more competitive as there are alternatives available. The scalability of a Microsoft Access database can be questioned here when comparing to other and more scalable solutions for a client/server application which naturally would include Microsoft SQL Server.
At this level, Access can be adapted as a front-end application if only to serve as an alternative as a reporting tool since the higher end systems don’t always ship with such functionality and at the same time keeping the cost of ownership lower.
The later version of Access (from version 2010) now provides better and more integrated web solutions which will support all levels of use and provides better collaboration meeting the objectives for most end-users and decision makers.
So what’s next? If you have the vision but not the expertise, that’s where I can help you. If you have started to build and test a database but need a consultant to ‘hold your hand‘, then I can help you too.
Drop me a line from my contact page and depending where you are based, I can either pop on site or handle your queries online.