• Choose a category
• All
• Classic-Fiction

By Root 1961 0

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. Start Access and load 08-07FE.MDB. Choose File → Get External Data Link Tables and select 08-07BE.MDB as the Access link database. At the Link Tables dialog, select tblPeople and click OK. (To appreciate the extra demands made on a multiuser application, you may wish to move the 08-07BE.MDB database to a file server on your local area network first.)

SPLITTING MULTIUSER DATABASES

This solution makes use of a common multiuser technique: splitting the application and data into separate databases. Multiuser application performance can be improved considerably if you place the data (or backend database) on the file server and a copy of the application (or frontend database) on each user's desktop. Access includes the Database Splitter Wizard, which makes it easy to split an existing database into data and application databases. Select Tools → Database Utilities → Database Splitter to run the wizard.

Once you've fixed up the link to tblPeople in the backend database, open the frmPeopleFindFirst form in form view and note how long it takes to load the form. Enter the value 60000 into the text box in the header of the form. Press the Goto Record button to move to the record with an ID of 60000. The time this operation takes is displayed to the right of the command button (see Figure 8-16).

Figure 8-16. The frmPeopleFindFirst form

Now close the form and open the frmPeopleRSChange form in form view. This form is similar to frmPeopleFindFirst, except that it initially loads with only one record in its recordset. Because of this, load time should be faster than for frmPeopleFindFirst. This form also differs in how it searches for records. Instead of using the potentially slow FindFirst method to navigate to a different record, it changes the record source of the form on the fly. Enter the value 60000 into the text box in the header of frmPeopleRSChange and press the Goto Record button. The time this operation takes should be faster than for frmPeopleFindFirst (see Figure 8-17).

Figure 8-17. The more multiuser-friendly frmPeopleRSChange form

Although the performance difference between these two forms may be noticeable with 60,000 records in the sample database, it's not that great. With more records or across a busy network, however, the difference will be much more significant. Still, even without a noticeable performance improvement, this technique significantly reduces the load you are placing on the network.

Follow these steps to create a form that uses the record source changing technique of frmPeopleRSChange:

Create a new form or edit an existing form in design view. Change the RecordSource property of the form so that it initially loads no records. The most efficient way to do this is to use a Select statement that restricts the record source by its primary key field to a nonexistent record. For example, we used the following record source for frmPeopleRSChange:

SELECT * FROM tblPeople WHERE ID = 0

This will cause Access to place you on the new record that's normally at the end of a form's recordset. If you prefer, you can use a Select statement that returns some small subset of the records instead.

Add an unbound