Improve performance of an Access database

Depending on your computer's configuration and your working environment, there are several things you can do that may improve the performance of Microsoft Access or your Access database (database: A collection of data related to a particular subject or purpose. Within a database, information about a particular entity, such as an employee or order, is categorized into tables, records, and fields.).

If you are optimizing the performance of an Access database, the best place to start is with the Performance Analyzer. You can use the Performance Analyzer to analyze a whole database or just selected objects in a database. The Performance Analyzer can also make some proposed changes for you if you want.

ShowRun the Performance Analyzer

You can use the Performance Analyzer to optimize the performance of an Access database. The Performance Analyzer is not available in an Access project (Microsoft Access project: An Access file that connects to a Microsoft SQL Server database and is used to create client/server applications. A project file doesn't contain any data or data-definition-based objects, such as tables and views.).

  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 (database objects: An Access database contains objects such as tables, queries, forms, reports, pages, macros, and modules. An Access project contains objects such as forms, reports, pages, macros, and modules.) 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.

  1. 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 Access itself or the system it is running on.

ShowImprove the performance of Access and your system

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

  • If you're using databases that other users don't need to share, install 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 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 MB 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

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 (link (tables): An action that establishes a connection to data from another application so that you can view and edit the data in both the original application and in Access.) a table only in an Access database, not an Access project (Microsoft Access project: An Access file that connects to a Microsoft SQL Server database and is used to create client/server applications. A project file doesn't contain any data or data-definition-based objects, such as tables and views.).

Although you can use linked tables in other files or in an SQL database as if they were tables in your Access database, it's important to keep in mind that they aren't physically in your Access database. Each time you view data in a linked table, 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 (SQL database: A database that is based on Structured Query Language (SQL).).

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 from 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, 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 (domain aggregate function: A function, such as DAvg or DMax, that is used to calculate statistics over a set of records (a domain).), such as Dsum, anywhere in your queries. When you use a domain aggregate function, 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, Access won't display any existing records. This property setting saves time, because 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 an Access database is on a network, avoid locking records longer than necessary.

ShowImprove performance of tables linked to tables in a SQL Server database

If the data in your front-end/back-end application (front-end/back-end application: A database application consisting of a "back-end" database file that contains tables, and copies of a "front-end" database file that contain all other database objects with links to the "back-end" tables.) consists only of Microsoft SQL Server tables, you can use either an Access project or an Access database as the front end for your application. However, by using an Access project, you will benefit from the advantages of using projects, such as allowing a larger number of tables and a larger database size. If you want the data in your front-end/back-end application to consist of both 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 (link (tables): An action that establishes a connection to data from another application so that you can view and edit the data in both the original application and in Access.) to the SQL tables, instead of opening the tables directly. You can open external SQL tables directly only by using Microsoft Visual Basic for Applications (VBA) code. Linked tables are considerably faster, more powerful, and more efficient than directly-opened tables.

Additional performance tips

ShowImprove performance in a multiuser environment

The following guidelines can help you optimize the performance of 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 (workgroup information file: A file that Access reads at startup that contains information about the users in a workgroup. This information includes users' account names, their passwords, and the groups of which they are members.) from Access 97 or earlier to the current version of Access.

ShowImprove query performance

ShowDatabases, tables, and indexes

The Jet database engine automatically optimizes a query that joins an Access table on your hard drive and an ODBC (Open Database Connectivity (ODBC): A standard method of sharing data between databases and programs. ODBC drivers use the standard Structured Query Language (SQL) to gain access to external data.) 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

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";
                        

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 (aggregate function: A function, such as Sum, Count, Avg, or Var, that you use to calculate totals.) 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, 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.

ShowExpressions and Rushmore optimization

ShowOverview of Rushmore optimization

Access can optimize simple expressions (expression: Any combination of mathematical or logical operators, constants, functions, and names of fields, controls, and properties that evaluates to a single value. Expressions can perform calculations, manipulate characters, or test data.) or complex expressions in the Criteria row of the query design grid (design grid: The grid that you use to design a query or filter in query Design view or in the Advanced Filter/Sort window. For queries, this grid was formerly known as the QBE 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 (Microsoft Jet database engine: The part of the Access database system that retrieves and stores data in user and system databases. It can be thought of as a data manager upon which database systems, such as Access, are built.), 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 (ODBC data source: Data and the information needed to access that data from programs and databases that support the Open Database Connectivity (ODBC) protocol.), since Access sends these queries to the ODBC data source instead of processing them locally.

ShowSimple optimizable expressions

A simple optimizable expression (expression: Any combination of mathematical or logical operators, constants, functions, and names of fields, controls, and properties that evaluates to a single value. Expressions can perform calculations, manipulate characters, or test data.) 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:

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 (expression: Any combination of mathematical or logical operators, constants, functions, and names of fields, controls, and properties that evaluates to a single value. Expressions can perform calculations, manipulate characters, or test data.) 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:

 Note   You can optimize multiple-field indexes if you query the indexed fields in the order in which they appear in the Indexes window (Indexes window: In an Access database, a window in which you can view or edit a table's indexes or create multiple-field indexes.), 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

ShowImprove filter performance

If the lists in fields in the Filter By Form (Filter By Form: A technique for filtering data that uses a version of the current form or datasheet with empty fields in which you can type the values that you want the filtered records to contain.) 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 (index: A feature that speeds up searching and sorting in a table based on key values and can enforce uniqueness on the rows in a table. The primary key of a table is automatically indexed. Some fields can't be indexed because of their data type.) 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 (control: A graphical user interface object, such as a text box, check box, scroll bar, or command button, that lets users control the program. You use controls to display data or choices, perform an action, or make the user interface easier to read.) 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 (control: A graphical user interface object, such as a text box, check box, scroll bar, or command button, that lets users control the program. You use controls to display data or choices, perform an action, or make the user interface easier to read.) 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 (Design view: A view that shows the design of these database objects: tables, queries, forms, reports, and macros. In Design view, you can create new database objects and modify the design of existing objects.).
  2. Display the property sheet for any text box control.

ShowHow?

  1. Open a table, query, form, report, or data access page (data access page: A Web page, published from Access, that has a connection to a database. In a data access page, you can view, add to, edit, and manipulate the data stored in the database. A page can also include data from other sources, such as Excel.) in Design view (Design view: A view that shows the design of these database objects: tables, queries, forms, reports, and macros. In Design view, you can create new database objects and modify the design of existing objects.).
  2. Open the property sheet by doing one of the following:
  3. In the property sheet, click the property you want to set, and then do one of the following:

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 (Form view: A view that displays a form to show or accept data. Form view is the primary means of adding and modifying data in tables. You can also change the design of a form in this view.) and Datasheet view (Datasheet view: A view that displays data from a table, form, query, view, or stored procedure in a row-and-column format. In Datasheet view, you can edit fields, add and delete data, and search for data.).

  1. 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 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 (join: An association between a field in one table or query and a field of the same data type in another table or query. Joins tell the program how data is related. Records that don't match may be included or excluded, depending on the type of join.) to fields in other tables in queries (query: A question about the data stored in your tables, or a request to perform an action on the data. A query can bring together data from multiple tables to serve as the source of data for a form or report.). However, indexes can slow down some action queries (action query: A query that copies or changes data. Action queries include append, delete, make-table, and update queries. They are identified by an exclamation point (!) next to their names in the Navigation Pane.) such as append queries (append query: An action query that adds the records in a query's result set to the end of an existing table.), when the indexes for many fields need to be updated while performing these operations.

The primary key (primary key: One or more fields (columns) whose values uniquely identify each record in a table. A primary key cannot allow Null values and must always have a unique index. A primary key is used to relate a table to foreign keys in other tables.) of a table is automatically indexed, and you can't index a field whose data type (data type: The characteristic of a field that determines what type of data it can hold. Data types include Boolean, Integer, Long, Currency, Single, Double, Date, String, and Variant (default).) is OLE Object (OLE Object data type: A field data type that you use for objects created in other applications that can be linked or embedded (inserted) in an Access database.). 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, Access sorts first by the first field defined for the index. If there are records with duplicate values in the first field, 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

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 (form module: A module that includes Visual Basic for Applications (VBA) code for all event procedures triggered by events occurring on a specific form or its controls.) loads more quickly and occupies less disk space. If a form or controls on the form don't use event procedures (event procedure: A procedure that is automatically executed in response to an event initiated by the user or program code, or that is triggered by the system.), the form doesn't require a form module.

Notes

ShowTips for subforms

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 (drop-down list box: A control on a data access page that, when clicked, displays a list from which you can select a value. You cannot type a value in a drop-down list box.) run faster:

 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

ShowTips for reports that contain pictures and objects

ShowTips for subreports

ShowImprove data access page performance

You can do several things to make your data access pages load faster in Page view (Page view: An Access window in which you can browse the contents of a data access page. Pages have the same functionality in Page view as in Microsoft Internet Explorer 5.0 or later.) or in Microsoft Internet Explorer 5.01 with Service Pack 2 (SP2) or later.

ShowGeneral tips

  • When using a data access page within 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 Data Entry 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. 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) (Visual Basic for Applications (VBA): A macro-language version of Microsoft Visual Basic that is used to program Windows applications and is included with several Microsoft applications.) code, saving your Access database as an MDE file (MDE file: A Microsoft Access 2003 or earlier database (.mdb) file with all modules compiled and all editable source code removed.) compiles all modules, removes all editable source code, and compacts the destination database. Your VBA code will continue to run, but it cannot be viewed or edited.

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 does not prevent changes to the database design. However, you will notice the following:

  • The user interface for modifying or creating forms, reports, or modules will be disabled.
  • The VBA References dialog box will not allow adding, deleting, or changing references to object libraries or databases.
  • The source code will not be available.
  • The importing and exporting commands will be disabled for 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 (front-end/back-end application: A database application consisting of a "back-end" database file that contains tables, and copies of a "front-end" database file that contain all other database objects with links to the "back-end" tables.).

ShowCode no longer employs user-level security

ShowConsideration before saving your database as an MDE file

Some restrictions may prevent you from saving your Access database as an MDE file (MDE file: A Microsoft Access 2003 or earlier database (.mdb) file with all modules compiled and all editable source code removed.):

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:

ShowAbout references and MDE files

If you try to create an MDE file from an Access database (.mdb) or an add-in (.mda) (add-in: A supplemental program that adds custom commands or custom features to Microsoft Office.) 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 (replica: A copy of a database that is a member of a replica set and can be synchronized with other replicas in the set. Changes to the data in a replicated table in one replica are sent and applied to the other replicas.) or Design Master (Design Master: The only member of the replica set in which you can make changes to the database structure that can be propagated to other replicas.)) cannot be saved as an MDE file. To save a replicated database as an MDE file, you must first remove replication (replication: The process of copying a database so that two or more copies can exchange updates of data or replicated objects. This exchange is called synchronization.).

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. If you need to make a design change to a replica set (replica set: The Design Master and all replicas that share the same database design and unique replica set identifier.) 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

Access combines compacting and repairing an Access file (Microsoft Access file: A database or project file. In Access 2007, database objects and data are stored in .accdb files. Earlier versions use.mdb files. An Access project file doesn't contain data, and is used to connect to a SQL Server database.) into a single process.

ShowCompacting an Access file

If you delete data or objects in an Access database (Microsoft Access database: A collection of data and objects (such as tables, queries, or forms) that is related to a particular topic or purpose.), or if you delete objects in an Access project (Microsoft Access project: An Access file that connects to a Microsoft SQL Server database and is used to create client/server applications. A project file doesn't contain any data or data-definition-based objects, such as tables and views.), 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 (database objects: An Access database contains objects such as tables, queries, forms, reports, pages, macros, and modules. An Access project contains objects such as forms, reports, pages, macros, and modules.) — such as tables or views — that reside in the Microsoft SQL Server database (Microsoft SQL Server database: A database that consists of tables, views, indexes, stored procedures, functions, and triggers. You can connect your database to SQL Server data by using ODBC or by creating an Access project (*.adp) file.) 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 (AutoNumber data type: In a Microsoft Access database, a field data type that automatically stores a unique number for each record as it's added to a table. Three kinds of numbers can be generated: sequential, random, and Replication ID.), 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, 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:

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

 
 
Applies to:
Access 2003