A number of times in 2008 I was 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.
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.
