Improve performance of an Access database

Microsoft Office Access 2003

You can use the Performance Analyzer to optimize the performance of a Microsoft Access database. The Performance Analyzer is not available in an Access project.

  1. Open the Access database you want to optimize.
  2. On the Tools menu, point to Analyze, and then click Performance.
  3. Click the tab for the type of database object that you want to optimize. Click the All Object Types tab to view a list of all database objects at once.
  4. Select the names of the database objects that you want to optimize. Click Select All to select all database objects in the list.
  5. Repeat steps 3 and 4 until you've selected all the objects you want to optimize, and then click OK.

    The Performance Analyzer lists three kinds of analysis results: Recommendation, Suggestion, and Idea. When you click an item in the Analysis Results list, information about the proposed optimization is displayed in the Analysis Notes box below the list. Suggestion optimizations have potential tradeoffs that you should consider before performing them. To view a description of the trade-offs, click a Suggestion in the list and then read the information in the Analysis Notes box. Access can perform Recommendation and Suggestion optimizations for you. You must perform Idea optimizations yourself.

  6. Click one or more of the Recommendation or Suggestion optimizations you want performed, and then click Optimize. The Performance Analyzer will perform the optimizations and then mark them as Fixed. Continue this process until the Performance Analyzer has completed all Recommendations and Suggestions that you want it to perform.

    To perform all Recommendation and Suggestion optimizations, click Select All, and then click Optimize.

    To perform an Idea optimization, click the optimization, and then follow the instructions displayed in the Analysis Notes box.

Note  The Performance Analyzer doesn't provide suggestions on how to improve the performance of Microsoft Access itself or the system it is running on.

ShowImprove the performance of Microsoft Access and your system

The following guidelines can help you optimize the performance of Microsoft Access on your computer:

  • If you're using databases that other users don't need to share, install Microsoft Access and all your databases on your hard disk drive rather than on a network server.
  • If you are the only person using a database, open the database for exclusive use; in the Open dialog box, click the arrow next to Open button, and then click Open Exclusive.
  • Make more memory available by closing applications that you aren't using.
  • Increase RAM on your computer. 40 MB of memory is recommended— 32 MB of memory plus an additional 8 MB of memory for Microsoft Access.
  • Don't use any of your RAM for a RAM disk.
  • Periodically delete unnecessary files and empty your Recycle Bin, compact your databases, and then defragment your hard disk with the Microsoft Windows Disk Defragmenter. To run the Disk Defragmenter, click the Windows Start button, point to Programs, point to Accessories, point to System Tools, and then click Disk Defragmenter.
  • In most cases, the default virtual memory setting used by Windows should perform optimally. However, in some situations, adjusting virtual memory parameters can improve performance. If you've already tried deleting unnecessary files and you still have a performance problem, try changing the default virtual memory setting in the following cases:
    • You don't have much disk space available on the drive that is currently being used for virtual memory, and another local drive with space is available.
    • Another local drive is available that is faster than the current drive (unless that disk is heavily used).

    In these cases, you might get better performance by specifying a different drive for virtual memory.

    You also might get better performance by specifying that the disk space available for virtual memory be at least 25 MB minus available RAM. For example, if your computer has 12 MB of RAM, you should specify at least 13 MB of virtual memory. You may want to specify more if you are running several large applications.

    To change Windows virtual memory parameters, in Windows Control Panel, double-click the System icon, click Performance Options under the Advanced tab, click the Change button in the Virtual Memory section, and then specify a different hard disk, or enter a value in the Initial size box that is at least 25 minus your available RAM.

  • If you have a wallpaper (full-screen background) bitmap on your Windows desktop, replace it with a solid color or pattern bitmap, or no bitmap at all.
  • If you use a screen saver, use a blank screen saver or consider not using one at all.
  • Eliminate disk compression software, or consider moving your databases to an uncompressed drive.
  • To ensure optimal performance, use substitution fonts only if you need dual-font support to display all of your data.

ShowImprove table performance

  • Use the Performance Analyzer to analyze specific tables in your database.

  • Design tables without redundant data. A well-designed database is a prerequisite for fast data retrieval and updates. If existing tables contain redundant data, you can use the Table Analyzer Wizard to split your tables into related tables to store your data more efficiently.

  • Choose appropriate data types 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 you sort, join, or set criteria for. You can make dramatic improvements in the speed of queries by indexing fields on both sides of joins, or by creating a relationship between those fields and indexing any field used to set criteria for the query. Finding records through the Find dialog box is also much faster when searching an indexed field.

    Indexes aren't appropriate in all cases, however. Indexes add to the size of the .mdb file, reduce concurrency (the ability of more than one user to modify a page at the same time) in multiuser applications, and decrease performance when you update data in fields that are indexed or when you add or delete records. It's a good idea to experiment to determine which fields should be indexed. Adding an index may speed up a query by one second, but slow down adding a row of data by two seconds and cause locking problems. Or it may add negligible gains depending on which other fields are indexed. For example, adding an index to a PostalCode field may provide very little performance gain if a CompanyName field and LastName field in the table are already indexed. Regardless of the types of queries you create, you should only index fields that have mostly unique values.

  • In a multiple-field index, use only as many fields in the index as necessary.

ShowImprove performance of linked tables

Note  You can link a table only in a Microsoft Access database, not a Microsoft Access project.

Although you can use linked tables as if they were regular tables in your Microsoft Access database, it's important to keep in mind that they aren't actually in your Access database. Each time you view data in a linked table, Microsoft Access has to retrieve records from another file. This can take time, especially if the linked table is on a network or in an SQL database.

If you're using a linked table on a network or in an SQL database, follow these guidelines for best results:

  • You can greatly enhance performance when opening the main database and opening tables and forms by forcing the linked database to remain open. To do this, create an empty table in the linked database, and link the table in the main database. Then use the OpenRecordset method to open the linked table. This prevents the Microsoft Jet database engine from repeatedly opening and closing the linked database and creating and deleting the associated .ldb file.
  • View only the data that you need. Don't move up and down the page unnecessarily in the datasheet. Avoid jumping to the last record in a large table. If you want to add new records to a large table, use the Data Entry command on the Records menu to avoid loading existing records into memory.
  • Use filters or queries to limit the number of records that you view in a form or datasheet. This way, Microsoft Access can transfer less data over the network.
  • In queries that involve linked tables, avoid using functions in query criteria. In particular, avoid using domain aggregate functions, such as Dsum, anywhere in your queries. When you use a domain aggregate function, Microsoft Access retrieves all of the data in the linked table to execute the query.
  • If you often add records to a linked table, create a form for adding records that has the DataEntry property set to Yes. When you open the form to enter new data, Microsoft Access doesn't display any existing records. This property setting saves time because Microsoft Access doesn't have to retrieve the records in the linked table.
  • Remember that other users might be trying to use an external table at the same time you are. When a Microsoft Access database is on a network, avoid locking records longer than necessary.

ShowImprove the performance of tables linked to SQL server

If the data in your front-end/back-end application consists only of Microsoft SQL Server tables, you can use either a Microsoft Access project or a Microsoft Access database as the front end of your application. However, by using an Access project, you will benefit from the advantages of projects such as a larger number of tables and larger database size. If you want the data in your front-end/back-end application to consist of both Microsoft Access tables and Microsoft SQL server tables, you must use an Access database as the front end.

If you're connecting to an external SQL database table, you can achieve the best performance results by linking to the SQL tables instead of opening the tables directly. You can open external SQL tables directly only by using Microsoft Visual Basic code. Linked tables are considerably faster, more powerful, and more efficient than directly opened tables.

Additional performance tips

  • Retrieve only the data you need. Design your queries to limit the number of records that you retrieve, and select only the fields you need, so that Microsoft Access can transfer as little data as possible over the network.
  • Don't use updatable result sets (dynasets) if you're not retrieving many records, are not updating the data, and don't need to see changes made by other users. To prevent result sets from being updated in a form, set the form's RecordSetType property to Snapshot. Snapshots are faster to open and scroll through than dynasets.
  • If you need to retrieve a large number of records, a dynaset is faster and more efficient than a snapshot. For example, moving to the end of a snapshot requires the entire result set to be downloaded to the local computer, but with a dynaset only the last screen of data is downloaded to the local computer. In addition, the fastest way to add new records to a table, form, or query is to click Data Entry on the Records menu. (Data Entry isn't available if the RecordsetType property is set to Snapshot.)
  • Use cache memory. If you will reuse the most recent data from the server while the application is running, it's faster to retrieve a single large chunk of data (many rows) and store it in a cache than it is to retrieve many individual rows. Microsoft Access forms and datasheets automatically use a cache. If you are retrieving data by using a Recordset object created in Microsoft Visual Basic, you can use the CacheSize property to specify how many records to retrieve at one time into local memory.
  • For bulk update queries against ODBC data sources, optimize performance on the server by setting the FailOnError property to Yes.
  • Avoid using queries that cause processing to be done on a local client computer. When accessing external data, the Jet database engine processes data locally only when the operation can't be performed by the external database server. Query operations performed locally (as defined by the SQL commands used to implement them) include:
    • JOIN operations between tables from different remote data sources. (For example, assume that the join involves a local table or query with few records and a remote table with many more records, and the remote table's join field is indexed. In this case, Access returns only the records that match the local table or query, thus greatly improving query performance.)
    • JOIN operations based on a query with the DISTINCT predicate or a GROUP BY clause.
    • Outer joins containing syntax not supported by the server.
    • DISTINCT predicates containing operations that can't be processed remotely.
    • The LIKE operator used with Text or Memo fields (may not be supported by some servers).
    • Multiple-level GROUP BY arguments and totals, such as those used in reports with multiple grouping levels.
    • GROUP BY arguments based on a query with a DISTINCT predicate or a GROUP BY clause.
    • Crosstab queries that have more than one aggregate, that have field, row, or column headings that contain aggregates, or that have a user-defined ORDER BY clause.
    • TOP n or TOP n PERCENT predicates.
    • User-defined functions, or operators or functions that aren't supported by the server.
    • Complex combinations of INNER JOIN, LEFT JOIN, or RIGHT JOIN operations in nested queries.

ShowImprove performance in a multiuser environment

The following guidelines can help you optimize the performance of Microsoft Access databases that are used in a multiuser environment.

  • Put only the tables on a network server and keep other database objects on users' computers. The database's performance will be faster because only data is sent across the network. You can separate the tables from other database objects by using the Database Splitter Wizard.
  • Choose a suitable record-locking strategy. If you use optimistic locking (No Locks), Access doesn't lock a record or page until a user saves changes to it, and data might be more readily available as a result. If you use pessimistic locking (Edited Records), Access locks the record as soon as a user begins to edit it— - records might be locked for longer periods of time, but users don't have to decide whose changes to save if two users edit the same record simultaneously.
  • Choose the appropriate locking level strategy (page-level or record-level locking).
  • Avoid locking conflicts by adjusting Refresh Interval, Update Retry Interval, Number Of Update Retries, and ODBC Refresh Interval settings (if applicable).
  • Optimize the performance of tables (including tables linked to other data sources such as Microsoft SQL Server).
  • Convert the workgroup information file from Access 97 or earlier to the current version of Access.

ShowImprove query performance

ShowDatabases, tables, and indexes

  • Compact your database. Compacting can speed up queries because it reorganizes a table's records so that they reside in adjacent database pages ordered by the table's primary key. This will improve the performance of sequential scans of a table's records because the minimum number of database pages will have to be read to retrieve all of the records. After compacting the database, run each query to compile it using the updated table statistics.
  • Index any field used to set criteria for the query and index fields on both sides of a join, or create a relationship between these fields. When you create relationships, the Microsoft Jet database engine creates an index on the foreign key if one does not already exist; otherwise, it uses the existing index.

    The Jet database engine automatically optimizes a query that joins a Microsoft Access table on your hard drive and an ODBC server table if the Access table is small and the joined fields are indexed. In this case, Access improves performance by requesting only the necessary records from the server. Make sure tables you join from different sources are indexed on the join fields.

  • Index the fields you use for sorting.
  • Use the Between...And, the In, and the = operators on indexed fields.

ShowFields, calculations, and criteria

  • When defining a field in a table, choose the smallest data type appropriate for the data in the field. Also, give fields you'll use in joins the same or compatible data types, such as AutoNumber and Number (if the FieldSize property is set to Long Integer).
  • When creating a query, add only the fields you need. In fields used to set criteria, clear the Show check box if you don't want to display those fields.
  • If you use criteria to restrict the values in a field used in a join between tables with a one-to-many relationship, test whether the query runs faster with the criteria placed on the "one" side or the "many" side of the join. In some queries, you get faster performance by adding the criteria to the field on the "one" side of the join instead of the "many" side.
  • Avoid calculated fields in subqueries. If you add a query that contains a calculated field to another query, the expression in the calculated field may slow performance in the top-level query. In the example below, query Q1 is used as the input for query Q2:
    Q1: SELECT IIF([MyColumn]="Yes","Order Confirmed","Order Not Confirmed") AS X FROM MyTable;
    Q2: SELECT * FROM Q1 WHERE X="Order Confirmed";
    								

    Because the IIf expression in Q1 cannot be optimized, Q2 also cannot be optimized. If an expression that can't be optimized is nested within a subquery, the entire query cannot be optimized.

    An alternative way to construct the query is as follows:

    Q1: SELECT * FROM MyTable WHERE MyColumn = "Yes";
    								
  • If expressions are necessary in the output, try to place them in a control on a form or report. For example, you could change the previous query into a parameter query that prompts for the value of MyColumn, and then base a form or report on the query. On the form or report, you could then add a calculated control that displays "Order Confirmed" or "Order Not Confirmed" depending on the value in MyColumn.

    Construct the query as follows:

    PARAMETERS [To see confirmed orders, enter Yes. To see unconfirmed orders, enter No.] Text;
    SELECT *
    FROM MyTable
    WHERE MyColumn = [To see confirmed orders, enter Yes. To see unconfirmed orders, enter No.];

    In the calculated control on the form or report, enter:

    =IIF([MyColumn]="Yes","Order Confirmed","Order Not Confirmed")

  • When grouping records by the values in a joined field, specify Group By for the field that's in the same table as the field you're totaling (calculating an aggregate on). For example, if you create a query that totals the Quantity field in the Order Details table and groups by OrderID, it's recommended that you specify Group By for the OrderID field in the Order Details table. If you specify Group By for the OrderID field in the Orders table, Microsoft Access must join all the records first and then perform the aggregate, instead of performing the aggregate and then joining only the necessary fields.

    For greater speed, use Group By on as few fields as possible. As an alternative, use the First function where appropriate.

    If a totals query includes a join, consider grouping the records in one query and adding this query to a separate query that will perform the join. This improves performance in some queries.

  • Avoid restrictive query criteria on calculated and nonindexed fields whenever possible.

ShowExpressions and Rushmore optimization

ShowOverview of Rushmore optimization

Microsoft Access can optimize simple expressions or complex expressions in the Criteria row of the query design grid, or in a WHERE clause in an SQL SELECT statement. For certain types of complex expressions, Access can use Rushmore, a data access technology used by the Microsoft Jet database engine, to achieve a greater level of optimization. In addition, the Count function is highly optimized for queries using Rushmore.

Rushmore queries will work with Access tables, as well as with Microsoft FoxPro and dBASE tables (.dbf files). You can't use Rushmore with ODBC data sources, since Access sends these queries to the ODBC data source instead of processing them locally.

ShowSimple optimizable expressions

A simple optimizable expression can form an entire expression or can appear as part of an expression. A simple optimizable expression takes one of the following forms:

IndexedField ComparisonOperator Expression

[Order Date] = #09/15/96#
								

or

Expression ComparisonOperator IndexedField

#11/1/96# < [HireDate]
								

In a simple optimizable expression:

  • IndexedField can either be a field that has its own index or a field that's the first field in a multiple-field index.

  • ComparisonOperator must be one of the following: <, >, =, <=, >=, <>, Between…And, Like, In.

  • Expression can be any valid expression, including constants, functions, and fields from other tables.

If you have created indexes for the LastName, Age, and HireDate fields in the Employees table, the following are simple optimizable expressions:

[LastName] = "Smith"

[Age] >= 21

#12/30/90# < [HireDate]

Employees.[LastName] = Customers.[LastName]

[LastName] In ("Smith", "Johnson", "Jones")

[Age] Between 18 And 65

ShowComplex expressions

A complex expression is created by combining two simple expressions with the And or the Or operator. A complex expression takes one of the following forms:

SimpleExpression And SimpleExpression

or

SimpleExpression Or SimpleExpression

A complex expression is fully or partially optimizable depending on whether one or both simple expressions are optimizable, and which operator you used to combine them. A complex expression is Rushmore-optimizable if all three of the following are true:

  • The expression uses And or Or to join two criteria.

  • Both criteria are made up of simple optimizable expressions.

  • Both expressions contain indexed fields. The fields can be indexed individually or they can be part of a multiple-field index.

Note  You can optimize multiple-field indexes if you query the indexed fields in the order they appear in the Indexes window, beginning with the first indexed field and continuing with adjacent fields (up to and including 10 fields). For example, if you have a multiple-field index that includes the LastName and FirstName fields, you can optimize a query on LastName or on LastName and FirstName, but you can't optimize a query on FirstName.

The following different combinations of simple expressions in query criteria indicate the level of query optimization.

Fully optimizable query result (using Rushmore)

  • (optimizable expression) And (optimizable expression)

  • (optimizable expression) Or (optimizable expression)

Partially optimizable query result (doesn't use Rushmore)

  • (optimizable expression) And (not optimizable expression)

Not optimizable query result

  • (not optimizable expression) And (not optimizable expression)

  • (not optimizable expression) Or (not optimizable expression)

  • Not (optimizable expression)

  • Not (not optimizable expression)

You can also use parentheses to group combinations of simple expressions. The preceding rules also apply to combinations of expressions grouped with parentheses.

Once you've combined simple optimizable expressions into complex expressions, these complex expressions can, in turn, be combined to form even more complex expressions that might be optimizable according to the preceding rules.

These examples assume that you created indexes for the LastName and HireDate fields (simple expressions using them are optimizable) but not the MiddleInitial or FirstName fields (simple expressions using them are not optimizable). The following examples of combined simple expressions show the extent to which the result is optimized.

Fully optimizable query result (using Rushmore)

[LastName] = "Smith" And [Hire Date] < #12/30/90#
						
[LastName] = "Smith" Or [Hire Date] < #12/30/90#
						

Partially optimizable query result (doesn't use Rushmore)

[LastName] = "Smith" And [MiddleInitial] = "C"
						
[FirstName] = "Terry" And [MiddleInitial] = "C"
						

Not optimizable query result

[LastName] = "Smith" Or [MiddleInitial] = "C"
						
[FirstName] = "Terry" Or [MiddleInitial]= "C"
						

ShowGeneral suggestions

  • If the RecordSource property for a form or report is set to an SQL statement, save the SQL statement as a query, and then set the RecordSource property to the name of the query.
  • For bulk update queries against ODBC data sources, optimize performance on the server by setting the FailOnError Property to Yes.
  • If your data doesn't change often, use make-table queries to create tables from your query results. Use the resulting tables rather than queries as the basis for your forms, reports, or other queries, and make sure you add indexes according to the guidelines recommended here.
  • Avoid using domain aggregate functions, such as the DLookup function, to access data from a table that's not in the query. Domain aggregate functions are specific to Microsoft Access, which means that the Microsoft Jet database engine can't optimize queries that use them. Instead, add to the query the table that the function was accessing, or create a subquery.
  • If you are creating a crosstab query, use fixed column headings whenever possible.

ShowImprove filter performance

If the lists in fields in the Filter By Form window take too long to display or they aren't displaying values from the underlying table, you can change Filter By Form performance for all tables, queries, and forms, or change performance for a specific form. In either case, you can prevent the lists from displaying the underlying table's field values, display field values on the list for certain types of indexed or nonindexed fields only, or change the record limit that determines if the list displays a field's values.

ShowImprove Filter By Form performance for all tables, queries, and forms in the database

These settings affect all tables and queries, and 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 you want to achieve.
    • If the list of values takes too long to display in nonindexed fields only, try limiting the lists to indexed fields. You can do this by clearing the Local Nonindexed Fields and ODBC Fields check boxes. If the lists take too long to display in indexed fields, clear the Local Indexed Fields check box as well since there are too many records in the indexes for the lists to display quickly.

      Note  If you use the same nonindexed field repeatedly to filter records, consider indexing it to improve filtering and other search operations on the field.

    • If lists aren't displaying the values from indexed or nonindexed fields, check under Show List Of Values In to make sure 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 records in any nonindexed field in the underlying table.

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

  1. Open a form in Design view.
  2. Display the property sheet for any text box control.

    ShowHow?

    1. Open a table, query, form, report, or data access page in Design view.
    2. Open the property sheet by doing one of the following:

      Form or report selector

      Callout 1 Form or report selector

      • On a data access page, click Select Page on the Edit menu.
      • On a table or query, click Properties Button image on the toolbar.
      • For a section on a form or report, double-click the section selector.

      Section selector

      Callout 1 Section selector

      • For a section on a data access page, double-click the section bar.

      Section bar in a data access page

      Callout 1 Section bar

      • To set properties of a query field, click the cell in the Field row, and then click Properties Button image on the toolbar.
      • To set properties of a query field list, click anywhere in the list, and then click Properties Button image on the toolbar.
      • To set properties for the body of a data access page, click above the first section, and then click Properties Button image on the toolbar.
      • To set properties for a control, click the control, and then click Properties Button image on the toolbar.
    3. In the property sheet, click the property you want to set, and then do one of the following:
      • If an arrow appears in the property box, click it and then select a value from the list.
      • Type a setting or expression in the property box.
      • If a Build button Button image appears next to the property box, click it to display a builder or list of builders.

    ShowTips

    • To get Help on any property in the property sheet, click the property and then press F1.

    • If you need more space to enter or edit a property setting, press SHIFT+F2 to open the Zoom box.

    Note  If the AllowDesignChanges property of a form is set to All Views, you can also set form properties in Form view and Datasheet view.

  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, 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 isn't displaying the values stored in that field, 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).

ShowImprove find and replace performance

An index helps Microsoft Access find and sort records faster. Access uses indexes in a table as you use an index in a book: to find data, it looks up the location of the data in the index. You can create indexes based on a single field or on multiple fields. Multiple-field indexes enable you to distinguish between records in which the first field may have the same value.

Deciding which fields to index

You'll probably want to index fields you search frequently, fields you sort, or fields that you join to fields in other tables in queries. However, indexes can slow down some action queries such as append queries, when the indexes for many fields need to be updated while performing these operations.

The primary key of a table is automatically indexed, and you can't index a field whose data type is OLE Object. For other fields, you should consider indexing a field if all the following apply:

  • The field's data type is Text, Number, Currency, or Date/Time.
  • You anticipate searching for values stored in the field.
  • You anticipate sorting values in the field.
  • You anticipate storing many different values in the field. If many of the values in the field are the same, the index may not significantly speed up queries.

Multiple-field indexes

If you think you'll often search or sort by two or more fields at a time, you can create an index for that combination of fields. For example, if you often set criteria for LastName and FirstName fields in the same query, it makes sense to create a multiple-field index on both fields.

When you sort a table by a multiple-field index, Microsoft Access sorts first by the first field defined for the index. If there are records with duplicate values in the first field, Microsoft Access sorts next by the second field defined for the index, and so on.

You can include up to 10 fields in a multiple-field index.

ShowImprove form and subform performance

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

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

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

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

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

ShowImprove list box and combo box performance

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

ShowImprove report and subreport performance

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

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

ShowTips for reports that contain pictures and objects

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

ShowImprove data access page performance

You can do several things to make your data access pages load faster in Page view or in Microsoft Internet Explorer 5.01 with Service Pack 2 (SP2) or later.

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

ShowControls

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

ShowPictures and objects

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

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

ShowRead about making your database an MDE file to improve performance

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

Security  Saving your database as an MDE file helps protect your forms and reports without requiring users to log on or requiring you to create and manage the user accounts and permissions that are needed for user-level security.

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

Saving your Access database as an MDE 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 MDE file contains no source code.
  • Importing or exporting forms, reports, or modules. However, tables, queries, data access pages, and macros can be imported from or exported to non-MDE databases.

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

Be sure to keep a copy of your original Access database. If you need to modify the design of forms, reports, or modules in an MDE file, you must modify the original Access database, and then resave it as an MDE file. Saving an Access database containing tables as an MDE file creates complications reconciling different versions of the data if you need to modify the design of the forms, reports, or modules later. For this reason, saving an Access database as an MDE file is most appropriate for the front-end database of a front-end/back-end application.

ShowCode no longer employs user-level security

In previous versions of Access, user-level security could be used with VBA modules. In Microsoft Access 2000 and later, all Visual Basic code for an Access database (.mdb) file or an Access project (.adp) file, including stand-alone modules and class modules (such as code behind forms and reports) must enable the security features by setting a password, or by saving the database as an MDE or ADE file, which removes the source code.

ShowConsideration before saving your database as an MDE file

Some restrictions may prevent you from saving your Access database as an MDE file:

  • You must have password access to the Visual Basic code.
  • If your database is replicated, you must first remove replication.
  • If your Access database references another Access database, or add-in, you must save all Access databases or add-ins in the chain of references as MDE files.

Additionally, if you define a database password or user-level security before saving an Access database as an MDE file, those features will still apply to an MDE file created from that database. If your Access database has a database password or user-level security defined and you want to remove these features, you must do so before saving it as an MDE file.

To save an Access database that has engaged user-level security as an MDE file, you must meet the following requirements before you can proceed:

  • You must join the workgroup information file that defines the user accounts used to access the database, or that were in use when the database was created.
  • Your user account must have Open/Run and Open Exclusive permissions for the database.
  • Your user account must have Modify Design or Administer permissions for any tables in the database, or you must be the owner of any tables in the database.
  • Your user account must have Read Design permissions for all objects in the database.

ShowAbout references and MDE files

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

For example, if Database1.mdb references Database2.mdb, which references Database3.mda, you would proceed as follows:

  1. Save Database3.mda as Database3.mde.
  2. Open Database 2.mdb and change its reference to point to the new Database3.mde.
  3. Save Database2.mdb as Database2.mde.
  4. Open Database1.mdb and change its reference to point to the new Database2.mde.
  5. Save Database1.mdb as Database1.mde.

ShowAbout saving a replicated database as an MDE file

A replicated database (either a replica or Design Master) cannot be saved as an MDE file. To save a replicated database as an MDE file, you must first remove replication.

Once a database is saved as an MDE file, it can be replicated; however, replication is only recommended in situations where no further changes need to be made to the original database. There is no way to make changes to the design of forms, reports, or code in the Design Master MDE file, and design changes made in the original database can't be synchronized with pre-existing MDE replicas. For this reason, if you need to make a design change to a replica set of MDE files, you must make that change in the original database, resave it as an MDE file, and then create and distribute an entirely new replica set from the new MDE file.

ShowRead about compacting an Access database

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

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

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

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