Improve performance of an Access project

In a client/server application, the general rules of thumb for reducing network traffic and optimizing application performance are:

• Do your data processing work on the server.

• Minimize roundtrips to the server to access the data.

• Limit the data you retrieve from the server.

Understanding OLE DB and the client/server environment

A Microsoft Access project uses OLE DB to connect and communicate with a Microsoft SQL Server database. Microsoft designed the OLE DB architecture to work efficiently in a client/server environment.

When you access data through a form or datasheet, OLE DB retrieves an updateable snapshot recordset from the SQL server database (up to the maximum record limit) and caches the data on the client ù making one roundtrip to the server. Access retrieves the data asynchronously, which means that you can continue to interact with Access while the data is being loaded. When you work with the data on the form or datasheet, whether browsing, filtering, sorting, finding or updating data, you are working with the data that is cached on the client.

Processing data on the server

To process data on the server, you can use stored procedures, triggers, user-defined functions, and SQL SELECT statements. You can also sort data on the server before retrieving it.

Stored procedures are a precompiled collection of one or more SQL statements and optional control-of-flow statements that can accept parameters and are processed as a single unit by Microsoft SQL Server. A stored procedure resides in the SQL Server database. Because a stored procedure is compiled on the server when it is created, it executes faster than individual SQL statements. Because a stored procedure runs on the server, it takes advantage of the extra processing power of the server.

Triggers are a special kind of stored procedure that automatically execute when you update, insert, or delete data. Use triggers to enforce business rules and restrictions that are more complex than those defined with check constraints. Unlike check constraints, triggers can reference columns in other tables. For example, a trigger can roll back updates that attempt to apply a discount (stored in a discounts table) to books (stored in a titles table) with a price of less than $10.

User-defined functions combine the best features of stored procedures and views into a single query that you can nest, pass parameters to, sort, and return values. In many cases, user-defined functions are a better alternative to stored procedures because you can return a single table of data or scalar value, hide the business logic and details of how that data or scalar value was created from the user, and simplify the complexity of your SQL statement syntax.

Sort records on the server by using a stored procedure, user-defined function, or SQL statement stored in the RecordSource property of a form or report (which is stored in the Access project, but executes on the server). Sorting records on the server is another way to take advantage of the extra processing power of the server.

Limiting data and property information retrieved from the server

As much as possible, limit the amount of data your application retrieves from the SQL Server database. Use views, stored procedures, user-defined functions, server filters, and SQL statement WHERE clauses to limit the data you or your users see in a form or datasheet. In general, avoid designing application scenarios where users browse the database in unrestricted fashion.

You can use the maximum record limit button or the MaxRecords property to limit the data you or a user can see in a form or datasheet.

You can also use server filters to limit records before they are retrieved from the database on the server, and filters to further limit records on the client. You can define a server filter in the ServerFilter property of a form or report. You can also optimize the performance of lists of values in Server Filter by Form and Filter by Form windows by modifying the FilterLookup property to control the number of values displayed if the values are retrieved from either the server or client.

Disable automatic row fix-up if not required. After you update a record in a SQL Server 2000 database, Access normally resynchronizes the data to reflect the changed values. However, these operations require additional round trips to the server. You can disable row fix-up by setting the PerformResync property to No.

Disable pre-fetching of default values if not required. By default, when you display a form or datasheet, Access shows the default values in the new row of the datasheet and the form controls for new records on forms. This requires a round-trip to the server. You can disable the pre-fetching of default values by setting the Fetch Defaults property to No.

Optimizing the SQL Server database

The following are general guidelines for optimizing SQL Server database performance. For detailed information on improving SQL Server database performance, see the SQL Server documentation.

• Design tables without redundant data. A well-designed database is a prerequisite for fast data retrieval and updates.
• Enforce referential integrity, which preserves the defined relationships between tables when you add, update, or delete the rows in those tables by defining primary key and foreign key constraints in the related tables.
• Choose an appropriate data type for fields. This can save space in your database and improve join operations. When defining a field, choose the smallest data type or field size that's appropriate for the data in the field.
• Create indexes for fields that you sort, join, or set criteria for. You can make dramatic improvements in the speed of a query by indexing fields that are on both sides of joins, and fields that are used to set criteria for the query. Indexes, however, can slow down the speed of record insertions, updates, and deletes.
• Use indexed views in SQL Server 2000 databases. Indexed views can greatly improve the performance of views found in data warehouse or other decision support applications where large amounts of data are aggregated into sums and averages. An indexed view forces the view's results to be stored in the database, so statements that reference the view will have substantially better response times. When using indexed views, you should be aware that modifications to the base data are automatically reflected in the view, which may cause additional maintenance overhead.
• Add a timestamp field to your tables to improve performance of record deletions and updates, especially if your table contains many fields. SQL Server uses a timestamp field to indicate that a record was changed (not when it was changed) by creating a unique value field, and then updating this field whenever a record is updated. SQL Server uses the value in timestamp fields to determine whether a record has been changed before updating it. In general, a timestamp field provides the best performance and reliability, especially in a multiuser environment. Without a timestamp field, SQL Server must check all the fields in the record to determine if the record has changed, and this check can slow performance.
• Periodically compact your SQL Server database to save disk space and reorganize indexes and data for faster access.

Optimizing linked tables created in the Link Table Wizard

 If the SQL Server database you are linking to is on another SQL Server, create a linked server (select Linked SQL on the first page of the Link Table Wizard). SQL Server, in most cases, will optimize joing operations.

Create an OLE DB data source that uses Transact SQL functions (select Transact SQL on the first page of the Link Table Wizard) if you are linking to Excel, text, or HTML files because the performance is generally better than creating a linked server.

Optimizing forms

When you design a form, determine the data and functionality needed, and then delay asking the server for this data and functionality until the information is requested by the user. For example, create a form so that it doesn't require data to be retrieved from the server during the form-opening process. For example, you can add a command button to the form to load data on the form.

Use batch transactions which are more efficient when you are updating related rows at once. For example, instead of making four round trips to the server to enter one order and three line items, a batch transaction enters the order and line items in a single round trip to the server.

Use SQL UPDATE and DELETE statements with the appropriate WHERE clause to update or delete multiple records that meet the same criteria. This is more efficient than opening a recordset and performing the updates or deletions one record at a time.

Display fields that take a long time to retrieve data from the server, such as text and image fields, only when requested. You can use the following techniques:

• Place text and image fields on a subform that uses the same record source. Access only retrieves the text or image values for the current record.
• Display the most important fields on a main form, and provide a button labeled "More Information" that opens another form containing other fields. Base the second form on a query that is passes as a parameter the primary key field on the main form.
• If your application is data-entry intensive, such as an order entry system, create a specific form for adding records that has the DataEntry property set to Yes. When you open the form to enter new data, Access doesn't display any existing records. This saves time because Access doesn't have to retrieve all the records from the server.

Optimizing reports

Instead of printing a long report, break up reports into smaller subsets of data by using views, stored procedures, user-defined functions, and SQL statements as your record source, or by using filters.

Use layout preview instead of print preview when you design your reports. You can take a quick look at the layout, which includes just a sample of the data in layout preview. When you are done working on the layout of the report, verify the data by opening the report in print preview.

You typically create and distribute reports on a schedule according to regular business cycles, such as weekly stock-level reminders, monthly sales reports, or quarterly financial statements. Create and distribute these reports as report snapshots in a batch file. Users can use the Snapshot Viewer to view and print the reports. When your users need up-to-the-minute reporting, use grouped data access pages.

If the lists in fields in the Filter By Form window or Server Filter By Form window take too long to display or aren't displaying values from the underlying table, you can change their performance. For both types of windows, you can prevent the lists from displaying the underlying table's field values, or you can change the record limit that determines if the list displays a field's values.

Optimize Filter By Form performance for all tables, queries, and forms in the database or optimize Server Filter By Form performance for all forms

For Filter By Form, these settings affect all tables and queries, and all text box controls whose FilterLookup property is set to Database Default. For Server Filter By Form, these settings affect all text box controls whose FilterLookup property is set to Database Default.

1. On the Tools menu, click Options.
2. Click the Edit/Find tab, and then set the Filter by form defaults according to the performance that you want to achieve.
   • If the list of values takes too long to display, you can prevent the list from retrieving the unique values for each field by clearing the Records in local snapshot check box, which affects the Filter By Form window, and the Records at server check box, which affects the Server Filter By Form window.
   • If lists aren't displaying the values from fields, check under Show list of values in to make sure that the appropriate boxes are selected, or try increasing the number in the Don't display lists where more than this number of records read box so that it's greater than or equal to the maximum number of unique values in any field in the underlying table or other record source.

Optimize Filter By Form or Server Filter By Form performance for a specific form

For each text box control on a form, you can specify whether the values stored in the field appear on its list in the Filter By Form window or in the Server Filter By Form window.

1. Open a form in Design view.
2. Display the property sheet for any text box control.
3. In the FilterLookup property box, set the property to the setting that will achieve the performance you want.
   • If the list takes too long to display in the Filter By Form window or Server Filter By Form window, either set the FilterLookup property to Never, or set it to Database Default and then make the appropriate changes in the Options dialog box (Tools menu).
   • If the list in the Filter By Form window or Server Filter By Form window isn't displaying the values that are stored in that field, or it's displaying only some of the values, make sure the FilterLookup property isn't set to Never. If the FilterLookup property is set to Database Default or Always, make the appropriate changes in the Options dialog box (Tools menu).

What is an index?

You can use an index to gain fast access to specific information in a database table. An index is a structure that orders the values of one or more columns in a database table, for example the last name (lname) column of the employee table. If you were looking for a specific employee by his or her last name, the index would help you get that information faster than if you had to search all the rows in the table.

The index provides pointers to the data values stored in specified columns of the table, and then orders those pointers according to the sort order you specify. The database uses the index much as you use an index in a book: it searches the index to find a particular value and then follows the pointer to the row containing that value.

In database diagrams, you can create, edit, or delete each type of index in the Indexes/Keys property page for a selected table. An index is saved in the database when you save the table that it is attached to, or when you save the database diagram in which that table appears.

As a general rule, you should create an index on a table only if the data in the indexed columns will be queried frequently. Indexes take up disk space and slow the adding, deleting, and updating of rows. In most situations, the speed advantages of indexes for data retrieval greatly outweigh these disadvantages. However, if your application updates data very frequently or if you have disk space constraints, you might want to limit the number of indexes.

Before creating an index, you must determine what columns to use and what type of index to create.

Index columns

You can create indexes based on a single column or on multiple columns in a database table. Multiple-column indexes enable you to distinguish between rows in which one column may have the same value.

Indexes are also helpful if you often search or sort by two or more columns at a time. For example, if you often set criteria for last name and first name columns in the same query, it makes sense to create a multiple-column index on those two columns.

To determine the usefulness of an index:

• Examine the WHERE and JOIN clauses of your queries. Each column included in either clause is a possible candidate for an index.
• Experiment with the new index to examine its effect on the performance of running queries.
• Consider the number of indexes already created on your table. It is best to avoid a large number of indexes on a single table.
• Examine the definitions of the indexes already created on your table. It is best to avoid overlapping indexes that contain shared columns.
• Examine the number of unique data values in a column and compare that number with the number of rows in the table. The result is the selectivity of that column, which can help you decide if a column is a candidate for an index and, if so, what type of index.

Types of indexes

Depending on the functionality of your database, you can create three types of indexes - unique, primary key, and clustered - in Database Designer.

Tip   Although a unique index will help locate information, for the best performance results it is recommended that you use primary key or unique constraints instead.

Unique Index

A unique index is one in which no two rows are permitted to have the same index value.

Most databases prevent you from saving a table with a newly created unique index when there are duplicate key values in the existing data. Your database may also prevent the addition of new data that would create duplicate key values in the table. For example, if you create a unique index on the employeeÆs last name (lname) in the employee table, then no two employees can share the same last name.

Primary Key Index

A database table often has a column or combination of columns whose value uniquely identifies each row in the table. This column is called the primary key of the table.

Defining a primary key for a table in a database diagram automatically creates a primary key index that is a specific type of unique index. This index requires each value in the primary key to be unique. It also permits fast access to data when you use the primary key index in queries.

Clustered Index

In a clustered index, the physical order of the rows in the table is the same as the logical (indexed) order of the key values. A table can contain only one clustered index.

If an index is not clustered, the physical order of the rows in the table does not match the logical order of the key values. A clustered index usually provides faster access to data than does a nonclustered index.

There are several things you can do to make your forms run faster.

General tips

• Avoid overlapping controls.
• Don't sort records in an underlying query unless record order is important, especially with multiple-table queries.
• If the underlying record source includes many records and you want to use the form primarily to enter new records, set the DataEntry property of the form to Yes so that the form opens to a blank record. If you open a form with all records showing, Microsoft Access has to read each record before it can display the blank record at the end of the recordset.
• Close forms that aren't being used.

Tips for forms that contain pictures and objects

• Use bitmaps and other graphic objects sparingly.
• Convert unbound object frames that display graphics to image controls.
• Use black-and-white rather than color bitmaps.

Tips for forms that contain code

A form without a form module loads more quickly and occupies less disk space. If a form or controls on the form don't use event procedures, the form doesn't require a form module.

• Eliminate code from forms that don't need it by setting the HasModule form property to No. The HasModule property specifies whether the form has a form module.
• Instead of using command buttons with event procedures, you can use command buttons with macros, or hyperlinks.

Notes

• If a form currently contains event procedures, and you decide to eliminate all event procedure code from that form, you must set the HasModule property to No to completely remove the form module.

• You can still use code with a form that has no form module by calling Function procedures from a standard module using an expression. (You can't use Sub procedures, because they can't be called using an expression.) To do this, define a Function procedure in a standard module and then call that function from an event property of the form or a control. For example, to use a command button to call a function to open a form, add an expression to the OnClick property of the command button like this: =OpenDataEntry().

Tips for subforms

• Base subforms on queries rather than on tables, and include only fields from the record source that are absolutely necessary. Extra fields can decrease subform performance.
• Index all the fields in the subform that are linked to the main form.
• Index any subform fields that are used for criteria.
• Set the subform's AllowEdits, AllowAdditions, and AllowDeletions properties to No if the records in the subform aren't going to be edited. Or set the RecordsetType property to Snapshot.

There are several things you can do to make your list boxes, combo boxes, and drop-down list boxes run faster:

• Base the list box, combo box, or drop-down list box on a saved query instead of an SQL statement. On a form, if you use a wizard to create a list box or combo box, Microsoft Access automatically sets the RowSource property of the control to an SQL statement. To change the RowSource property to a saved query, click the Build button next to the RowSource property box. With the Query Builder window open, click Save on the File menu, and type a name for the query. When you close the Query Builder window, click Yes when Microsoft Access asks if you want to update the property.
• Base the drop-down list box on a saved query instead of a table. On a data access page, if you use a wizard to create a drop-down list box, Access automatically sets the ListRowSource property of the control to the table you specified in the wizard. To retrieve rows from a query instead of a table, select the query name from the ListRowSource property box.
• In the query specified in the RowSource or ListRowSource property box, include only fields that are absolutely necessary. Extra fields can decrease performance.
• Index both the first field displayed in the combo box, list box, or drop-down list box and the bound field (if the fields are different).
• In combo boxes on a form, set the AutoExpand property to No if you don't need the fill-in-as-you-type feature.
• On a form, if the AutoExpand property for a combo box is set to Yes, the first displayed field should have a Text data type instead of a Number data type. In order to find a match in the list, Microsoft Access converts a numeric value to text. If the data type is Text, Microsoft Access doesn't have to do this conversion.
• On a form, if the bound field in a lookup combo box is not the displayed field:
  • Don't use expressions for the bound field or the displayed field.
  • Don't use restrictions in the row source.
  • Use single-table (or query) row sources rather than multiple-table row sources, if possible.
• Don't create list boxes, combo boxes or drop-down list boxes based on data in a linked table if the data won't change. It's better to import the data into your database in this case.

Note   The Query Builder is not available in a data access page. Wizards are not available in a stand-alone data access page, or when you point a data access page to a database other than the one currently open. In both cases, you will have to create a list box or drop-down list box on your own.

There are several things you can do to make your reports print faster.

General tips

• Avoid overlapping controls.
• Avoid sorting and grouping on expressions.
• Index fields you sort or group on.
• Avoid using domain aggregate functions. Include the field in the report's underlying query or use a subreport.
• Make sure the report's underlying query is optimized.
• Use the HasData property or NoData event to determine if a report is bound to an empty recordset.

Tips for reports that contain pictures and objects

• Use bitmaps and other graphic objects sparingly.
• Convert unbound object frames that display graphics to image controls.
• Use black-and-white rather than color bitmaps.

Tips for subreports

• Base subreports on queries rather than tables, and include only fields from the record source that are absolutely necessary. Extra fields can decrease subreport performance.
• Index all the fields in the subreport that are linked to the main report.
• Index any subreport fields used for criteria.

You can do several things to make your data access pages load faster in Page view or in Microsoft Internet Explorer 5 or later.

General tips

• When using a data access page within Microsoft Access, close all database objects that aren't being used. When using a data access page in Internet Explorer, close all windows that aren't being used.
• On data access pages that are used for data entry, set the DataEntry property of the page to True so that the page opens to a blank record.
• Index any fields that you sort, group, or filter on.
• Bind a data access page to an XML data file, so the page could get its data from a file located on the local client instead of having to access the database server.

Controls

• Avoid overlapping controls.
• Use bound span controls instead of text boxes to display any data that isn't updateable ù for example, to display autonumbered values, values derived from expressions, and hyperlinks.

Pictures and objects

• Use bitmaps and other graphic objects sparingly. Use black-and-white rather than color bitmaps.

Grouped pages

• Make sure the ExpandedByDefault property is set to False for the highest group level. Setting it to False for lower group levels will speed up other interactions after the page is opened.
• Make sure the DataPageSize group level property is set to a low rather than a high number, or is set to All. The lower the number, the faster the records are displayed.
• On a page that contains records with a one-to-many relationship, group records by table rather than by a field or expression.
• On a page that is based on tables with a one-to-many relationship, bind each section to a table rather than to a query. Microsoft Access retrieves the records from a table only as it needs to display them on the page. With a query, Access retrieves all the records before it displays any records on the page. Use a query only when you want to limit the data used on the page, calculate aggregate values, or perform a task that's possible only in a query.

If your Microsoft Access project (.adp) file contains Visual Basic for Applications (VBA) code, saving your Access project as an .ade file compiles all modules, removes all editable source code, and compacts the destination Access project file. Your VBA code will continue to run, but it cannot be viewed or edited.

Your Access project file will continue to function normally ù you can still update data and run reports. Additionally, the size of your Access project will be reduced due to the removal of the code, so memory usage is optimized, which will improve performance.

Saving your Access project file as an .ade file prevents the following actions:

• Viewing, modifying, or creating forms, reports, or modules in Design view.
• Adding, deleting, or changing references to object libraries or databases.
• Changing code ù an .ade file contains no source code.
• Importing or exporting forms, reports, or modules. However, tables, data access pages, views, stored procedures, database diagrams, and macros can be imported from or exported to non-.ade Access projects.

Considerations if you need to modify the design of forms, reports, or modules

Be sure to save a copy of your original Access project file. If you need to modify the design of forms, reports, or modules in an Access project file that you have saved as an .ade file, you must modify the original Access project, and then resave it as an .ade file.

You won't be able to open, convert, or run code in an Access 2002 .ade file in future versions of Access. The only way to convert an Access 2002 .ade file to a future version will be to open the original Access project that the .ade file was created from, convert it to the later version of Access, and then save the converted Access project file as an .ade file.

Considerations before saving your Access project file as an .ade file

Some restrictions may prevent you from saving your Access project file as an .ade file:

• You must have password access to the VBA code if a password is defined.
• If your Access project references another Access project or add-in, you must save all Access projects and add-ins in the chain of references as .ade or .mde files.

About references and .ade files

If you try to create an .ade file from an Access project file or an add-in that references another Access project or add-in, Access displays an error message and doesn't let you complete the operation. To save an Access project that references another Access project as an .ade file, you must save all projects in the chain of references as .ade or .mde files, starting from the first project referenced. After saving the first project as an .ade or .mde file, you must then update the reference in the next project to point to the new file before saving it as an .ade file, and so on.

For example, if Project1.adp references Project2.adp, which references Project3.mda, you would proceed as follows:

1. Save Project3.mda as Project3.mde.
2. Open Project 2.adp and change its reference to point to the new Project3.mde.
3. Save Project2.adp as Project2.ade.
4. Open Project1.adp and change its reference to point to the new Project2.ade.
5. Save Project1.adp as Project1.ade.

Microsoft Access combines compacting and repairing an Access file into a single process.

Compacting an Access file

If you delete data or objects in an Access database, or if you delete objects in an Access project, the file can become fragmented and use disk space inefficiently. Compacting the Access file makes a copy of the file and rearranges how the file is stored on your disk. Compacting a previous-version Access database won't convert it to Access 2002 format.

Compacting optimizes the performance of both Access databases and Access projects. However, in an Access project, compacting does not affect database objects ù such as tables or views ù that reside in the Microsoft SQL Server database instead of in the Access project itself.

Compacting has no effect on autonumbering in an Access project. However, in an Access database, if you have deleted records from the end of a table that has an AutoNumber field, compacting the database resets the AutoNumber value; the AutoNumber value of the next record you add will be one greater than the AutoNumber value of the last undeleted record in the table.

Repairing an Access file

In most cases, Microsoft Access detects whether an Access file is damaged when you try to open it and gives you the option to repair it at that time. If the current Access file contains a reference to another Access file that is corrupt, Access does not attempt to repair the other file. In some situations, Access may not detect that a file is damaged. If an Access file behaves unpredictably, compact and repair it.

Access can repair:

• Corruption in a table in an Access database.

• Missing information about the structure of an Access file's Visual Basic for Applications (VBA) project.

• Corruption in a form, report, or module.

• Missing information that Access needs to open a particular form, report, or module.

To help prevent corruption of Access files, follow these guidelines:

• Compact and repair Access files regularly. You can specify that Access compact a file automatically when you close it.

• Back up your Access files regularly.

• Avoid quitting Access unexpectedly. For example, don't quit Access suddenly by turning off your computer.  

• If you are experiencing network problems, avoid using a shared Access database located on a network server until the problems are resolved. If possible, move the Access database to a computer where you can access it locally instead of over the network.

Repairing files after Access shuts down unexpectedly

If a serious problem occurs and causes Microsoft Access to shut down, Access restarts, creates a copy of the file that was open, and names it filename_Backup.mdb or filename_Backup.adp, where filename is the name of the original file. If filename_Backup.mdb or filename_Backup.adp already exists, Access asks you to specify the name of the file. Access then attempts to compact and repair the original file.