Is Microsoft Access bad?
Well no… We think Microsoft Access is the best non-enterprise level database on the planet but only if it is built correctly. Read on to find out more…
Why is Microsoft Access so good?
MS Access is one of the most user-friendly, versatile and helpful databases on the planet. For non-enterprise level users, we would say it’s the best database on the planet.
MS Access is low maintenance, very flexible and impressively well-served with functions and tools that give users everything they want – and a whole lot of other good stuff they didn’t know existed.
Data reports can be produced in a single step and give a business exactly the information they need – formatted, arranged and presented exactly how they need to see it.
Which is why, across the world, tens of thousands of businesses are run on Microsoft Access.
So what went wrong? Well, it’s not me it’s you…
Yep. The problem here is you. You’ve changed.
Or more to the point, your business has changed.
Your activities, your customers, the services you provide, the way you do things, the people who do them… not much of that today is the same as it was when your database was first installed.
You have different requirements of your Access database today to what you had before. The nature of your data, the volume of it… how you want it stored and how you then wanted it presented in reports will not be the same.
Your business didn’t stand still – but your database did
You’re basically using last year’s set-up for this year’s needs. The database can’t cope with that and, as it struggles, so do you.
At ICEF we spend our working day reconfiguring and enhancing MS Access databases so their owners can fall back in love with them again.
But here’s the interesting thing: over the years we’ve realised that, despite routinely fixing a very long list of very different MS Access difficulties and frustrations… almost everything we’ve ever done for our customers comes down to addressing just a couple of core failings.
This guide will tell you what those core problem areas are so that if you’re experiencing the problem… you know where to look for the solution.
Just 3 areas
Anything that’s not right with your Access database 90% of the time is going to fall within one of only three areas.
No matter what frustrating, disruptive, hair-pulling aggravations your database is delivering it all comes down to one of the three. Or two of the three. Occasionally – if you’re really unlucky – it’s all three of the three:
Over time database settings need tweaking for the continued smooth running of your database especially if your database version changes which can even prevent your database from loading altogether. Environmental factors, especially faulty PC and network hardware can also cause havoc with a database even if only 1 pc is affected.
Tables are the foundation of your database and as such should be treated with care when assembling. A good database consists of multiple tables each consisting of well-defined fields, holding specific data and linked via relationships to create a well-oiled foundation for your application
VBA Code allows the database to perform the non-standard ‘out of the box’ operations within the database. Any advanced processing that needs to take place will usually be performed by code. Similar functions can be performed by Macros, but code has the advantage of error trapping to allow for easy location of issues.
Once it – or they – are fixed then the difference you’ll experience in the way that Access will serve you can be incredible.
Get quick, accurate and useful data on revenues, inventory, order statuses, staffing, project tasks, open jobs – and anything else that you might record … and then make intelligent decisions based on what you now know.
Stop making do. Your database can deliver exactly what you want the moment it’s fixed.
Following are the things most likely to go wrong with a typical MS Access database along with the problem category and likely resolution that each error or glitch belongs to:
MS Access Problem Resolution
Form showing incorrect data
Crashing or Error Message when loading form or report
Error message appearing when performing specific task
Slow Form Loading
Data Not Saving
Database issues after upgrading Microsoft Access
Backup both front and back-end databases
Run Compact and Repair on back-end database then front-end database
Try opening the database again
Note: Corruptions can cause data loss so check data to ensure none has been lost
Note: This is only for users happy to dive into the code and if not, we are always here to help if required
Note: This should only be performed by a person with coding experience as it is easy to make matters worse by changing things in the code. If in doubt we are always here to help if required
PC Hardware - The client PC will be used for the brunt of the processing with an MS Access database so the hardware can play a large factor in performance. Having 8GB of RAM or more and having a Solid-State Disk drive can help improve speed of the database as well as everything else on the PC
Slow network speeds - A poor network connection can slow the performance of an Access database because Access pulls a lot of data across then network to find the data it needs. A poor network can also cause database corruptions in an Access database
Form Design - If there are a lot of drop-down lists on the form each with many items then this can also slow down form loading. If there are lots of callbacks to the database or other processing in the code behind the form this can also cause slow loading forms
Table Structure: It can be tempting when first creating a database to store all your data in a single table. While this may be ok for very small databases it can soon get out of hand. Before creating too much of your database it may be worth learning the basics of database normalisation as this knowledge will serve you well as your database expands.
Naming: Although naming will not break the database (Outside of using invalid names and characters for objects) they are important for future maintenance of the database; it is much easier to locate a field named “FirstName” on a form than a field named “Field12” and when looking through code this makes it easier to understand what data is being passed to or manipulated by code.
Form Design: Another important factor is the way your forms are designed. All data should be easy to locate within the form without having to search for it. Breaking the form into specific areas where related data can be grouped can help with this. It is a fine line between maximising the amount of data available to the user in a single place with avoiding clutter on the screen. Having well aligned, similarly sized controls can help with this and setting the Tab Order of controls to make sure the navigation of the form when using the tab is sensible practice.
Although not exhaustive, this list will hopefully help with identifying some of the most common issues faced by MS Access database users.
If you still require assistance, we would be happy assist where possible. You can contact us by either entering a short message in the contact form below or alternatively we offer a free database appraisal service where you fill out a brief Google Form giving an overview of your database and any issues you have with it. On receiving this form, we will produce a report detailing any issues you may have and how you could improve your database system therefore restoring it to the valuable asset it once was.
This is a free service offered by iCef Solutions and you can get your free appraisal by clicking the link below: