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:
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 text box named txtGoto to the form’s header. Add a command button control named cmdGoto to the right of the text box.
Create a new event procedure for the Change event of the txtGoto text box. (If you’re unsure of how to do this, see “How Do I Create an Event Procedure?” in the Preface of this book.) Add the following code to the event procedure:
Private Sub txtGoto_Change( ) ' Enable cmdGoto only when a character ' has been typed into txtGoto Me!cmdGoto.Enabled = (Not IsNull(Me!txtGoto.Text)) End Sub
Create a new event procedure for the Click event of cmdGoto and add code similar to the following (or copy the cmdGoto_Click event procedure from frmPeopleRSChange; however, that event procedure has additional code that times the operation, which is eliminated here):
Private Sub cmdGoto_Click( ) ' Go to new record by changing the ' form's RecordSource property Dim ctlGoto As TextBox Dim ctlTime As TextBox Dim varCriteria As Variant Dim lngStart As Long Dim lngEnd As Long Dim dblTime As Double On Error GoTo HandleErr Const acbcQuote = """" ' Start the timer. lngStart = acb_apiGetTickCount( ) Set ctlGoto = Me!txtGoto Set ctlTime = Me!txtTime ' 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 " & acbcQuote & _ ctlGoto.Value & "*" & acbcQuote End If ' Change the form's recordset based on criteria. Me.RecordSource = "SELECT * FROM tblPeople WHERE " _ & varCriteria lngEnd = acb_apiGetTickCount( ) ' 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 ' Post the time to txtTime. dblTime = (lngEnd - lngStart) / 1000 ctlTime = "Operation took " & Format(dblTime, "##0.00") _ & " seconds" ExitHere: Exit Sub HandleErr: Select Case Err.Number Case Else MsgBox "Error#" & Err.Number & ": " & Err.Description, _ , "Goto Procedure" Resume ExitHere End Select End Sub
See Section 8.7.3 for information on how to customize this code for your particular form.
Save the form and switch to form view to test it.
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
Next,
the criteria of the SQL Select
statement is
constructed using this code:
' 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. Additional optimization strategies for client/server applications are discussed in Section 8.8.2 and in Chapter 14.
3.144.124.232