Neville
Silverman

Visual Basic Programmer, Sydney
Microsoft Access Database Programmer

Custom Built Software
System Optimisation
Office Automation
Phone Australia
(02) 9453-0456

Improve Microsoft Access Database Performance

Improving Microsoft Access Database PerformanceI have optimised many Microsoft Access Database Applications over the years – identifying and resolving Access Database problems is my speciality. So if you have reliability or performance issues with your Access application, I am sure that I can help.

The number of simultaneous users that can use a Microsoft Access Database system varies from 5 to 30. The big difference in the number of users that can be supported relates to how well the Microsoft Access Database system has been optimised.

Microsoft Access Split Database Architecture

In order to create a multi-user administration system, the Access Database is split into Front-End and a Back-End databases.

Improving Microsoft Access Database Performance

The Front-End Access Database on each user's PC, contains the Forms and Visual Basic code. The Back-End Access Database contains the records shared by the users.

Improving system efficiency

Increasing the number of users that can get reasonable performance involves increasing the Front-End Database and Back-End Database efficiency, as well as reducing Network traffic. The problems may be caused by complex applications, the Visual Basic coding, the Front-End and Back-End Database structures or Access Database limitations.

If your Database application is constrained by the limitations of Microsoft Access, I may recommend a migration to Microsoft SQL Server. But before this option is chosen, there is usually much that can be done to improve performance and reduce the bottlenecks.

Here are some of the optimisations that I carry out to extend the life span of Microsoft Access systems:

Optimising the Network Traffic

Performance problems related to the Network are usually the result of Front-End and Back-End Database inefficiencies.

Upgrading the Network Interface Controllers (NICs) to Gigabit Ethernet devices may help – but you will get the greatest improvements by optimising the Front-End and Back-End Access Database activity.

Optimising the Microsoft Access Front-End

There are literally thousands of optimisations that can improve the performance of the Front-End Access Database.

A detailed analysis of all critical aspects of an Access Database application will identify all issues that will normally go undetected. I will:

Optimising Microsoft Access Forms

  • Introduce a comprehensive error handling system
  • Identify all objects, routines and properties with inefficiencies
  • Ensure that Database record handling is efficient
  • Delete all unused procedures and variables
  • Ensure that all object references are eliminated after use
  • Delete all redundant Forms, Queries, Reports, etc – Access Databases are often used as a rubbish dump
  • Ensure that all variables are Type Cast for efficiency and maintainability
  • Enforce coding standards where possible
  • Ensure that the record locking strategy is efficient and sufficient

The changes will ensure that the system is easily maintainable and error free.

Optimising the Microsoft Access Back-End

There are many issues that will impact performance on the Back-End Access Database – these need to be identified and eliminated.

Performance problems can arise from:

Optimising the Microsoft Access Database

  • Incorrect Table structure
  • Inefficient Indexes
  • Unicode fields
  • Object fields
  • Primary Keys
  • Binary Large Objects (Blobs
  • Memo fields
  • Invalid Links
  • Field Name conflict

A comprehensive database analysis will identify all these issues.

A Stable Access Database Platform

These Optimisations will stabilise an Access Database Administration system, and may be the starting point for adding further functionality.

Or it may provide a sufficient safety margin of time for an alternative administration system to be built.