ACC2002: PivotChart and PivotTable List Lose Formatting in Replicas (282264)


The information in this article applies to:

  • Microsoft Access 2002
This article was previously published under Q282264
Advanced: Requires expert coding, interoperability, and multiuser skills.

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

SYMPTOMS

In a replicated database, you notice that queries in which you designed PivotChart or PivotTable lists, the PivotChart or PivotTable list has been reset to blank. It does not matter whether the replica is a new one created from the Design Master or one that has been freshly synchronized with the Design Master. However, in the Design Master, any PivotChart or Pivot Table list still appears as expected.

CAUSE

PivotChart and PivotTable information for queries does not replicate between databases. If PivotChart and PivotTable formatting were replicable, you could potentially experience conflicts. In order to prevent these conflicts, the system table MSYSACCESSXML is not replicable.

RESOLUTION

PivotChart and PivotTable formatting for forms, however, can replicate between databases. As a workaround, you can create a form that is based on the query, set the default view of the form to PivotTable or PivotChart, design the PivotChart and PivotTable list as desired, and then synchronize. The steps below detail this process.
  1. Open the Design Master.
  2. Create a new form that is based on the query that you want to make a PivotChart or a PivotTable list from.
  3. In Design view of the form, click Properties on the View menu.
  4. On the Format tab, change the DefaultView property to PivotTable or PivotChart.
  5. Save the form.
  6. On the View menu, click PivotChart View or PivotTable View.
  7. Once in PivotChart or PivotTable view, you can implement the same design that you had originally in the query.
  8. When you are finished, save the form.
  9. Open one of the replica databases.
  10. On the Tools menu, point to Replication, and then click Synchronize Now. Note that the new form appears in the replica.
  11. Open the form. Note that the PivotChart or PivotTable list is formatted as expected.

MORE INFORMATION

Steps to Reproduce the Behavior

  1. Create a new database, and save it as Test.mdb.
  2. On the File menu in Test.mdb, point to Get External Data, and then click Import.
  3. In the Import dialog box, browse to the folder that contains the Northwind.mdb sample database. Its default location is: \Program Files\Microsoft Office\Office10\Samples.
  4. Click the Northwind sample database, and then click Import.
  5. On the Tables tab of the Import Objects dialog box, click OrderDetails, and then click Import.
  6. On the Insert menu, click Query. In the New Query dialog box, click Simple Query Wizard, and then click OK.
  7. The Simple Query Wizard should already have the OrderDetails query chosen. Add all fields, and then click Finish. Note that the new query appears in DataSheet view.
  8. On the View menu, click Design.
  9. In Design view of the query, double-click in the blank area of the Table pane to open the Query Properties dialog box.
  10. Change the DefaultView property to PivotTable.
  11. Save the Query.
  12. On the View menu, click PivotTable View.
  13. From the field list, drag OrderID to the Column Fields area, drag UnitPrice to the Data Fields area, and drag Quantity to the Row Fields area.
  14. When you are finished, save the query, and then close it.
  15. On the Tools menu, point to Replication, and then click Create Replica.
  16. Click Yes in the "Database must be closed" message. Click No in the next message that asks if you want to create a backup.
  17. In the Location of New Replica dialog box, accept the name Replica of Test, and then click OK.
  18. The next informational message indicates the location of the replica. Make note of this location.
  19. At this point, Test is a Design Master and is currently open. Note that if you open the OrderDetails query in the Design Master, the PivotTable list is unchanged.
  20. On the File menu, click Open. Open the Replica of Test database.
  21. Open the OrderDetails query. Note that the PivotTable list is reset to blank.
  22. Close the query.
  23. On the Tools menu, point to Replication, and then click Synchronize Now.
  24. After the synchronization is finished, reopen the OrderDetails query. Note that the PivotTable list is still blank.
Modification Type:MajorLast Reviewed:9/25/2003
Keywords:kbprb KB282264
©2003 Microsoft Corporation. All rights reserved.