|[ Team LiB ]|
Recipe 8.7 Accelerate Multiuser Applications
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?
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. 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.)
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).
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).
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:
In a multiuser environment, it's always important to limit the amount of data sent across the network to your desktop. By default, however, Access binds forms to all records in the table or query to which your form is bound. This is fine for smaller recordsets of perhaps less than 20,000 records (the exact cutoff figure will vary based on the speed of your PCs, the speed of your network cards and file server, and the average network load), but it can slow things considerably for moderately large recordsets. This solution improves the performance of the form and reduces network traffic by carefully limiting the records in the form's recordset.
By using a SQL statement that initially returns no records as the form's record source, you can quickly open the form in append mode. When the user enters a value in the txtGoto text box and presses the Goto Record button, code attached to the button's Click event changes the form's RecordSource to the correct record.
The event procedure behind the cmdGoto command button begins by setting up an error handler, declaring a few variables, and setting ctlGoto to point to the txtGoto text box control:
On Error GoTo cmdGotoClickErr Dim ctlGoto As TextBox Dim varCriteria As Variant Const acbQuote = """" Set ctlGoto = Me.txtGoto
' Create criteria based on the type of data ' entered into txtGoto. If IsNumeric(ctlGoto.Value) Then varCriteria = "ID = " & CLng(ctlGoto.Value) Else ' A string, so search LastName varCriteria = "LastName Like " & acbQuote & _ ctlGoto.Value & "*" & acbQuote End If
In the case of the people form, we decided to be flexible and allow users to search on either last name or ID. You'll want to make sure the fields you allow the user to search are indexed. The code determines which field the user wishes to search by using the IsNumeric function to test if the entered value is a number. If so, the code constructs criteria using the ID field of tblPeople. If the entered value is non-numeric, then the code assumes the user wishes to search on LastName. Again, we add a bit of flexibility by allowing the user to enter partial matches—the criteria string is constructed using the Like operator. Because this is a Text field, we must surround the value with quotes, so we use the acbcQuote constant that we defined earlier in the procedure. Finally, we have added "*" (an asterisk) before the closing quote to perform a pattern match search.
If you wish, you can simplify this code on your own form to use a single field. Either way, you'll need to change the references to ID and LastName to match the names of the fields (not the control names) in your form's record source. If you decide to allow a search on a date/time field, make sure you surround the date/time value with # (pound signs) instead of quotes.
With the criteria built, the SQL statement is easily created:
' Change the form's recordset based on criteria. Me.RecordSource = "SELECT * FROM tblPeople WHERE " & varCriteria
Of course, you'll need to replace tblPeople with the name of the table or query on which your form is based.
The remaining code determines if any records were found:
' Now check the form's recordset to see if ' any records were found. With Me.Recordset If .EOF And .BOF Then MsgBox "No matching record found.", _ vbOKOnly + vbCritical, "Goto Procedure" End If End With
This portion of code is not absolutely required, because Access will pull up the "new" record if no matching records are found. However, you might prefer to notify the user when no records were found. You can do this by using the form's Recordset property to return a recordset object that you can inspect. If the recordset is empty, Access sets both the end of file (EOF) and beginning of file (BOF) flags to True, so you can use this fact to test for the absence of records in the form's recordset.
A simple error handler is included in this procedure. It's important to include error-handling code in all multiuser procedures to handle the cases where records are locked. See Chapter 10 for more information on developing multiuser applications.
The one negative side to using this technique is that users may find it restrictive if they are used to navigating freely among records using the navigation controls at the bottom of the form. The sample form allows users to grab a subset of records from tblPeople by entering a partial match on LastName. If you also need to return groups of records when using numeric primary key field searches, you can use two text boxes to allow users to search for a range of primary key values, perhaps including code that limits the range to some arbitrary number.
The techniques presented in this solution apply equally to client/server applications.
8.7.4 See Also
|[ Team LiB ]|