Being immersed in Microsoft Access for the best part of twenty years, it still amazes me how many organisations still fail to take advantage of building an Access database to their own exact requirements but instead stick with the basic general templates within the system or buying into a solution that’s just too generic for the business sector.
One of my jobs is to review a company working practices and their workflows learning how the business works and which tools will best fit. If there is a need to build a database, then it’s probably safe to say Microsoft Office applications will already be on users’ desktops and that may also include the Microsoft Access database application (part of the professional suite).
Once it has been identified that the client will require a custom-built database, then the next step will be to advise them of the how best to go about planning, designing and eventually build that database.
Here are 5 top tips for the client to contemplate and muster over with an Access database developer consultant and be in the know before they engage in the building and implementation.
1. Looking at the end goal – reports!
Locate and identify what reports you will need to achieve. After all, this is what any database system should serve the process and aim for. Make a list of key reporting requirements which will actually help the consultant to formalise database architecture during the design brief. Prioritise this list as it will certainly be extensive but the more reports you identify the better the design approach. You only get out (as reports) with what you able to put in (via form interfaces).
2. Who will use the database? – forms!
Identify the user’s roles in your database application that will be responsible for the day-to-day running of an Access database. This should be split into different categories of use namely end users responsible for data entry, authorised users to run reports, other users (management) for the more sensitive and financial based reports, administrators who will maintain the system parameters and business rules and having one (or two) super user who has a more technical approach to re-designing aspects of the database by being able to add the ad-hoc report.
3. Data migration from older systems – tables!
It will be a high probability that you may need to populate data from other systems into your newly designed Access database into their ‘normalised’ tables (data source files). This will mean two aspects; a) cleaning data ready for importing and b) importing the actual data into Access tables and setting the correct indexes. The additional element to this area of thought will be to consider which data sets will be just a ‘one-off’ task versus any regular data input streams to feed the database.
4. Keep it simple – design methodology #1
This really should the first task in my top 5 tips but it’s positioned here so that when you look into this, it will probably require a revisit to the above three steps again. This is potentially a complex approach but the first aspect here is to keep your design simple by dividing business processes into smaller units.
For example, if this is an invoice system, then make the core process handle the invoice transaction and not deviate from this at all so it ends up being not just about invoices but suddenly you have a stock control system, a marketing system and contact management system in the same database.
Yes, they are all potentially related and can always be added into an existing Access database, but start with just one core process here.
5. Don’t break it!
If an existing system works well, don’t meddle with it. This may sound a bit odd but if you have an existing database system (hopefully as a MS Access database) then consider enhancing it further without going back to the drawing board.
A lot of database developers do not like to inherit other database systems in order to add and improve functionality but I’m standing up for the client here and save not just time but money too.
Carry out an audit of the existing database structure and design aspects and see if it can be adapted further or at least rule out this option before starting from a fresh canvas.
There are more tips I could give you here but the first 5 tips above will focus your thoughts leading to more questions I’m sure
