<\/p>\n
Unter Access bietet sich das Speichern von Daten in Tabellen an. Die legen Sie in der Datenbankdatei an und greifen über gebundene Formulare, Berichte und Steuerelemente darauf zu. Unter VBA geschieht dies fast genauso bequem mithilfe von DAO- oder ADO-Recordsets. Manche Konstellationen schließen die Verwendung von gebundenen Recordsets aus und Sie müssen auf andere Strukturen zum temporären Speichern Ihrer Daten zurückgreifen. Dieser Beitrag stellt die verschiedenen Möglichkeiten und ihre Vor- und Nachteile vor.<\/b><\/p>\n
Wollen Sie nur eine einzige Information wie etwa den Namen einer Person so festhalten, dass Sie jederzeit schnell darauf zugreifen können, reicht eine simple String-Variable aus:<\/p>\n
Dim strName As String\r\nstrName = "Sascha Trowitzsch"<\/pre>\nSobald Sie mehrere Daten zu einer Einheit zusammenfassen möchten, helfen die Standarddatentypen nicht mehr weiter. Hier können Sie benutzerdefinierte Strukturen mit einem festen Aufbau verwenden, deren Definition etwa so aussieht:<\/p>\n
Type TPerson\r\nVorname As String\r\nNachname As String\r\nGeburtsdatum As Date\r\nEnd Type<\/pre>\nUm diese Struktur einzusetzen, deklarieren Sie eine Variable auf Basis der Struktur und füllen deren Element wie in den folgenden Beispielanweisungen:<\/p>\n
Dim Autor As TPerson\r\nAutor.Vorname = "Sascha"\r\nAutor.Nachname = "Trowitzsch"\r\nAutor.Geburtsdatum = "11.1.1918"<\/pre>\nDamit stoßen Sie spätestens dann an Ihre Grenzen, wenn Sie mehrere gleichartige Daten in einer Liste zusammenfassen wollen – im Beispiel etwa eine Gruppe von Personen. Dann benötigen Sie ein Datenfeld, wobei einem als Erstes das Array einfällt. Tatsächlich gibt es unter Access und VBA aber noch einige Möglichkeiten mehr:<\/p>\n
Die ersten fünf Varianten stellen wir in den folgenden Abschnitten vor, die sechste würde leider den Rahmen dieses Beitrags sprengen.<\/p>\n
ADODB-Recordsets<\/b><\/p>\n
Wenn man an ADO-Recordsets denkt, zieht man direkt Parallelen zu ihrem DAO-Pendant und damit zur Bindung an Tabellen. Im Gegensatz zu DAO benötigt ein ADO-Recordset jedoch nicht zwingend einen Bezug zu einer physischen Datenherkunft und kann als reiner Datencontainer im Speicher dienen. Solche Recordsets, in denen die Eigenschaft Connection <\/b>nicht gesetzt ist, nennt man auch ungebundene Recordsets (es gibt auch noch die Disconnected Recordsets<\/b>, die zunächst mit Daten etwa aus einer Tabelle gefüllt und dann von dieser getrennt werden – mehr dazu im Beitrag Disconnected Recordsets<\/b>, Shortlink 437).<\/p>\n
Listing 1 zeigt beispielhaft, wie Sie solch ein ungebundenes Recordset anlegen und füllen.<\/p>\n
Listing 1: Disconnected ADODB-Recordset als Variablenspeicher<\/p>\n
Sub ADODBCollection()\r\n Dim rst As ADODB.Recordset\r\n Set rst = New ADODB.Recordset\r\n rst.CursorLocation = adUseClient\r\n rst.Fields.Append "Nachname", adBSTR\r\n rst.Fields.Append "Vorname", adBSTR\r\n rst.Fields.Append "Geburtsdatum", adDate\r\n rst.Open , , adOpenKeyset\r\n rst.AddNew\r\n rst!Nachname = "Trowitzsch"\r\n rst!Vorname = "Sascha"\r\n rst!Geburtsdatum = "11.1.1918"\r\n rst.MoveFirst\r\n rst.Find "Nachname='Trowitzsch'"\r\n If Not rst.EOF Then\r\n Debug.Print rst(0), rst(1), rst(2)\r\n End If\r\n rst.Close\r\n End Sub<\/pre>\nWichtig ist hier die Open<\/b>-Methode des Recordsets, die den Connection<\/b>-Parameter auslässt, wodurch das Recordset keinerlei Bezug zu einer Tabelle der Datenbank selbst hat. Bei einem Disconnected Recordset <\/b>bindet man das Recordset zunächst an eine Tabelle und deaktiviert anschließend die Verbindung:<\/p>\n
Set rst.Connection = Nothing<\/pre>\nDanach hat man wieder ein rein speicherbasiertes Recordset, in dem Sie Daten ändern können, ohne dass sich dies auf die Daten aus der Herkunftstabelle niederschlägt.<\/p>\n
Das große Plus von ADODB ist seine hohe Flexibilität. Das Recordset lässt verschiedenste Operationen zu, wobei gerade die Suchmöglichkeiten einen wesentlichen Vorteil gegenüber den weiter unten vorgestellten Arrays und Collections ausmachen. Das hat jedoch seinen Preis: Der Verwaltungsaufwand für das Objekt und seine Schnittstelle ist hoch, was zulasten der Performance geht. Wer Millionen Berechnungen auf Basis der Elemente dieses Datencontainers ausführen möchte, wird am ADODB-Recordset keine rechte Freude haben.<\/p>\n
Zum Einsatz kommen ungebundene Recordsets als reiner Variablenspeicher daher vor allem, wenn kompliziertere Operationen ausgeführt werden sollen, die sich mit Arrays und Collections nur durch hohen Programmieraufwand realisieren ließen.<\/p>\n
TempVars<\/b><\/p>\n
Mit Access 2007 hat Microsoft einen neuen Typ von Auflistungsvariablen eingeführt: die TempVars<\/b>. Es handelt sich dabei um ein eindimensionales Array, das allerdings eher einer Collection ähnelt, weil Sie dessen Elemente über einen Key ansprechen können. Speichern können Sie in TempVars <\/b>jedoch nur Standarddatentypen, aber keine Objekte, Arrays oder benutzerdefinierte Typen.<\/p>\n
TempVars <\/b>müssen Sie nicht extra als Variable deklarieren, weil Access die Auflistung von vornherein als Eigenschaft des Application<\/b>-Objekts mitbringt. Neue Elemente fügen Sie etwa so hinzu:<\/p>\n
TempVars.Add "Nachname", "Trowitzsch"\r\nTempVars.Add "Vorname", "Sascha""\r\nTempVars.Add "Geburtsdatum", CDate("1.11.1918")\r\nTempVars("Vorname") = "Alexander" 'Inhalt ändern\r\nDebug.Print TempVars("Vorname")<\/pre>\nDas ist eine komfortable Möglichkeit, Daten schnell und ohne großen Aufwand in einem Datenfeld zu speichern. TempVars <\/b>haben zudem eine ganz besondere Eigenschaft: Sie gehen nicht verloren. Während globale Variablen beim Auftreten eines nicht behandelten Fehlers unter VBA grundsätzlich zerstört werden, bleiben TempVars <\/b>erhalten. Sie sind damit sichere Datenspeicher und wickeln ihre Geschäfte zudem recht flott ab. Die Performance der Auflistung beim Zugriff auf die enthaltenen Elemente ist sehr gut.<\/p>\n
Andererseits sind die Features nicht gerade atemberaubend. Ein Knackpunkt etwa ist die fehlende Möglichkeit, neue TempVars<\/b>-Variablen anzulegen. Man ist auf die eine eingebaute und flache Liste angewiesen. Damit eignen sich TempVars <\/b>in erster Linie zum Speichern anwendungsbezogener Daten, in denen Sie beispielsweise einzelne Einstellungen unterbringen möchten.<\/p>\n
Arrays<\/b><\/p>\n
Der klassische Variablentyp zur Speicherung mehrerer Elemente ist das Array. Es kann beliebige Datentypen aufnehmen, also auch Objekte und benutzerdefinierte Typen und arbeitet wegen seines einfachen Aufbaus außerordentlich schnell.<\/p>\n
Das Beispiel aus Listing 2 speichert mehrere der in einem früheren Beispiel angelegten Type-Strukturen in Form einer Personenliste. <\/p>\n
Listing 3: Variablen setzen mit DAO<\/p>\n
Sub SetVarDAO(ByVal strVarName As String, Wert As Variant)\r\n With rstVars\r\n .FindFirst "VarName='" & strVarName & "'"\r\n If .NoMatch Then\r\n .AddNew\r\n !VarName = strVarName\r\n Else\r\n .Edit\r\n End If\r\n !VarWert = CStr(Wert)\r\n .Update\r\n End With\r\n End Sub<\/pre>\nEinige Besonderheiten sind im Umgang mit Arrays zu beachten:<\/p>\n
\n
- Die Zahl der enthaltenen Elemente können Sie zwar schon bei der Deklaration der Variable voreinstellen (statisches Array), günstiger ist jedoch die Deklaration als dynamisches Array ohne Angabe der Dimensionen. Diese können Sie jederzeit ändern, was bedeutet, dass Sie beispielsweise nachträglich neue Elemente hinzufügen können. Anfangs initialisieren Sie das dynamische Array daher mit einem einzigen Element: ReDim arrPerson(0)<\/b><\/li>\n
- Wenn Sie neue Elemente in ein dynamisches Array aufnehmen, müssen Sie es zuvor neu dimensionieren. Damit bei diesem Vorgang nicht alle bereits gespeicherten Elemente verloren gehen, fügen Sie das Schlüsselwort Preserve <\/b>ein: ReDim Preserve arrPerson(1)<\/b><\/li>\n
- Der Zugriff auf Elemente des Arrays geschieht immer über ihre Ordinalzahl, also den Index des Elements. Sie können ein Element nicht wie bei Collection<\/b>– und Dictionary<\/b>-Auflistungen über einen Schlüssel ansprechen. Damit entfällt auch eine gezielte Suche nach Elementen, sodass Sie Suchmechanismen selbst programmieren müssen.<\/li>\n
- Die Elemente eines Arrays können Sie über die Direktive Erase <\/b>leeren. Das ist gerade beim Speichern von Objekten im Array wichtig, weil Erase <\/b>dann alle Objektbezüge auf Nothing<\/b> setzt. Somit bestehen keine Referenzen auf die Ursprungsobjekte mehr, die dazu führen könnten, dass diese im Speicher verbleiben.<\/li>\n<\/ul>\n
Die Zahl der Elemente fragen Sie mit der Funktion UBound <\/b>ab:<\/p>\n
Debug.Print UBound(arrPerson) + 1<\/pre>\nDa UBound <\/b>den Index des letzten Elements zurückgibt, das Array normal aber nullbasiert ist, ergibt sich die Anzahl aus dem höchstem Index plus eins. Ist das Array noch gar nicht dimensioniert worden, liefert der Aufruf von UBound<\/b> nicht etwa den Wert 0<\/b>, sondern löst einen Fehler aus.<\/p>\n
Arrays können auch andere Arrays als Elemente aufnehmen. Dadurch ist, vor allem im Zusammenspiel mit benutzerdefinierten Typen, der Aufbau von Strukturen möglich. So einfach und schnell solche Gebilde auch sind, ein Manko haftet ihnen doch an: Um ein Element im Array zu finden, müssen Sie leider alle Elemente in einer Schleife durchlaufen und mit einer Suchvariablen vergleichen. Der Zeitaufwand für die Suche steigt damit linear mit der Anzahl der Elemente.<\/p>\n
Nehmen wir an, Sie hätten im Array des Beispiels aus Listing 2 genau 100 Personen gespeichert. Um nun den Vornamen der Person Müller<\/b> zu erfahren, müssen Sie alle Nachnamen-Elemente des Arrays mit diesem Suchbegriff vergleichen, um schließlich nach dem hundertsten Durchlauf Gleichheit festzustellen. Über den nun ermittelten Index des gefundenen Elements erhalten Sie den Vornamen:<\/p>\n
strFind = "Müller"\r\nFor i = 0 To UBound(arrPerson)\r\n If arrPerson(i).Nachname = strFind Then\r\n Debug.Print arrPerson(i).Vorname\r\n Exit For\r\n End If\r\nNext i<\/pre>\nUnter Performance-Gesichtspunkten ist also die Verwendung von Arrays kontraindiziert, wenn gezielt Elemente bearbeitet werden sollen.<\/p>\n
Collections<\/b><\/p>\n
Collections brauchen Sie im Gegensatz zu Arrays nicht zu dimensionieren. Sie erleichtern die Suche nach Elementen über ihren Schlüssel (Key<\/b>). Man spricht deshalb bei Collections und ihren Derivaten auch von assoziativen Arrays. Eine Collection<\/b>-Variable ist schnell angelegt und gefüllt:<\/p>\n
Sub PersonenCollection()\r\n Dim colPersonen As VBA.Collection\r\n Set colPersonen = New VBA.Collection\r\n With colPersonen\r\n .Add "Minhorst", "Nachname"\r\n .Add "André", "Vorname"\r\n .Add CDate("26.05.1929"), "Geburtsdatum"\r\n Debug.Print "Anzahl Elemente:" & .Count\r\n Debug.Print !Nachname\r\n End With\r\n Debug.Print colPersonen(2)\r\n Set colPersonen = Nothing\r\n End Sub<\/pre>\nWeitere Bemerkungen zum Collection<\/b>-Objekt:<\/p>\n
\n
- Eine Collection instanzieren Sie per New<\/b>-Anweisung. Vermeiden Sie, das bereits bei Deklaration der Variablen zu tun, weil damit immer unklar ist, in welchem Zustand sie sich befindet. Gelöscht wird die Instanz mit Setzen auf Nothing<\/b>.<\/li>\n
- Elemente fügen Sie mit der Add<\/b>-Methode hinzu, wobei Sie einen Schlüssel zur späteren Identifizierung hinzugeben können. Ohne den Schlüssel ist der Zugriff dann nur noch über den Index, also die Position des Elements im Array, möglich.<\/li>\n
- Ein Element können Sie auch zwischen bestehenden Elementen einfügen. Sie müssen dazu die optionalen Parameter before <\/b>und after <\/b>angeben. Erläuterungen finden Sie in der Visual Basic-Hilfe.<\/li>\n
- Ein Element können Sie über drei Syntaxarten direkt abfragen: Collection("Schlüssel")<\/b>, Collection!Schlüssel <\/b>oder Collection(Index)<\/b><\/li>\n
- Der Zugriff auf die Elemente über den Ordinalindex beginnt im Unterschied zu Arrays mit dem Index 1 <\/b>und nicht mit 0<\/b>. Das erste Element im Beispiel ist colPersonen(1)<\/b>.<\/li>\n
- Ein einmal hinzugefügtes Element können Sie nicht mehr verändern.<\/li>\n<\/ul>\n
<\/p>\n
Der letzte Punkt ist deutlich auf der Minusseite zu verbuchen. In einem Array können Sie jederzeit ein bestehendes Element mit einem neuen Wert überschreiben, in einer Collection geht das nicht. Der Workaround sieht so aus: Löschen Sie das bestehende Element und fügen Sie es mit gleichem Schlüssel wieder an gleicher Position hinzu:<\/p>\n
.Remove "Vorname"\r\n.Add "Krischna", "Vorname", , "Nachname"<\/pre>\nNachname<\/b> ist hier der Parameter after<\/b>, also der Schlüssel des Elements, hinter dem der neue Wert platziert werden soll. Das ist etwas umständlich und kostet wieder einiges an Performance, weil das Löschen und Einfügen intern einige Speicherverschiebungen voraussetzt. Bemerkbar dürfte sich das allerdings kaum machen, weil Collections sowohl beim Lesen wie Schreiben ohnehin sehr schnell sind.<\/p>\n
Die Tatsache, dass Collections auch Objekte als Elemente aufnehmen können (und somit auch andere Collections), macht sie für Verschachtelungen und Objektmodelle interessant. Eine Eigenschaft wurde noch verschwiegen: Collections sind enumerationsfähig, was bedeutet, dass sie ihre Elemente mit For Each<\/b>-Schleifen durchlaufen können:<\/p>\n
Dim itm As Variant\r\nFor Each itm In colPersonen\r\n Debug.Print itm, TypeName(itm)\r\nNext itm<\/pre>\nDas ist zweifellos praktischer als das Hantieren mit UBound <\/b>und Schleifenzählern, wie es Arrays notwendig machen.<\/p>\n
Dictionarys<\/b><\/p>\n
In Access-Kreisen offensichtlich selten benutzt wird das Dictionary<\/b>-Objekt. Das ist nicht verwunderlich, weil es nicht aus den unmittelbar in Access eingesetzten Standardbibliotheken kommt, sondern aus der Library Microsoft Scripting Runtime<\/b>. Mit zusätzlichen Referenzen soll man ja in VBA-Projekten im Interesse von Kompatibilität und Stabilität nicht wuchern, doch dieser Verweis ist ziemlich unverdächtig, findet sich die Datei scrrun.dll <\/b>schließlich in jedem Windows-System wieder. Früher wurde sie aus Sicherheitsgründen von Administratoren gerne mal deaktiviert, doch heute sollte das passé sein, weil viele Windows-Funktionen auf dieser Bibliothek aufbauen.<\/p>\n
Wenn Sie keinen Verweis auf die Bibliothek setzen möchten, dann erhalten Sie das Dictionary<\/b>-Objekt auch über Late Binding:<\/p>\n
Dim objDictionary as Object\r\nSet objDictionary = CreateObject("Scripting.µ\r\nDictionary")<\/pre>\nWie immer bei Late Binding müssen Sie dann aber kleinere Performance-Einbußen hinnehmen, die dem Marshalling der Objekte geschuldet sind, und können im VBA-Editor nicht die Dienste von IntelliSense in Anspruch nehmen.<\/p>\n
Ein Dictionary<\/b>-Objekt ist eigentlich auch eine Collection, hat jedoch mehr Eigenschaften und Methoden, wodurch es flexibler und einfacher zu bedienen ist.<\/p>\n
Der Code zum Instanzieren und Füllen des Dictionary<\/b>-Objekts ist fast identisch mit dem für Collections. Der folgende Beispielcode verwendet zur Abwechslung mal Late Binding:<\/p>\n
Sub PersonenDictionary()\r\n Dim dictPersonen As Object\r\n Set dictPersonen = CreateObject("Scripting.µ\r\n Dictionary")\r\n With dictPersonen\r\n !Nachname = "Trowitzsch"\r\n !Vorname = "Sascha"\r\n !Geburtsdatum = CDate("11.1.1918")\r\n Debug.Print "Anzahl Elemente:" & .Count\r\n Debug.Print !Nachname\r\n .Add "Nachname", "Minhorst" 'Fehler!\r\n End With\r\n Set dictPersonen = Nothing\r\n End Sub<\/pre>\nEs fällt auf, dass für das Hinzufügen von Elementen keine Add<\/b>-Methode zum Einsatz kommt, sondern lediglich eine direkte Zuweisung des Werts über einen Schlüssel. Das schreit nach Erläuterung, wobei die folgende Übersicht als Erweiterung dessen gilt, was Sie bereits zu Collections lesen konnten:<\/p>\n
\n
- Elemente eines Dictionary<\/b>-Objekts sind immer mit einem obligatorischen Schlüssel verknüpft. Ein Zugriff über einen Index, wie bei Collections, ist nicht direkt möglich. Der Grund hierfür ist, dass Sie als Schlüssel nicht nur Zeichenketten verwenden können, sondern beliebige Datentypen und sogar Objekte (Ausnahme: Arrays). Infolgedessen weiß ein Dictionary<\/b>-Objekt bei Angabe einer Ordinalzahl nicht, ob es sich hierbei um einen Index oder einen Key handelt.<\/li>\n
- Neue Elemente können Sie durch einfache Zuweisung eines Schlüssel-Wert-Paares anlegen.<\/li>\n
- Es gibt dennoch eine Methode Add<\/b>, die immer dann fehlschlägt, wenn ein Schlüssel bereits im Dictionary<\/b>-Objekt vorhanden ist.<\/li>\n
- Elemente können Sie im Gegensatz zu denen von Collections verändern. Der Wert wird unter Angabe des Schlüssels neu gesetzt, zum Beispiel:<\/li>\n<\/ul>\n
dictPersonen!Nachname = "Minhorst"<\/p>\n
\n
- Aus der Kombination der bisherigen Ausführungen folgt eine Eigentümlichkeit von Dictionarys, der besonderes Augenmerk zu widmen ist. Versuchen Sie, auf ein Element zuzugreifen, das noch nicht existiert, also dessen Schlüssel noch nicht in der Keys<\/b>-Auflistung zu finden ist, dann legt das Objekt einfach ein neues Element mit diesem Key <\/b>an und weist ihm den Wert Empty <\/b>zu. Die folgende Anweisung zum Abfragen des nicht existierenden Elements Adresse<\/b> gibt demnach weder den Wert Null <\/b>zurück, noch ereignet sich ein Fehler:<\/li>\n<\/ul>\n
Debug.Print dictPersonen!Adresse<\/p>\n
Stattdessen legt die Anweisung ein neues Element mit leerem Inhalt an.<\/p>\n
\n
- Um solche Probleme zu vermeiden, sieht das Dictionary die Funktion Exists <\/b>vor, mit der Sie das Vorhandensein eines Elements abfragen können:<\/li>\n<\/ul>\n
If dictPersonen.Exists("Adresse") Then _<\/p>\n
Debug.Print dictPersonen!Adresse<\/p>\n
\n
- Nicht nur den Wert eines Elements können Sie ändern, sondern auch dessen Schlüssel. Das mag Stirnrunzeln hervorrufen, denn wozu sollte man den Key <\/b>ändern – schließlich än-dert man die ID eines Datensatzes auch nicht, nachdem er einmal angelegt wurde. Sinn bekommt das Ganze aber etwa in Anbetracht der Tatsache, dass man die Elemente eines Dictionarys nicht wie mit einer Abfrage sortieren kann, ohne sie zu vertauschen. Sieht man für ein Dictionary numerische Schlüssel vor, dann kann man nach Sortierung nach Wert der Elemente einfach die Schlüssel in aufsteigender Reihenfolge neu vergeben und hat nun in Zukunft eine sortierte Liste vor sich.<\/li>\n<\/ul>\n
Den Schlüssel eines Elements ändern Sie so:<\/p>\n
arrPersonen.Key("Nachname") = "Vorname"<\/pre>\nAls Schlüssel für Dictionary<\/b>-Elemente können Sie auch Objekte angeben. Betrachten Sie folgendes Beispiel:<\/p>\n
Dim dict As New Dictionary\r\ndict.Add CurrentDb, "Test 1"\r\ndict.Add CurrentDb, "Test 2"\r\ndict.Add dict, "Test 3"\r\nDebug.Print dict(CurrentDb)\r\nDebug.Print dict(dict)<\/pre>\nDie Anweisungen verwenden drei verschiedene Objekte als Schlüssel: Zweimal den Verweis auf die aktuelle Datenbank und einmal wird als Kuriosum das Dictionary selbst als Schlüsselobjekt eingesetzt. Tatsächlich gibt nur die letzte Zeile etwas aus, nämlich „Test 3“.<\/p>\n
Das erste Debug.Print <\/b>zeigt nur einen leeren Wert. Die Ursache ist, dass CurrentDb <\/b>immer eine neue Kopie der aktuellen Datenbank zurückgibt, nicht einen Verweis auf das eigentliche Datenbankobjekt, den man mit DBEngine(0)(0) <\/b>erhielte. Also ist das dritte CurrentDb <\/b>ein falscher Schlüssel, der nicht nur nichts zurückgibt, sondern zu allem Überfluss auch noch automatisch ein neues Element anlegt, wie bereits weiter oben ausgeführt.<\/p>\n
Also sind die ersten zwei Elemente nun verschwunden Nicht wirklich, denn es gibt noch eine andere Methode, um auf deren Wert zuzugreifen:<\/p>\n
Debug.Print dict.Items(2)\r\nTest 2<\/pre>\nDas zeigt, dass man über Umwege doch einen Index für den Zugriff verwenden kann. Wo aber ist der Schlüssel (CurrentDb<\/b>) geblieben Auch der ist noch vorhanden:<\/p>\n
Debug.Print Typename(dict.Keys(2)) ' >"Database" !<\/pre>\nAlso führt auch diese etwas schräge Konstruktion zum Erfolg:<\/p>\n
Debug.Print dict(dict.Keys(2))\r\nTest 2<\/pre>\nEigentlich ist ein Dictionary<\/b>-Element ein Container für zwei verknüpfte Datenobjekte, auf die Sie später separat zugreifen können. Die Bedeutung des Begriffs Assoziative Liste <\/b>ist hier perfekt umgesetzt.<\/p>\n
Lassen wir es bei diesen Beispielen bewenden, die nur demonstrieren sollen, wie komplex der Umgang mit Dictionarys sein kann und dass Objektmodelle mit ihnen noch einfacher zu realisieren sind als mit Collections.<\/p>\n
In der Beispieldatenbank finden sich im Modul mdlDictionarySpecials <\/b>noch ausführlichere Prozeduren, die verschiedene weitere Möglichkeiten zeigen.<\/p>\n
Performance im richtigen Leben<\/b><\/p>\n
Schön und gut, werden Sie sich sagen, aber was fange ich nun mit den hier vorgestellten Elementen in meinen Anwendungen an<\/p>\n
Nun, Ihre Fantasie möchten wir nicht infrage stellen und lediglich anregen, sich beim nächsten Mal, wenn Ihnen die Verwendung von Arrays für einen Zweck vorschwebt, an diesen Beitrag zu erinnern und über Alternativen nachzudenken.<\/p>\n
Ein ausführliches Beispiel ist in der Demodatenbank enthalten und zeigt, wie man Daten einer Tabelle, die immer wieder in der Anwendung benötigt werden, ohne direkten Tabellenzugriff schnell auslesen und setzen kann.<\/p>\n
Das können etwa Daten sein, die Werte für beliebige Einstellungen der Anwendung enthalten – quasi Systemvariablen – und daher nennt sich die zugrunde liegende Tabelle auch tblVariablen<\/b>. <\/p>\n
Häufig realisiert man so etwas über ein globales Recordset, das ständig offen ist und auf dessen Datensätze über Find<\/b>-Methoden zugegriffen wird. Auch in der Demodatenbank gibt es diese Technik, aber eben auch alternative Methoden.<\/p>\n
Die Aufgabe besteht darin, die Werte aus der Tabelle tblVariablen <\/b>ständig schnell und ressourcensparend bereit zu halten.<\/p>\n
Ihr Aufbau ist einfach:<\/p>\n
\n
- ID <\/b>(Autowert<\/b>)<\/li>\n
- VarName <\/b>(Text<\/b>)<\/li>\n
- VarWert <\/b>(Text<\/b>)<\/li>\n<\/ul>\n
Ein Datensatz zur Speicherung einer Variablen für die horizontale Position eines Formulars sieht etwa so aus:<\/p>\n
1 | "frmPersonen.X" | "330"<\/pre>\nIn vielen Fällen wird so ein einzelner Wert über die Funktion DLookup <\/b>ermittelt:<\/p>\n
Function GetVarLookup(ByVal strVarName As String)\r\n GetVarLookup = DLookup("VarWert", _\r\n "tblVariablen", _\r\n "VarName='" & strVarName & "'")\r\nEnd Function\r\nDebug.Print GetVarLookup("frmPersonen.X")<\/pre>\nFür sporadische Zugriffe mag diese Funktion völlig in Ordnung sein, für häufige ist sie es aber nicht, wie Performance-Tests später zeigen werden.<\/p>\n
Die Beispieldatenbank enthält mehrere Module, die alle gleichartig aufgebaut sind und auch alle die gleichen Aufgaben erfüllen, aber mit unterschiedlichen Techniken arbeiten. Die Hauptaufgaben sind:<\/p>\n
\n
- Datensätze aus der Tabelle auslesen: InitVars<\/b><\/li>\n
- Variablen hinzufügen oder bearbeiten: SetVar (Variablenname, Wert)<\/b><\/li>\n
- Variablen auslesen: GetVar(Variablenname)<\/b><\/li>\n<\/ul>\n
Das Auslesen der Tabelle geschieht in allen Modulen zunächst über ein DAO-Recordset. Danach jedoch teilen sich die Wege und die Werte werden in Arrays (mdlArray<\/b>), Collections (mdlCollections<\/b>) oder Dictionarys (mdlDictionary<\/b>) überführt, über die sie anschließend ausgelesen oder neu gesetzt werden können.<\/p>\n
Das Modul mdlDAO <\/b>stellt mehr oder weniger den Prototyp dar. Es gibt dort ein modulweit gültiges Recordset rstVars<\/b>, das mit dieser Zeile aufgefüllt wird:<\/p>\n
Set rstVars = CurrentDb.OpenRecordset ("SELECT µ\r\nVarName, VarWert FROM tblVariablen", µ\r\ndbOpenDynaset)<\/pre>\nDas Lesen von Variablen geschieht über die Funktion GetVarDAO<\/b>:<\/p>\n
Function GetVarDAO(ByVal strVarName As String) _\r\n As Variant\r\n With rstVars\r\n .FindFirst "VarName='" & strVarName & "'"\r\n If .NoMatch Then\r\n GetVarDAO = Null\r\n Else\r\n GetVarDAO = !VarWert\r\n End If\r\n End With\r\nEnd Function<\/pre>\nWer unter DAO oft mit der Recordset<\/b>-Methode FindFirst<\/b> arbeitet, der weiß, dass diese Methode nicht besonders performant ist. Weitaus schneller ist die auf den Primärindex arbeitende Methode Seek<\/b>, die aber bei verknüpften Tabellen leider ausscheidet.<\/p>\n
Die Prozedur SetVar <\/b>fügt Variablen hinzu oder setzt diese neu. Die DAO-Variante sieht wie in Listing 3 aus.<\/p>\n
Listing 5: Variablen einer Collection bearbeiten <\/p>\n
Sub SetVarCol(ByVal strVarName As String, Wert As Variant)\r\n On Error Resume Next\r\n colVars.Add Wert, strVarName\r\n If Err.Number = 457 Then 'Schlüssel bereits vorhanden\r\n colVars.Remove strVarName\r\n colVars.Add Wert, strVarName\r\nEnd If\r\nEnd Sub\r\nFunction GetVarCol(ByVal strVarName As String) As Variant\r\nGetVarCol = colVars(strVarName)\r\nEnd Function<\/pre>\nDie restlichen Prozeduren beschäftigen sich mit dem Löschen von Werten oder dem Abbau von Objekten und sollen hier nicht im Detail beschrieben werden.<\/p>\n
Das Modul mdlArrays <\/b>zeigt, genau wie die entsprechenden Module mit den Collection<\/b>– und Dictionary<\/b>-Beispielen, eine Abweichung: Nach dem Öffnen des Recordsets wird über die Prozedur FillArray <\/b>dessen Inhalt in das modulweit gültige Array arrVars <\/b>überführt. Die analogen Prozeduren für die anderen Module lauten FillCollection<\/b>, FillDictionary <\/b>und füllen die Objektvariablen colVars<\/b> und dictVars<\/b>. Die Funktionen zum Auslesen und Setzen der Variablen laufen ab da nur noch über diese Auflistungsobjekte ohne Bezug zur Tabelle tblVariablen<\/b>.<\/p>\n
Nebenbei: Prozeduren, die den Inhalt der Variablen abschließend wieder in der Quelltabelle speichern, müssten Sie selbst nachtragen – sie fehlen in der Beispieldatenbank.<\/p>\n
Beispielhaft zeigen die Listings 4 bis 6 die einzelnen Funktionen zum Auslesen und Setzen der Variablen. Schon am Umfang der Prozeduren ist zu erkennen, welche Methoden sich für die gestellte Aufgabe besser oder weniger eignen.<\/p>\n
Was nun noch ansteht, ist ein Stress-Test dieser Module und eine Messung der Performance der Lösungen. Die dafür benötigten Aufrufprozeduren sind für Sie im Modul mdlTest<\/b> zusammengefasst. Werfen Sie dort einfach die Sub-Funktion TabelleFuellenUndPerformanceTesten <\/b>an und warten Sie auf das Ergebnis im VBA-Direktfenster. Der Ablauf des Tests im Schnelldurchlauf:<\/p>\n
\n
- Tabelle tblVariablen <\/b>mit 1.000 künstlichen Werten füllen<\/li>\n
- Auflistungsvariablen in den einzelnen Modulen initialisieren<\/li>\n
- Für jede Methode jeweils 10.000 Variablenwerte auslesen<\/li>\n
- Für jede Methode jeweils 5.000 Werte setzen, wobei davon 1.000 neu angelegt und 4.000 bearbeitet werden<\/li>\n
- Für jede Methode die benötigte Zeit messen<\/li>\n<\/ul>\n
Tab. 1 zeigt das mit einem aktuellen Rechner ermittelte Ergebnis und spricht Bände.<\/p>\n
Fast schon schockierend: Die Dictionary<\/b>-Methode ist etwa um den Faktor 250 schneller als DLookup <\/b>– ein weiterer Beweis für die Unzulänglichkeit dieser Funktion. <\/p>\n
Gut ist es aber auch nicht um DAO-Recordset-Methoden bestellt. Die beiden SQL-Einträge kommen übrigens durch weitere Methoden zustande, dem Setzen und Lesen von Datensätzen über zusammengesetzte SQL-Strings, wie hier:<\/p>\n
DBEngine(0)(0).OpenRecordset("SELECT VarWert µ\r\nFROM tblVariablen " & "WHERE VarName=µ\r\n'Nachname'", dbOpenSnapshot)(0)<\/pre>\nAuch das ist keine empfehlenswerte Lösung, wobei man ihr zugutehalten muss, dass sie in anderen Zusammenhängen wegen der flexibleren Such- und Filtermöglichkeiten ein Mittel der Wahl ist.<\/p>\n
Die Array-Methoden schneiden sogar schlechter ab als der DAO-Zugriff.<\/p>\n
Das allerdings nicht etwa deshalb, weil Arrays langsam wären, sondern weil der gezielte Zugriff auf ihre Elemente nur durch Schleifenkonstrukte möglich ist.<\/p>\n
Als Spitzenreiter bleiben die Collection<\/b>– und Dictionary<\/b>-Lösungen, die in einer ganz eigenen Liga spielen. Die Messungen befinden sich hier schon fast außerhalb der Toleranzen. Man muss sich vor Augen führen, dass sich in einem Dictionary<\/b>-Element gezielt locker 300.000 Elemente pro Sekunde setzen lassen.<\/p>\n
Fazit<\/b><\/p>\n
Werfen Sie Ihre Arrays über Bord und vor allen Dingen Ihre DLookups, wenn nicht triftige Gründe dagegen sprechen.<\/p>\n
Leichter und performanter als mit Collections und Dictionarys werden Sie tabellenunabhängige Datenlisten kaum bearbeiten können.<\/p>\n
Und letztlich spricht wegen der Flexibilität von Dictionarys, etwa der datentyptoleranten Schlüssel, auch wieder nichts für Collections.<\/p>\n
\n\n
\n \n Listing 2: Array mit Elementen eines benutzerdefinierten Datentyps<\/p>\n<\/td>\n<\/tr>\n
\n \n Type Person\r\nNachname As String\r\nVorname As String\r\nGeburtsdatum As Date\r\nEnd Type\r\nSub PersonenArray()\r\n Dim arrPerson() As Person\r\n ReDim arrPerson(0)\r\n arrPerson(0).Vorname = "Sascha"\r\n arrPerson(0).Nachname = "Trowitzsch"\r\n arrPerson(0).Geburtsdatum = "11.1.1918"\r\n ...\r\n ReDim Preserve arrPerson(100)\r\n arrPerson(100).Vorname = "Walter"\r\n arrPerson(100).Nachname = "Ulbricht"\r\n arrPerson(100).Geburtsdatum = "31.12.1899"\r\n Debug.Print arrPerson(100).Nachname\r\n Erase arrPerson\r\n End Sub<\/pre>\n<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n\n\n
\n \n Wohin mit den Anwendungseinstellungen<\/p>\n<\/td>\n<\/tr>\n
\n \n Anwendungseinstellungen müssen im Prinzip natürlich nicht in verknüpften Tabellen eines Backends gespeichert werden, denn schließlich handelt es sich hier ja um lokale Einstellungen. Die Sache hat aber einen Haken: Sobald Sie ein Update Ihrer Anwendung (Frontends) an die Benutzer weitergeben, werden deren Einstellungen alle verloren gehen beziehungsweise durch Ihre ersetzt.<\/p>\n
Deshalb ist es günstiger, solche Variablentabellen nicht im Frontend unterzubringen, sondern im Backend. Im Unterschied zu unserer Demo ist dann allerdings ein weiteres indiziertes Feld Username <\/b>oder ähnlich in die Variablentabelle aufzunehmen, damit die Einstellungen eines Benutzers entsprechend gefiltert werden können.<\/p>\n<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n
Listing 4: Variablen eines Arrays bearbeiten<\/p>\n
Sub SetVarArr(ByVal strVarName As String, Wert As Variant)\r\n Dim i As Long\r\n Dim n As Long\r\n n = UBound(arrVars, 2)\r\n For i = 0 To n\r\n If arrVars(0, i) = strVarName Then\r\n arrVars(1, i) = Wert\r\n Exit For\r\n End If\r\n Next i\r\n ReDim Preserve arrVars(1, i)\r\n arrVars(0, i) = strVarName\r\n arrVars(1, i) = Wert\r\n End Sub\r\nFunction GetVarArr(ByVal strVarName As String) As Variant\r\n Dim i As Long\r\n Dim n As Long\r\n n = UBound(arrVars, 2)\r\n For i = 0 To n\r\n If arrVars(0, i) = strVarName Then\r\n GetVarArr = arrVars(1, i)\r\n Exit For\r\n End If\r\n Next i\r\nEnd Function<\/pre>\nListing 6: Variablen eines Dictionary bearbeiten <\/p>\n
Sub SetVarDict(ByVal strVarName As String, Wert As Variant)\r\n dictVars(strVarName) = Wert\r\n End Sub\r\nFunction GetVarDict(ByVal strVarName As String) As Variant\r\n GetVarDict = dictVars(strVarName)\r\nEnd Function<\/pre>\nDownloads zu diesem Beitrag<\/h3>\n
Enthaltene Beispieldateien:<\/p>\n
collections.mdb<\/p>\n