How to fix your Microsoft Access Database

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:

Settings/Environment

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

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

Code

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.

What then?

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

Database corrupting
Data missing
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
Symptom
Database will not open with the error "Unrecognised database format" or "Microsoft Access has detected corruption in this file" or similar
Data known to be in the database disappears
Data on a form is linking to the wrong parent record
An error message is displayed when opening a form. Sometimes this will not impact the running of the form and other times it will heavily impact or prevent the form being used at all
An error occurs or the form closes when updating specific fields or clicking buttons
When opening a form hangs for a long time before displaying data or is unresponsive for a period of time after opening
When closing a form or navigating to a new record the data changes made on the previous form are not saved
Access will not open or throws errors when opening after an update to Microsoft Office/Access
Resolution
For 2-layer systems (front-end and back-end databases linked) get everyone out of the database,
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

If this is a one-off occurrence with no other symptoms (e.g. database corruption) then it is probably user error, even if they don’t admit it. Other than this data loss can be caused by database corruption. If data tends to be lost within the same area of the system, it can also be caused by incorrect table relationships causing the data to link to the wrong parent object so the underlying tables should be checked to ensure correct implementation of Primary and Foreign keys
This can occur if the table relationships are set up incorrectly or the Link Parent Fields/Link Child Fields are set up incorrectly on subforms. For subforms the Primary Key of the table should be used as this should appear in both the parent and child tables if they have been created correctly
Most often this is caused by an event (Code or macro) running on the Form Load, Form Open or Form Current event which encounters an error. If this happens you can try holding down Ctrl+Break (Pause on some keyboards) which should allow you to view the offending code.

Note: This is only for users happy to dive into the code and if not, we are always here to help if required

Macros/code is usually the fault here. If error trapping is properly implemented in code, then this should not happen because the error should be handled without detriment to the form. The only way to check is to see what event is attached to the control or button and step through the event(s) in code while performing the same tasks.

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

This can be due to several factors some of which being:
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
This will usually be due to a silent error occurring which means an error is occurring in code but is not being handled by the application therefore preventing the record from being saved. Another reason may be that the database is beginning to corrupt so backing up the data then Repairing and Compacting may fix this
This behaviour is usually because the references within the code are no longer valid. Within the code window select the "Tools - References" and see if any of the ticked items is flagged as "Missing". This would indicate that MS Access cannot find a necessary file which can happen if an older database is used on a newer version of MS Access but can also happen if required files are moved or removed from the client PC

Other considerations

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:

Contact Us