Recently I tried one of the recommendations found at
http://www.granite.ab.ca/access/performancefaq.htm to
improve the startup performance of one of my Access 2002
applications.  I was pleasantly surprised by the results,
which I would like to share with others.

The application is fairly large and complex.  It is split
into a front-end MDB of 300 megabytes and 7 back-end MDBs
varying between 200 and 500 megabytes each.  The front-end
contains forms, reports, queries, code modules, temp
tables as needed, and links to the back-end MDBs.  

The front-end MDB runs on mostly high-end Windows XP or XP
Pro clients in a LAN consisting of 15 to 20 concurrent
users.  The back-end MDBs reside on a fairly high-end
Windows 2000 Server with Service Pack 3 installed.  

The MDBs are compacted nightly by an automated system.

User-level security is implemented in the front-end and
back-end.  Users join a central Workgroup Information File
(WIF) on the server.

The application is built around a conventional Switchboard
form that is always open.  Startup code in the Switchboard
performs various functions, including the following:  
Ensures that the user is joined to the proper WIF; ensures
that the user has an employee record;  ensures that the
front-end clients are properly linked to the back-end
MDBs;  ensures that the back-end MDBs are properly matched
to one another.

The problem:  When more than one user was using the
application, the startup code took forever to execute.

Here are the time trials:  Startup for the first user took
about 30 to 40 seconds.   Startup for each additional user
took about 10 MINUTES!

Performance was good once the startup code had been
executed, but the startup time was a very cold wet blanket.

I recently read the advice provided by Tony Toews here and
at http://www.granite.ab.ca/access/performancefaq.htm and
decided to try maintaining a persistently-open recordset
from each of the clients to each of the back-end MDBs.

I added a table with one field and no records to each of
the 7 back-end MDBs.  In the front-end MDB, I added a link
to each of these tables.  I added 7 hidden subforms to the
Switchboard.  Each of these subforms has as its
RecordSource a link to one of the 7 new tables.  When the
7 Switchboard subforms are loaded, a persistently open
recordset is maintained from each of the clients to each
of the 7 back-end MDBs throughout a client session.

The results are impressive:  Startup for the first user is
now about 15 seconds.  Startup for each successive user is
now about 34 seconds, nearly 18 TIMES FASTER than before.

I wish to thank Tony Toews and Granite Consulting, plus  
others who have generously shared their knowledge about
this issue.

Don Skolnik
Don Skolnik Consulting Services
San Diego, CA