Access Cookbook

Accelerate Multiuser Applications

Problem

You have a single-user application that you just converted to run on a network to be shared by multiple users. Your once-responsive application is now sluggish. How can you improve the performance of multiuser applications?

Solution

Moving a single-user application to a shared environment can make that application slower for at least three reasons. First, to read or write data from the database, the data must now travel across relatively slow network wires. This is almost always slower than reading and writing data directly to a local hard disk drive. Second, every time a record is written to disk, Access must spend time obtaining, releasing, and managing locks to make sure that two users do not write to a page of records at the same time. Third, if multiple users are trying to access the same records in the database, they must wait their turns before gaining access to the records. Because of these factors, you need to make an extra effort to optimize multiuser applications to bring their speed to an acceptable level. This solution discusses one way to improve performance by limiting the number of records in your form’s recordsets.

This solution employs two files, 08-07FE.MDB and 08-07BE.MDB. You’ll first need to link the data tables from 08-07BE.MDB (the “backend” or data database) to 08-07FE.MDB (the “frontend” or application database). Linking a data table allows you to use a table from one Access database within another Access database. ...