ACC2002: Changes to a Form in PivotTable View or PivotChart View Are Not Saved in a Multi-user Environment (275106)


The information in this article applies to:

  • Microsoft Access 2002
This article was previously published under Q275106
Novice: Requires knowledge of the user interface on single-user computers.

This article applies only to a Microsoft Access database (.mdb).

SYMPTOMS

When you make changes to the PivotTable view or the PivotChart view of a form in a Microsoft Access database, the changes are not saved when you close the form.

CAUSE

The database is being used in a multi-user environment and more than one instance of the database is open. If you are the only user, then changes made to the PivotTable and PivotChart views of forms are saved as you expected.

RESOLUTION

There are two possible resolutions to this issue. Either open the database exclusively, or use the PivotTable and PivotChart views of a table or query rather than a form.

Method 1: Open the Database Exclusively

If you open the database exclusively, it prevents other users from opening it until you close the database. This will ensure that all design changes you make to the PivotTable and PivotChart views of forms will be saved automatically. To open the database exclusively, follow these steps:
  1. Start Microsoft Access. On the File menu, click Open.
  2. In the Open dialog box, select the database that you want to open exclusively, and then click the arrow to the right of the Open button.
  3. Click Open Exclusive. If the database is not in use, it opens, giving you exclusive rights to use it. All other users are unable to open the database while you have exclusive use.

    If the database is already in use, you receive the following alert:
    You can't open 'C:\MyDatabase' for exclusive use because another user has the database open or because you do not have permissions to open exclusively.

    Microsoft Access will open the database for shared access.
    If the database is already opened exclusively by another user, then Access displays the following alert:
    Could not use 'C:\MyDatabase'; file already in use.

Method 2: Use the Pivot Views of a Table or Query

Tables and queries do not fall under the exclusive lock requirement. Thus, it is possible to save changes made in the PivotTable and PivotChart views of a table or query in a multi-user environment. Note that a limitation of this approach is that tables and queries do not expose events for PivotTables or PivotCharts. To modify the PivotTable view of a table in a multi-user environment, follow these steps:
  1. Start two instances of Microsoft Access.
  2. Open the sample database Northwind.mdb in both instances.
  3. Minimize the second instance of Microsoft Access. Use the first instance for the remainder of these steps.
  4. On the View menu, point to Database Objects, and then click Tables.
  5. Select the Orders table in the Database window, and then click Open.
  6. On the View menu, click PivotTable View.
  7. In the PivotTable Field List, click and drag the CustomerID field and drop it on the Row Fields drop zone on the left side of the PivotTable.
  8. Similarly, click and drag the EmployeeID field and drop it on the Column Fields drop zone near the top of the PivotTable.
  9. To complete the PivotTable, click and drag the Freight field and drop it on the Detail Fields drop zone in the middle of the PivotTable.
  10. Close the table.
  11. Click Yes when Microsoft Access prompts you to save the changes to the table.
  12. Open the Orders table again.
  13. On the View menu, click PivotTable View. Notice that Microsoft Access successfully saved the changes you made in PivotTable view.

STATUS

This behavior is by design.

MORE INFORMATION

Microsoft Access requires an exclusive lock in order to save design changes made to forms, reports, macros, modules, or command bars. Any changes made in PivotTable or PivotChart view of a form is considered a design change, thus Microsoft Access requires that the database be opened exclusively.

For additional information about using Access databases in a multi-user environment, click the article number below to view the article in the Microsoft Knowledge Base:

283228 ACC2002: Exclusive Lock Required for Saving Design Changes to Access Objects

Steps to Reproduce the Behavior

  1. Start two instances of Microsoft Access.
  2. Open the sample database Northwind.mdb in both instances.
  3. Minimize the second instance of Microsoft Access, and use the first instance for the remainder of these steps.
  4. On the View menu, point to Database Objects, and then click Forms.
  5. Select the Orders form in the Database window, and then click Open.
  6. On the View menu, click PivotTable View.
  7. In the PivotTable Field List, click and drag the Bill To field and drop it on the Row Fields drop zone on the left side of the PivotTable.
  8. Similarly, click and drag the Salesperson field and drop it on the Column Fields drop zone near the top of the PivotTable.
  9. To complete the PivotTable, click and drag the Freight field and drop it on the Detail Fields drop zone in the middle of the PivotTable.
  10. Close the form.
  11. Open the Orders form again.
  12. On the View menu, click PivotTable View. Notice that Microsoft Access did not save any changes to the PivotTable. If the database had been opened by only one instance of Microsoft Access, the changes would have been saved automatically.
Modification Type:MinorLast Reviewed:1/26/2005
Keywords:kbnofix kbprb KB275106
©2004 Microsoft Corporation. All rights reserved.