databasedev.co.uk - database solutions and downloads for microsoft access

Methods for Optimizing Multi-user Microsoft Access Applications

Following on from our previous article on optimizing multi-user applications, we are going to take a further look at other methods of optimizing Microsoft Access application performance.

In the previous article we focused on improving the performance of multi-user applications in a networked environment. We will continue on that route because multi-user applications by their very nature need to be in a networked environment. The important question here is the distribution of your Access application. In other words, do you centralize your application so that it is available to everyone at once or do you make it available to each user individually? If you choose to centralize Microsoft Access then it involves putting Access on a central server. This means that users will download Access and all of its associated libraries as well as the Access application itself.

This has several benefits:

• reduce cost of Access licenses
• allows for the use of dump terminals
• minimal hard disk requirements

Whilst the financial manager of a company will be delighted with the benefits of centralizing your Access application. The network administrator will not share in this delight, because of the following disadvantages:

• A massive amount of network traffic that will be generated which will inevitably slowdown application performance
• Enormous strain that it will put on the file server, since it has to send both Access application and libraries to enable clients to use its services.
• Finally, Access will not be able to support such a load because it is by its very design a single desktop based application and not really a multi-user application. So at best Access will not be efficient in this regard.

The other alternative to centralizing the distribution of your application is to make your application available to each and every user in the company. This means placing the front end of your Microsoft Access application (as well as Access itself) in the hands of each user. A file server will then be responsible for providing data for front end requests. This method of application distribution offers better performance for both application and network when compared to centralization. The disadvantage is that it maximizes licensing fees, increased hardware costs and uses more hard disk space.

Both these methods makes one thing clear and that is that by putting different parts of your access application on different systems (client or file server) you will be able to improve the performance of your Access applications.

Other performance gains that can be made come from the way in which an application is coded. An example of this is binding which is the point at which an object becomes known. Late binding and early binding. Late binding simply means that the identity of an object becomes known only as code is run as opposed to early binding which means that the identity of an object is known when the code is compiled and before it is actually run, so that all information about the object (such as its methods and method parameters) can be verified. Early binding provides significant performance gains compared to late binding. An example of early and late binding:

Sub earlybind()
    Dim early As New ADODB.Recordset
...
End Sub

In this example 'early' is an early bind. While below 'late' is a late bound:

Sub latebind()
    Dim late As Object 
      ...     
End Sub

This is just one example in which you can optimize your code which in turn will improve application performance.

I’ve not exhausted the list of techniques that can be used to improve application performance, but what we covered in the two articles is more than enough to get you started to create better performing applications.