Access is used by many small organisations and is a great system to manage all your business contacts, information and processes. Systems we have built have saved businesses significant time and money.

A consistent and professional design will ensure future additions are efficient and also avoid potential issues are avoided. Design your system consistently.

Access tables
Access tables

Ensure your regular computer maintenance includes defragging your hard drive. This is something all computers need over time. A fragmented drive will increase the risk of your database running slow or corrupting.

Document your database system. Simple documentation on how the database is designed, what tables are for and commenting code helps your own developer and any other new developers in the future.

Like your computer, your database should be backed up. Regular backups should be scheduled. For critical databases you can have  backup system taking a copy every hour or even every few minutes, for very little cost (approx $9/month).

A well designed database will be split into front and backend. This is two parts with one file holding just the data. This makes installing updates and doing maintenance so much more efficient.

You should compact and repair your Access database regularly. Once a week is usually sufficient. With larger databases or more people using it you should do this more frequently. if you get a database is corrupt error message, this can usually be fixed by repairing and compacting. The error usually means the table indexes are corrupt.

access corrupted
Error usually meaning it is time to repair and compact your database

 

If you use PC and Apple Mac computers on the same network, separate the database machines as Mac computers create a lot of noisy network traffic. Minimise database crashes by using only quality networking components or devices.

“Air pockets” develop in your Access database overtime as you add and modify information. Compacting restores table records into their correct order. This makes the read-ahead capabilities of your database much more efficient.

Access relationship
Access tables and repationships

Speed will be enhanced significantly. Maintenance reclaims space occupied by deleted data. Special fields are also reset and all queries are marked to be recompiled the next time that they are run. These are two very important related benefits of the maintenance process. If the volume of data in the table has been dramatically changed, the query won’t execute efficiently. It is a bit like defragmenting your hard drive. A disk defragmenter will place all files, including your database file into contiguous clusters on a hard disk, making file access faster. If you do not defragment your hard disk, the operating system may have to go to several physical locations on the disk to retrieve the database file, making file access slower. It is also very common to find orphaned records, missing data, broken links and ‘blank’ entries that affect the integrity and reliability of your database and the accuracy of reports from your database system. Most of these you cannot see as due to the nature of these events they generally don’t show on screens and reports.

Access vba
VBA code in Access database

You should also inspect database tables for odd looking data or missing keys and orphaned records. Such issues can cause partial loss or information ‘hiding’ and inaccurate reporting due to lack of data integrity or even embarrassment, financial loss or loss of credibility through use of incorrect data and reports.

Your database provider should be helping you maintain your database every 6 to 12 months generally depending on the nature of your database system to avoid corruption and loss of data.