-
Is your Access application split
into two databases?
Most Access developers split their Access application into two
files the application database and the data database. The
application database (the forms, reports, saved queries and
programming) is installed on the local hard disk, and the data
file is stored on the network drive for access by multiple users
over the network.
This is useful for when you want to share your Access data
across the network. Read our guide on
how to link your Access database to an Access or SQL Server
backend.
-
Do you always check the validity of linked tables?
Linking a table from an external database allows you to read data, update and add data (in most cases),
and create queries that use the table in the same way you would with a table native to the database.
With the Microsoft Jet database engine, you can create links to tables in Access databases,
as well as other data formats supported by Microsoft Jet's installable ISAM drivers (Excel, dBase, Paradox, Exchange/Outlook, Lotus WKS, Text, and HTML)
and ODBC drivers.
It is essential that all linked tables in the front end application
are in sync with a corresponding back end database.
Reasons for validating linked tables are:
- In the front end application linked tables seem to get
corrupted occasionally after compression of back end tables
- A shared drive containing the back end database was renamed
-
Do you always check bindings are OK?
It is essential that if any of your forms and controls have their RecordSource
or ControlSource set to a table (as is likely the case), you
must ensure that linkages to the table are not broken. The last
thing you want is for a user to open a form, only to find a
whole lot of errors because the underlying fields or tables have
had their name changed or been removed.
In databases (particularly ones with a lot of tables) it is
difficult to keep bindings in sync. The manual way to do this is
to compare a list of the names of the tables in your backend
database with the form record sources in your front-end and reconcile. This
can be very time-consuming, which is why we use a program called
SSW Performance PRO! to
automatically scan your front-end for any broken table bindings.
Figure: If a field or table is renamed or deleted, the user
will only get an error when they open the form
Figure: SSW Performance PRO! automatically scans your
front-end for broken bindings and produces an easy-to-follow
report
-
Do you only select the records you
need?
Access developers commonly make the mistake of retrieving all
the records from a table when opening a form, that
is, setting the forms Record Source to a whole table or
query. When the number of records increases to the thousands,
selecting all records for a form can cause major performance
issues such as sluggish forms and network lag.
In most cases, your users do not need every single record
from a table or query (for example, they only need to view 1
contact record at a time). To get the best performance out of
your application, implement a record search form to select only
the record/s that the user wants to view. For more information
on how to implement this, see the Microsoft whitepaper,
How to Migrate from
Access to SQL Server under Step 6: (Optional) Fix the
Performance of Very Slow Forms in Part C.
-
Do you fill combo boxes and listboxes only when you need them?
Access developers make the common mistake of retrieving all records for dropdown lists on a form, that is,
setting a controls Control Source to an entire table or
query column. Because every single value is retrieved on form
load, the form's loading time can become very slow, especially
if there is more than one combo box on the form or if the data
is being retrieved over the network from a SQL Server database.
The solution is to populate the drop-down lists
when the user activates them.
Tip: Use an event procedure or a button to set the row source
for the drop-down list. For example, Me!myDrop List.Row Source =
Q where Q is, once again, either the name of a query or an SQL
string.
Tip: Drop-down lists themselves will be more responsive if they
return fewer records. Try cascading criteria so that successive
lists are limited by the selection in a previous list. The row
source query for a list could depend on the item selected in a
previous list as in this example: Q = " SELECT Field1, Field2
FROM Table1 WHERE Field3 = " & Me!DropList1.Value
For more information on this topic, see the Microsoft
whitepaper,
How to Migrate from
Access to SQL Server under Step 6: (Optional) Fix the
Performance of Very Slow Forms in Part C.
-
Do you use automated tools to ensure database integrity?
It is nearly impossible to manually check complete
consistency across your Access database - are all relationships
valid? Are there any corrupt tables? We use
FMS Total Access
Analyzer
to conduct a deep scan of our databases to ensure
that all data is clean and everything is in working order.
Read this guide on using FMS Total Access Analyzer
to detect and correct errors, and document your database objects.
-
Do you decompile your code?
Decompiling your code can significantly reduce the size of your Access file and correct some corruption.
Access stores code as both source and compiled code streams. Sometimes some of the compiled code streams become corrupt and need to be flushed.
Running Access in decompile mode causes all the compiled code streams to be marked as invalid and therefore removed.
Since the compiled code streams are removed, the database size is also significantly reduced when the database is compacted.
Note: the decompile option is an undocumented feature of MsAccess.
To run access in decompile mode, use the command line "MSACCESS.EXE /decompile"
-
Do you use Linked tables instead of ADP when using SQL Server as a backend?
Linked tables are a more flexible solution than ADP. They have the ability to have local queries and local tables, and the ability to connect to multiple data sources.
Any performance considerations of reports are negated by using SQL Server Reporting Services for reports.
Most developers also prefer modifying SQL tables directly within SQL Server's management studio.
The preferred way to connect to SQL Server is MDB file format or ACCDB file format. This enables you to use the full flexibility of local tables and local queries, while leveraging the full power of SQL Server.
In addition, MDB and ACCDB files link to multiple SQL Servers and a wide variety of other data sources.
Office Access 2007 contains many new features available in both MDB and ACCDB file formats, but only a subset of those features are available in ADPs.
Because of the layers required to get from Access to SQL Server in the ADP architecture, it is often easier to optimize MDB/ACCDB file solutions.
However, there are some scenarios where a report might be generated significantly faster in an ADP file.