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

اینجــــا یک کتابخانه دیجیتالی است

با بیش از 100000 منبع الکترونیکی رایگان به زبان فارسی ، عربی و انگلیسی

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

Ken Getz; Paul Litwin; Andy Baron

| نمايش فراداده ، افزودن یک نقد و بررسی
افزودن به کتابخانه شخصی
ارسال به دوستان
جستجو در متن کتاب
بیشتر
تنظیمات قلم

فونت

اندازه قلم

+ - پیش فرض

حالت نمایش

روز نیمروز شب
جستجو در لغت نامه
بیشتر
لیست موضوعات
توضیحات
افزودن یادداشت جدید










Recipe 8.1 Accelerate the Load Time of Forms



8.1.1 Problem


The first time you open a form in
your application, it seems to take forever to load. Is there any way
to accelerate this?


8.1.2 Solution


You can radically improve the time it takes to load a form for the
first time by preloading your forms when the database is initially
opened. You can also decrease the load time for subsequent loadings
by hiding instead of closing forms. This solution shows you how to
improve form load time using these techniques.

Load the

08-01.MDB database. Note the time it
takes for the switchboard form to appear (see Figure 8-1). Make sure that the "Preload
and keep loaded forms" checkbox is unchecked; if
it's checked, uncheck it, close the database, and
start over. Now press one of the command buttons, such as the Orders
button, and note how long it takes Access to initially load the form.
Close the form.


Figure 8-1. The 08-01.MDB switchboard form


Now check the "Preload and keep loaded
forms" checkbox on the switchboard form and close
the database. Reload the database and again note the time it takes
for the switchboard form to appear. Load the Orders form, again
recording the form load time.

You'll see that the switchboard form now takes
longer to appear but that the subsequent form load time is
significantly shorter. That's because checking the
"Preload and keep loaded forms"
checkbox and reloading the database flips an internal switch that
causes the application to preload its forms (in a hidden state) as
the switchboard form is loaded by Access. This lengthens the time it
takes for the switchboard form to appear initially. However, because
the Orders form is now preloaded, it takes less time for it to appear
when you press the Orders command button.


A switchboard form (or menu form) is an unbound form used for
application navigation. Switchboard forms are usually made up of
labels and command buttons with an optional picture.

Follow these steps to set up your application to preload its forms:

  1. Create a table for storing the names of
    the forms you wish to preload. This table (zstblPreloadForms in the
    sample database) should have a single field, FormName, with a
    datatype of Text. Switch to datasheet view (see Figure 8-2) and add a row for each form in your
    application that you wish to preload.



Figure 8-2. Store the list of preloaded forms in the zstblPreloadForms table


  1. Create a switchboard form or edit your
    existing one.

  2. Set the form's AutoCenter property to Yes.

  3. Add the following code to the declarations section at the top of the
    form's module (replacing the values with the actual
    names for your table and splash form, if you've used
    different names):

    Private Const acbPreloadTable = "zstblPreloadForms"
    Private Const acbSplashForm = "frmSplash"
  4. Create a new event procedure for the form's Open
    event. (If you're unsure of how to do this, see
    How Do I Create an Event
    Procedure?
    in the Preface of this book.)

  5. Add the following code to the event procedure:

    Private Sub Form_Open(Cancel As Integer)
    ' Preload forms.
    Dim db As DAO.Database
    Dim rst As DAO.Recordset
    Dim varFormName As Variant
    On Error GoTo HandleErr
    DoCmd.OpenForm acbSplashForm
    Set db = CurrentDb( )
    ' Preload the forms listed in zstblPreloadForms.
    Set rst = db.OpenRecordset(acbPreloadTable, dbOpenSnapshot)
    Do While Not rst.EOF
    varFormName = rst("FormName")
    If Not IsNull(varFormName) Then
    DoCmd.OpenForm FormName:=varFormName, _
    WindowMode:=acHidden, OpenArgs:="StayLoaded"
    End If
    rst.MoveNext
    Loop
    ExitHere:
    DoCmd.Close acForm, acbSplashForm
    If Not rst Is Nothing Then
    rst.Close
    End If
    Set rst = Nothing
    Exit Sub
    HandleErr:
    MsgBox "Error " & Err.Number & ": " & Err.Description, , "Form Open"
    Resume ExitHere
    End Sub

    You can also copy this code from the frmSwitchboard1 form
    (

    not the frmSwitchboard form) in

    08-01.MDB . (The frmSwitchboard1 version of the
    form always preloads forms, thus eliminating all the code associated
    with the "Preload and keep loaded
    forms" checkbox.)

  6. Create an event procedure for the
    switchboard form's Close event. Add this code to the
    event procedure:

    Private Sub Form_Close( )
    ' Unload preloaded forms
    Dim db As DAO.Database
    Dim rst As DAO.Recordset
    Dim varFormName As Variant
    On Error GoTo HandleErr
    Set db = CurrentDb( )
    ' Unload the forms listed in zstblPreloadForms
    Set rst = db.OpenRecordset(acbPreloadTable, dbOpenSnapshot)
    Do Until rst.EOF
    varFormName = rst("FormName")
    If Not IsNull(varFormName) Then
    DoCmd.Close acForm, varFormName
    End If
    rst.MoveNext
    Loop
    ExitHere:
    If Not rst Is Nothing Then
    rst.Close
    End If
    Set rst = Nothing
    Exit Sub
    HandleErr:
    MsgBox "Error " & Err.Number & ": " & Err.Description, , "Form Open"
    Resume ExitHere
    End Sub
  7. Create the following functions in a
    global module (or import the basStayLoaded module from

    08-01.MDB ):

    Public Function acbOpenForm(strFormName As String, _
    fStayLoaded As Boolean) As Boolean
    ' Open specified form and pass it the
    ' StayLoaded argument.
    On Error GoTo acbOpenFormErr
    If fStayLoaded Then
    DoCmd.OpenForm strFormName, OpenArgs:="StayLoaded"
    Else
    DoCmd.OpenForm strFormName
    End If
    acbOpenFormExit:
    Exit Function
    acbOpenFormErr:
    MsgBox "Error " & Err.Number & ": " & Err.Description, _
    vbOKOnly + vbCritical, "acbOpenForm"
    Resume acbOpenFormExit
    End Function
    Public Function acbCloseForm(frmToClose As Form)
    ' If StayLoaded is True, hide the form instead of closing it.
    On Error GoTo acbCloseFormErr
    If InStr(frmToClose.OpenArgs, "StayLoaded") > 0 Then
    frmToClose.Visible = False
    Else
    DoCmd.Close acForm, frmToClose.Name
    End If
    acbCloseFormExit:
    Exit Function
    acbCloseFormErr:
    MsgBox "Error " & Err.Number & ": " & Err.Description, _
    vbOKOnly + vbCritical, "acbCloseForm"
    Resume acbCloseFormExit
    End Function
  8. Throughout your application, when you create code that opens a form
    and you wish to load that form only once, call the

    acbOpenForm function from Step 8. If you wish to
    open a form from code, you can use this syntax:

    Call acbOpenForm("formname", True)

    You can also
    call the function directly from an event property. In this case,
    enter the following in the event property:

    =acbOpenForm("formname", True)

    For those forms that you don't wish to keep loaded,
    change the second parameter of

    acbOpenForm to
    False.

  9. For each form you are preloading or
    loading with the

    acbOpenForm function, add a
    command button with the caption
    "Close". Enter the following in the
    event property for the button's Click event:

    =acbCloseForm(Form)

    Don't place any quotes around the
    Form argument.

  10. Make a copy of the form created in Step 2
    and name it frmSplash. This is what's known as a
    "splash form." Open frmSplash in
    design view and remove all the command button controls. Also remove
    all the code behind the form for this copy. In the area where the
    command buttons used to be, add a label control that contains an
    initialization message. For example, the label on frmSplash has the
    attributes shown in Table 8-1. frmSplash is shown
    in form view in Figure 8-3.


Table 8-1. Properties of frmSplash's lblMessage control

Property


Value


Name


lblMessage


Caption


Initializing...


BackStyle


Transparent


BorderStyle


Transparent


FontName


Arial


FontSize


14


TextAlign


Center


Figure 8-3. The splash form, frmSplash


  1. Open the switchboard form created in Step 2. Open the
    form's module and add the following constants to the
    declarations section of the module:

    Const acbcPreloadTable = "zstblPreloadForms"
    Const acbcSplashForm = "frmSplash"

    Change "zstblPreloadForms" to the
    name of your table from Step 1. Change
    "frmSplash" to the name of your
    form from Step 11.

  2. Select Tools Startup to open
    the database Startup dialog (see Figure 8-4).
    Select the switchboard form from Step 2 in the Display Form/Page
    field.



Figure 8-4. The database Startup dialog


  1. Close the database and reload it to test your startup procedure and
    switchboard form.



8.1.3 Discussion


Access forms are stored as binary data in
hidden system tables in your database. When you load a form, Access
reads data from the system tables to recreate and display that form.
This takes time. The solution described here improves the application
load time of forms by preloading them when the database is first
loaded. This means that the initial application load time will be
slower, but users are more tolerant of a long application load time
because it is a one-time commitment. As with most performance
optimizations, the benefits of this technique are especially
noticeable on slow machines.

Prior to
Access 95, you had to use an AutoExec macro to
initiate some action upon database startup; in recent versionsyou can
use the Startup dialog to specify a form to be opened when the
database is loaded. This solution takes advantage of the Startup
properties, but you also could have used an
AutoExec macro.

When the switchboard form opens, the
form's Open event is triggered and the code attached
to the Open event is executed. Unfortunately, when the Open event
procedure is called, the form has not had time to paint itself, so
users normally see nothing during the Open event procedure. To remedy
this, we created a "splash" form to
display during the potentially lengthy process. You
don't have to make the splash form the same size as
the switchboard form, but in this case, we made the two forms very
similar in appearance.

The code to preload the forms is shown here:

Set rst = db.OpenRecordset(acbcPreloadTable)
Do While Not rst.EOF
varFormName = rst("FormName")
If Not IsNull(varFormName) Then
DoCmd.OpenForm FormName:=varFormName, _
WindowMode:=acHidden, OpenArgs:="StayLoaded"
End If
rst.MoveNext
Loop

Each record from the zstblPreloadForms table is read and the named
form is loaded in hidden mode. In addition, the
form's OpenArgs parameter
is passed the string "StayLoaded".
You can use the OpenArgs parameter of
OpenForm to pass a custom string to a form, much as you pass
parameters to a function. This OpenArgs
parameter will be used later to decide what to do when the preloaded
form is closed.

Once the forms have been loaded in a hidden state, you
don't need to do anything special to make them
appear. Access is smart enough to make a hidden form visible when you
attempt to load it, which makes working with invisible forms easy.
However, we include wrapper functions for opening and closing your
application's forms in case you want some forms to
be treated differently. For example, you may not wish to preload and
keep all your forms loaded, because they will take up memory.

Like the Form_Open event procedure
attached to the switchboard form, the

acbOpenForm function passes the string
"StayLoaded" to a form via its

OpenArgs argument when you pass
True as the function's second
parameter. Closing the application form is then handled by

acbCloseForm , which is called by the Click event
of each form's Close button. This function
determines whether to close or hide the form by checking its OpenArgs
property, which was passed to the form when it was opened:

If InStr(frmToClose.OpenArgs, "StayLoaded") > 0 Then
frmToClose.Visible = False
Else
DoCmd.Close acForm, frmToClose.Name
End If

For forms that you do not wish to preload, don't add
them to zstblPreloadForms. For forms that you wish to close normally
when the Close button is pressed, open them using the following
syntax:

=acbOpenForm("formname", False)

If you have enough memory, you may wish to
preload all forms and not close them until the application exits. In
some situations, however, you may wish to be more selective. By using
the preload technique and the

acbOpenForm and

acbCloseForm functions throughout your
application, you can easily change your mind or customize form
preloading and form hiding for different requirements.

We did not remove from each sample form
the Close button and control box provided by the system. This means
that you can use one of these alternate mechanisms to bypass the
application-defined Close button (and the

acbCloseForm function) and close the form
instead of hiding it. Thus, you may wish to set the CloseButton and
ControlBox properties of your forms to No to prevent the use of these
mechanisms.

You
may wish to make zstblPreloadForms a hidden table. You can adjust the
hidden property of an object by selecting View Properties.


Benchmarking 101


Benchmarking different scenarios is a painstaking process. Because
Windows includes a hard disk cache and because Access itself caches
data, it's difficult to get fair and accurate
timings. Because of caching, the order in which you time things does
matter. Avoid jumping to conclusions without repeating the readings
several times in different orders. Also, there is no reliable
programmatic way to measure the time a form takes to load. Although
you can set timers at each of the form's events,
Access does some things internally after the last loading event has
fired. You will find that the only accurate way to test a
form's loading time is to manually test and average
the form load using a stopwatch.


/ 232