Improve performance of an Access project

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.

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.

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.

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.

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.

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

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.

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.

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

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.

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.

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

Tip

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

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

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

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

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

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

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

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

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

  Security  Use bound span controls or hyperlink controls with caution. Unauthorized users may be able to insert harmful HTML text into these controls. Processing this text without validation or modification could result in the loss of personal information or other data. To prevent the HTML text contained in a control from being rendered, encode the contents of the control.

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

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

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 .ade file in future versions of Access. The only way to convert an 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.

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.

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.

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

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.

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.