Focus on Popular Objects to Speed up an Access 2007 Database

A number of times this year I have been asked to speed up an Access database. This article outlines how usage data can make this quest more focused. The article also delves into an Access 2007 ACCDB only feature called TempVars. But firstly let's discuss some users’ scenarios that I've had to deal with this year.

Situation one: A skilled Excel technician has set up a database with numerous related tables. When he rang me for advice, he was adamant that he needed to upgrade to SQL server to speed up the database. As the database was only 20 MB in size, I doubted this but still we had the meeting. Some of the forms were very slow, had many Tab controls with hundreds of fields scattered across many subforms. In addition, the training users had was to scroll through records one at a time to find the record that they were interested in looking at. Just adding a find record box made finding the data a lot quicker.

Situation two: A complex database that had been in development for six years and now the developer had left the business. The last three years they had done nothing to the database apart from compacting and repairing the database but the performance was woeful. In this database, some forms could take up to two minutes to close when the close button was pressed. Also, some reports took nearly an hour to run.

Situation three: A huge database already converted to use SQL Server as a back-end; there were 250 forms and 80 tables. Performance was sluggish throughout during peak periods.

Okay. So what can you do? Initially I think it is safe to assume that the slow database will be a complex one. Therefore, if you want to speed up every object, it's going to take a long time. This means you can only work on some of the database and the best way to identify what objects in the database are worth working on is too initially proceed as follows.

Interview the key users to find out which forms and reports are slow and make a list. Find the five most popular slow objects from that list and work on those. This will give you a good idea how much difference you can make and how long it will take.