Datensatzherkunft eines Access 2010 PivotChart-Forms ändern

by Philipp Stiefel, ursprünglich veröffentlicht 27. Juni 2017

Kürzlich stellte jemand im Access-O-Mania-Forum eine Frage zu einem Access 2010 Formular in der PivotChart-Ansicht. Er wollte das gleiche Diagramm für verschiedene Geschäftsbereiche darstellen. Um das zu erreichen wollte er die Datenquelle des PivotChart-Formulars zur Laufzeit mit VBA ändern. Ein wichtiges Detail der Frage war, dass es sich bei der Anwendung um ein ADP-Projekt mit SQL-Server-Daten handelte.

Für mich hatte diese Frage mehrere interessante Aspekte. Der erste war, dass dies tatsächlich ein kniffliges Problem ist. Weiterhin hat eine kurze Recherche ergeben, dass diese Frage einige Male in verschiedenen VBA-, Access-, und Excel-Foren aufgekommen ist, aber niemals irgendwo vollständig geklärt wurde. Es kam noch hinzu, dass scheinbar keiner der Regulars im Access-O-Mania-Forum tiefgehende Kenntnisse zu der PivotChart-Ansicht von Access Formularen hatte.

Dies war Grund genug diese Angelegenheit etwas tiefergehend zu untersuchen. Da allerdings die verwendete Technologie überholt ist und eher selten verwendet wurde, werde ich hier nur ein paar Lösungsansätze für das konkrete Problem skizzieren und keinen vollständige Abhandlung zu dem Thema schreiben.

Access 2010 Formulare in der PivotChart-Ansicht  

In Access 2007 und Access 2010 stellte Microsoft eine neue PivotTable- und PivotChart-Ansicht für Access Formulare vor. Besonders die PivotChart-Ansicht war eine einfache Option um Daten in einer Access-Anwendung visuell darzustellen ohne dafür externe Komponenten verwenden zu müssen.

In vielen Anwendungsbereichen ist es ein zusätzlicher Vorteil, dass die Daten einfach für Drilldown-Operationen gefiltert werden können. Weiterhin können Benutzer relativ einfach die Datenfelder der Diagrammachsen, der Datenserien und sogar den Diagrammtyp ändern.

Auf der Office-Support-Website gibt es ein Video, das die PivotTable- und PivotChart-Ansicht in Access 2010 demonstriert.

Allerdings hat Microsoft entschieden, diese Funktionalität mit Access 2013 wieder zu entfernen. Dies wurde eher selten verwendet und selbst wenn du heute noch Access 2010 verwendest, rate ich davon ab, dieses Feature noch für eine umfangreichere Neuentwicklung einzusetzen, da es keine direkte Anschluss an diese Technologie in neueren Versionen von Access gibt.

Das Problem die Datensatzherkunft zu ändern

Wie oben erwähnt, ist es für erfahrene Benutzer einfach, die Daten in einem Diagramm zu filtern. Dies mag in vielen Fällen ausreichend sein. Wenn du aber komplexere Filterbedingungen auf die Daten anwenden willst, wirst du wahrscheinlich auf VBA- und SQL-Programmierung zurückgreifen wollen, anstatt den Benutzer diese Filter setzen zu lassen. - Genau hier wird es aber problematisch.

Normalerweise kannst du einfach in VBA die Datensatzherkunft eines Formulars ändern, indem du die RecordSource-Eigenschaft des Formulars setzt. Du weist dieser Eigenschaft eine neue Tabelle, Abfrage oder ein SQL-Statement zu. Solange die Feldnamen in der neuen Datenquelle mit denen der alten übereinstimmen, werden die neuen Daten sofort im Formular angezeigt. - Trivial.

Mit einem Formular in der PivotChart-Ansicht ist dies leider nicht so trivial. Immer wenn du die RecordSource-Eigenschaft des Formulars änderst, werden alle Datenfelder-Eigenschaften des PivotCharts zurückgesetzt. Das Chart verliert Kategorie-, Serien-, Daten- und Filter-Bindungen an die Datenfelder.

Dabei ist es irrelevant wann die RecordSource-Eigenschaft geändert wird. Selbst wenn du die RecordSource mit VBA-Code im Form_Load- oder Form_Open-Ereignis änderst, wird das Chart resetted.

Lösung 1 - Parameterabfragen / Gespeicherte Prozeduren

Wenn du dieselbe Abfrage mit anderen Parametern als Filter für die Daten verwenden willst, gibt es eine relativ einfache Lösung für das Problem. In einer Access-ACCDB-Datenbank kannst du eine Parameterabfrage verwenden, die die Parameterwerte beim Öffnen des Pivot-Formulars aus Steuerelementen in der Access-Oberfläche oder aus VBA-Funktionen abruft. Dies ist Standardvorgehen bei der Access-Entwicklung und ich werde es hier nicht im Detail beschreiben.

Allerdings handelte es sich bei der Anwendung aus der ursprünglichen Frage um eine ADP-Anwendung mit SQL-Server-Daten. Dort gibt es keine lokalen Abfragen und Sichten (Views) und Gespeicherte Prozeduren (Stored Procedures) auf dem SQL-Server können weder auf Steuerelemente in der Access-Oberfläche noch auf VBA-Funktionen zugreifen.

Ein vergleichbarer Ansatz in einem ADP ist es eine Stored Procedure in Verbindung mit der nicht so besonders bekannten InputParameters-Eigenschaft des Formulars zu verwenden. Wenn du jedoch die InputParameters-Eigenschaft direkt mit VBA änderst, hat dies denselben Effekt wie die RecordSource des Formulars zu ändern und die Einstellungen des Charts werden zurückgesetzt. Aber in der InputParameters-Eigenschaft kann man auf Steuerelemente in der Oberfläche und auf benutzerdefinierte VBA-Funktionen verweisen.

Die grundsätzliche Syntax für InputParameters ist:

@FirstParameterName paramDataType = paramValue, @SecondParameterName paramDataType = paramValue

Hier sind ein paar Beispiele für die Verwendung von Input Parameters.

• Einen statischen Paramterwert vorgeben:

  @anIntParam int = 999

• Auf ein Steuerelement auf einem Access Formular verweisen:

  @anIntParam int=forms!frmYourParameterForm!ParamValueControl

• Eine öffentliche VBA-Funktion referenzieren:

  @anIntParam int = YourFunctionName()

Hier noch zwei Beispiel-Screenshots einer TSQL-Prozedur und den korrespondierenden Einstellungen in den Eigenschaften eines Formulars.

Mit diesem Ansatz sollten sich die meisten Vorkommen von dieser Problemsituation lösen lassen. Du solltest dir bewusst sein, dass sowohl die Parameter einer lokalen Access-Abfrage als auch die InputParameters für eine Gespeicherte Prozedur nur einmalig beim Öffnen des Formulars ausgewertet werden. Es gib keinen (einfachen) Weg, um die Daten eines bereits geöffneten Formulars zu aktualisieren.

Lösung 2 - Programmierung der PivotChart-Ansicht mit VBA

Die oben vorgestellte Lösung funktioniert gut, wenn die Datensatzherkunft des Formulars nicht geändert wird und nur verschiedene Parameterwerte für ein und dieselbe Abfrage verwendet werden. Das sollte in den meisten Fällen ausreichen.

Es kann aber Fälle geben, in denen es tatsächlich erforderlich ist, die RecordSource des Formulars auf eine andere Tabelle oder Abfrage zu setzen. Dann hilft der obige Ansatz nicht weiter.

Die naheliegende Lösung wäre, einfach die erforderlichen Eigenschaften, um den Chart an die Datenquelle zu binden, mit VBA zu setzen. Das Problem mit der PivotTable- und PivotChart-Ansicht ist jedoch, dass das Access Formular scheinbar keine API offenlegt um diese Eigenschaften anzusprechen. Die relevanten Eigenschaften des Access Formulars sind die ChartSpace oder die PivotTable Eigenschaften. Beide sind jedoch vom Typ Object und es gibt keine Unterstützung durch Intellisense und keine weiteren Informationen im VBA Objektkatalog.

Das ChartSpace- und das PivotTable-Objekt sind in der the Microsoft Office Web Components Bibliothek implementiert. Die Bibliotheksdatei ist die OWC11.DLL und diese ist üblicherweise in dem Ordner C:\Program Files (x86)\Common Files\Microsoft Shared\Web Components\11\ zu finden.

Du kannst einen Verweis auf diese DLL hinzufügen und kannst dann danach alle Eigenschaften und Methoden des ChartSpace-Objektes im VBA Objektkatalog sehen.

Auch wenn diese Bibliothek inzwischen veraltet ist, ist die Dokumentation der Office Web Components API weiterhin verfügbar.

Die API ermöglicht dir sehr viele Aspekte der PivotChart-Ansicht (oder PivotTable-Ansicht) mit VBA zu steuern. Unser primärer Fokus hier ist das Problem mit der Datenbindung zu lösen, daher werde ich nur die dafür relevante Methode konkret vorstellen.

Du musst keinen Verweis auf die OWC11.DLL setzen um den hier gezeigten Beispielcode zu verwenden wenn du Late Binding verwendest. Selbst dann nicht, wenn du auf Ereignisse des Charts reagieren willst, denn diese stehen auch als direkte Ereignisse des Access-Formular-Objekts zur Verfügung.

Um die Datenbindung des Chart einzustellen, müssen wir nur die SetData-Methode aufrufen. Beim Einsatz von Late Binding empfehle ich, die benötigten Enum-Werte in deinem VBA-Code neu zu deklarieren. Ich bin faul und deklariere in dem Beispielcode nur die wenigen Enum-Werte, die ich auch tatsächlich verwende.

Hier ist ein kurzes Beispiel, das illustriert, wie man einen ChartSpace mit Kategorien, Serien, und Werte konfiguriert. (ActorName, RatingYear und Rating sind Feldnamen in meiner Stored Procedure.)

Me in dem Beispielcode referenziert das Formular in der PivotChart-Ansicht.

Fazit

Die PivotChart- und PivotTable-Ansicht waren immer eine Art ungeliebtes Stiefkind von Access. Sie wurden niemals besonders beworben bevor sie dann mit Access 2013 aufgegeben wurden. Dennoch sind (waren) sie sehr nützlich zur Visualisierung von Daten.

Basierend auf den Informationen in diesem Artikel kannst du diese Ansichten mit VBA-Code steuern und damit ihrer Nützlichkeit noch weiter vergrößern. Unglücklicherweise macht das heute bestenfalls für kleinere Projekte mit wenig Programmieraufwand Sinn, da du diesen Code neu schreiben müssen wirst, wenn du auf Access 2013 und spätere Versionen umstellst.

In Access 2013 gibt es ein Microsoft Graph Chart Steuerelement, das in Access eingebaut ist, und das verwendet werden kann um Daten in einer ähnlichen Weise zu visualisieren.

Share this article: