Access Cookbook, 2nd Edition [Electronic resources] نسخه متنی

Recipe 8.7 Accelerate Multiuser Applications

8.7.1 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?

8.7.2 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. 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).

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:

1. 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.

2. 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.

3. 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

4. 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 Recipe 8.7.3 for information on how to customize this code for
   your particular form.

5. Save the form and switch to form view to test it.

8.7.3 Discussion

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 matchesthe 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

Additional optimization strategies for client/server applications are
discussed in the Solution in Recipe 8.8 and
in Chapter 14.