Mehrwertige Felder sind eine Erfindung von Microsoft, um den Umgang mit Datenkonstrukten, bei denen für ein Feld mehrere Werte ausgewählt werden können, zu vereinfachen. In einem mehrwertigen Feld können wir aus einer Liste von Werten, die entweder aus einer Wertliste oder aus einer anderen Tabelle stammen, keinen, einen oder mehrere Einträge auswählen. Aus einer verknüpfen Tabelle mehrere Werte zuordnen? Das hört sich ja eigentlich nach dem Einsatzzweck einer m:n-Beziehung an. Genau das bildet Microsoft intern ab. Allerdings funktioniert das nur innerhalb des Access-Biotops. Sollen die Daten einmal zum SQL Server oder einer anderen Datenbank wandern, wird es kompliziert. Hier können wir solche Konstrukte nämlich nicht mehr einfach abbilden – wir müssen diese also ersetzen. Wie wir die mehrwertigen Felder loswerden, zeigen wir in diesem Beitrag.<\/b><\/p>\n
Microsoft hat in Access 2010 einige Neuerungen geliefert, die in Zusammenhang mit den gleichzeitig veröffentlichen sogenannten Webdaten standen. Neben den mehrwertigen Feldern sind das zum Beispiel die Anlagefelder. Wobei die Anlagefelder, wenn man sie richtig nutzt, zumindest noch einigermaßen sinnvoll sind. 1:1 zum SQL Server übertragen können wir diese jedoch ebenfalls nicht. Das ist allerdings Thema eines anderen Beitrags.<\/p>\n
In diesem Beitrag wollen wir uns die mehrwertigen Felder ansehen. Davon gibt es grob zwei Ausführungen:<\/p>\n
Beide sind praktisch, bis man mal einen der Einträge aus der Liste entfernt – diese bleiben nämlich in der Auswahl erhalten. In diesem Beitrag schauen wir uns zunächst an, was geschieht, wenn wir erstgenannten Felder zum SQL Server migrieren wollen und wie wir das Datenmodell und die enthaltenen Daten korrekt zum SQL Server migrieren.<\/p>\n
Richtig problematisch wird es jedoch, wenn wir Tabellen mit solchen Feldern zum SQL Server migrieren wollen. Warum das der Fall ist, zeigen wir gleich nach dem Erstellen der Beispiele für diesen Beitrag.<\/p>\n
Zu Beispielzwecken legen wir eine Tabelle namens tblProdukte <\/b>an, welche die folgenden drei Felder enthält:<\/p>\n Für den Datentyp des dritten Feldes wählen wir Nachschlage-Assistent… <\/b>aus (siehe Bild 1).<\/p>\n Bild 1: Anlegen eines Nachschlagefeldes<\/span><\/b><\/p>\n Im Assistenten führen wir folgende Schritte durch:<\/p>\n Bild 2: Einstellen der Nachschlagefeld-Eigenschaften<\/span><\/b><\/p>\n<\/ul>\n Danach können wir für das Feld Kategorien<\/b> in den Eigenschaften unter Nachschlagen <\/b>die gewünschten Kategorien durch Semikola separiert für die Eigenschaft Datensatzherkunft<\/b> eingeben (siehe Bild 3).<\/p>\n Bild 3: Hinzufügen der Einträge<\/span><\/b><\/p>\n Wenn wir wollen, dass der Benutzer die Werte selbst ändern kann, stellen wir noch die Eigenschaft Wertlistenbearbeitung zulassen <\/b>auf den Wert Ja <\/b>ein.<\/p>\n Wenn wir ein neues Formular mit dieser Tabelle als Datensatzquelle ausstatten und die drei Felder hinzufügen, sehen wir das Ergebnis aus Bild 4. In diesem Fall ist die Wertlistenbearbeitung aktiviert, was wir an der Schaltfläche erkennen, die beim Öffnen der Liste eingeblendet wird.<\/p>\n Bild 4: Einsatz der mehrwertigen Wertliste im Formular<\/span><\/b><\/p>\n Bevor wir prüfen, was beim Migrieren der mehrwertigen Wertliste zum SQL Server passiert, schauen wir uns an, was diese Felder eigentlich speichern. Zunächst einmal betrachten wir den im Feld Kategorien <\/b>des ersten Datensatzes gespeicherten Wert. Dazu nutzen wir die DLookup<\/b>-Funktion, die folgendes Ergebnis liefert:<\/p>\n Außerdem schauen wir uns noch an, wie die zugrunde liegenden Daten aussehen. Dazu lesen wir die Eigenschaft RowSource <\/b>des Kombinationsfeldes aus:<\/p>\n Wir erhalten also zwei Listen, von denen die erste nur die selektierten Einträge enthält.<\/p>\n Nun migrieren wir diese Tabelle mit dem SQL Server Migration Assistant in eine neue Datenbank im SQL Server – mehr dazu im Beitrag Access und SQL Server: Der Migrations-Assistent <\/b>(www.access-im-unternehmen.de\/18<\/b>).<\/p>\n Dazu gehen wir nach dem Starten des SSMA wie folgt vor:<\/p>\n Bild 5: Zu migrierende Tabelle auswählen<\/span><\/b><\/p>\n Bild 6: Verbinden mit der noch anzulegenden Datenbank<\/span><\/b><\/p>\n Bild 7: Die neue Datenbank ist bereits erstellt.<\/span><\/b><\/p>\n Bild 8: Es gibt einen Fehler.<\/span><\/b><\/p>\n Bild 9: Ein Datentyp wird nicht unterstützt.<\/span><\/b><\/p>\n In diesem Fall sehen wir, dass das Feld Kategorien<\/b> zwar migriert wurde.<\/p>\n Allerdings finden wir hier ein Feld mit dem Datentyp varchar(8000)<\/b> vor. Das Feld enthält lediglich die für die jeweiligen Produkte ausgewählten Kategorien. Die verfügbaren Kategorien sind hier nicht mehr zu sehen (siehe Bild 10).<\/p>\n Bild 10: Das Ergebnis der Migration<\/span><\/b><\/p>\n Und da die meisten Tabelleneigenschaften, vor allem aber die zum Thema Nachschlagefelder, nicht zum SQL Server übertragen werden, können wir nicht damit rechnen, dass die vollständige Liste der verfügbaren Kategorien irgendwo im SQL Server zu finden ist.<\/p>\n Wenn wir die zum SQL Server migrierte Tabelle tblProdukte <\/b>nun per ODBC-Verknüpfung in die Beispieldatenbank einbinden, sehen wir dort im Feld Kategorien <\/b>zwar noch die gleichen Werte, die auch in der ursprünglichen Tabelle enthalten waren, aber wir können diese nicht mehr selektieren.<\/p>\n Auch wenn wir eine Kopie des Beispielformulars von oben anlegen und dieses mit der verknüpften Tabelle dbo_tblProdukte<\/b> versehen, sehen wir dort zwar die Liste der markierten Einträge. Wenn wir die Liste jedoch öffnen, finden wir keine Möglichkeit mehr, mehr als einen Eintrag auszuwählen (siehe Bild 11).<\/p>\n Bild 11: Bei der migrierten und neu verknüpften Tabelle werden die Kontrollkästchen für die Kategorien nicht mehr angezeigt.<\/span><\/b><\/p>\n Nun schauen wir uns an, wie wir diese mehrwertigen Wertlisten so migrieren können, dass die gewünschten Daten vom SQL Server bereitgestellt werden und auch zugewiesen werden können. Es führt dabei kein Weg daran vorbei, die Daten, die sich im Feld Kategorien <\/b>der Tabelle tblProdukte <\/b>befinden, in eine eigene Tabelle namens tblKategorien <\/b>auszulagern. Diese weisen wir allerdings nicht der Eigenschaft Datensatzherkunft <\/b>des Feldes Kategorien <\/b>der Tabelle tblProdukte <\/b>zu.<\/p>\n Dies wäre nur sinnvoll, wenn nur eine Kategorie je Produkt ausgewählt werden soll. Die Ausgangssituation ist jedoch, dass wir jedem Produkt eine, keine oder mehrere Kategorien zuweisen können. Um dies zu realisieren, benötigen wir also auch noch eine Verknüpfungstabelle zum Herstellen einer m:n-Beziehung. Diese nennen wir tblProdukteKategorien<\/b>.<\/p>\n In weiteren Schritten sind zunächst die Einträge der Wertliste des Feldes Kategorien <\/b>in die Tabelle tblKategorien <\/b>zu übertragen. Und zu guter Letzt übertragen wir noch die ausgewählten Werte in die Verknüpfungstabelle tblProdukteKategorien<\/b>.<\/p>\n Damit sind wir leider noch nicht am Ende der Migrationsarbeiten angelangt. Die einfache Darstellung aus dem mehrwertigen Feld mit der Anzeige aller Einträge, die man einfach per Kontrollkästchen selektieren kann, gibt es so für m:n-Beziehungen leider nicht.<\/p>\n Also werden wir in weiteren Beiträgen Alternativen dazu vorstellen – siehe m:n-Daten wie im mehrwertigen Feld selektieren <\/b>(www.access-im-unternehmen.de\/1424<\/b>).<\/p>\n Wir gehen in diesen Schritten vor:<\/p>\n Diese Tabelle enthält nur zwei Felder – das Primärschlüsselfeld KategorieID <\/b>und das Feld Kategorie<\/b>. Für dieses legen wir einen eindeutigen Index fest, damit jede Kategorie nur einmal eingetragen werden kann (siehe Bild 12).<\/p>\n Bild 12: Tabelle zum Speichern der Kategorien<\/span><\/b><\/p>\n Die Tabelle tblProdukteKategorien <\/b>soll die Verknüpfung jedes Eintrags der Tabelle tblProdukte <\/b>mit jedem Eintrag der Tabelle tblKategorien <\/b>erlauben.<\/p>\n Dazu fügen wir dieser Tabelle zunächst ein Primärschlüsselfeld namens ProduktKategorieID <\/b>hinzu. Außerdem legen wir zwei Fremdschlüsselfelder an. Das erste heißt ProduktID <\/b>und ermöglicht die Angabe des Produkts eines Datensatzes, das zweite heißt KategorieID <\/b>und soll das Zuweisen einer Kategorie erlauben.<\/p>\n Für die beiden Felder ProduktID <\/b>und KategorieID <\/b>legen wir außerdem einen zusammengesetzten, eindeutigen Index an (siehe Bild 13).<\/p>\n Bild 13: Verknüpfungstabelle zwischen den Tabellen tblProdukte und tblKategorien<\/span><\/b><\/p>\n Da diese Tabelle direkt zum SQL Server migriert werden soll, der bekanntermaßen solche Eigenschaften wie die zum Realisieren von Nachschlagefeldern in Access nicht erlaubt, brauchen wir uns gar nicht erst die Mühe zu machen, die Fremdschlüsselfelder als Nachschlagefelder einzurichten.<\/p>\n Damit kommen wir zu den Beziehungen, die wir über das Beziehungen-Fenster anlegen. Dazu ziehen wir die drei Tabellen tblProdukte<\/b>, tblProdukteKategorien <\/b>und tblKategorien <\/b>in das Beziehungen-Fenster. Die Beziehung zwischen der Tabelle tblProdukte <\/b>und tblProdukteKategorien <\/b>erzeugen wir, indem wir das Feld ProduktID <\/b>von der Tabelle tblProdukte <\/b>auf das gleichnamige Feld der Tabelle tblProdukteKategorien <\/b>ziehen. Im nun erscheinenden Dialog Beziehungen bearbeiten <\/b>aktivieren wir die Option Mit referentieller Integrität<\/b>. Außerdem aktivieren wir die Option Löschweitergabe an verwandte Datensätze<\/b>.<\/p>\n Damit sorgen wir dafür, dass wenn wir ein Produkt löschen, auch die Verknüpfungen zu den jeweiligen Kategorien aus der Tabelle tblProdukteKategorien <\/b>entfernt werden.<\/p>\n Den gleichen Schritt führen wir für die Beziehung zwischen den Tabellen tblKategorien <\/b>und tblProdukteKategorien <\/b>durch. Hier ziehen wir das Feld KategorieID <\/b>von tblKategorien <\/b>auf das Feld KategorieID <\/b>der Tabelle tblProdukteKategorien<\/b>.<\/p>\n Neben der Option Mit referentieller Integrität <\/b>aktivieren keine weitere Option – insbesondere nicht die Option Löschweitergabe an verwandte Datensätze<\/b>.<\/p>\n So verhindern wir, dass Kategorien aus der Tabelle tblKategorien <\/b>gelöscht werden können, die bereits einem Produkt zugeordnet sind (siehe Bild 14).<\/p>\n Bild 14: Anlegen der Verknüpfungen zwischen den Tabellen<\/span><\/b><\/p>\n Nun wollen wir alle Kategorien aus der Wertliste des Feldes Kategorien <\/b>der Tabelle tblProdukte <\/b>in die Tabelle tblKategorien <\/b>übertragen. Dazu haben wir dieser Tabelle einige weitere Produkte samt Kategorien hinzugefügt (siehe Bild 15). Die Einträge der Wertliste des Feldes Kategorien <\/b>können wir per DAO-Zugriff auf die jeweilige Eigenschaft des Feldes ermitteln. Wir erhalten eine Liste, deren Einträge durch Semikola voneinander getrennt sind:<\/p>\n Bild 15: Zu übertragende Daten<\/span><\/b><\/p>\n Um diese jeweils als neuen Datensatz zur Tabelle tblKategorien <\/b>hinzuzufügen, nutzen wir die VBA-Prozedur aus Listing 1.<\/p>\n Listing 1: Übertragen der Kategorien<\/span><\/b><\/p>\n Diese liest den Inhalt der Wertliste des Feldes Kategorien <\/b>der Tabelle tblProdukte <\/b>in die Variable strKategorien <\/b>ein. Dann erzeugt sie daraus mit der Split<\/b>-Methode ein Array, das sie mit der Variablen strKategorieArray <\/b>referenziert.<\/p>\n Die Elemente dieses Arrays durchlaufen wir in einer For…Next<\/b>-Schleife über alle Elemente des Arrays, wobei wir den ersten und den letzten Index mit den Funktionen LBound <\/b>und UBound <\/b>ermitteln.<\/p>\n In dieser Schleife führen wir eine INSERT INTO<\/b>-Anweisung aus, die den aktuellen Wert des Arrays in einen neuen Datensatz der Tabelle tblKategorien <\/b>einträgt.<\/p>\n Diese SQL-Anweisung hat eine Besonderheit gegenüber anderen Beispielen, in denen wir Texte in ein Textfeld einer Tabelle eintragen. Normalerweise würden wir die Variable wie folgt in Hochkommata einfassen, da wir damit einen Text zum Feld hinzufügen wollen:<\/p>\n Wie wir weiter oben gesehen haben, enthält die Eigenschaft Wertliste die einzelnen Kategorien bereits als in Anführungszeichen eingefasste Einträge. Wir müssen in diesem Fall also auf die Hochkommata verzichten, da wir sonst Werte wie „Kategorie 1“ <\/b>in der Tabelle vorfinden würden.<\/p>\n Nachdem wir die Prozedur ausgeführt haben, sieht die Tabelle tblKategorien <\/b>wie in Bild 16 aus.<\/p>\n Bild 16: Gefüllte Tabelle mit Kategorien<\/span><\/b><\/p>\n Es kann an dieser Stelle passieren, dass die Datenbank Kategorien doppelt enthält. Dies würde bei der obigen Prozedur zu einem Fehler führen, wenn diese versucht, der Tabelle tblKategorien <\/b>einen neuen Datensatz hinzuzufügen, der im Feld Kategorie <\/b>einen Wert enthält, der bereits in der Tabelle enthalten ist. Der Grund ist, dass wir weiter oben einen eindeutigen Index für das Feld Kategorie <\/b>der Tabelle tblKategorien <\/b>definiert haben. Der Versuch, einen bereits vorhandenen Wert nochmals hinzuzufügen, resultiert im Fehler 3022<\/b>. Diesen können wir jedoch durch Einschließen der db.Execute<\/b>-Anweisung in die folgenden Zeilen verhindern:<\/p>\n Dadurch unterbinden wir allerdings auch Meldungen, die durch andere Fehler entstehen. Es kann zum Beispiel sein, dass sich irgendwie ein Hochkomma in einen Kategorienamen eingeschlichen hat. Um dadurch ausgelöste Fehler zu verhindern, verdoppeln wir eventuell auftauchende Hochkommata vorher wie folgt:<\/p>\n Nun wollen wir die eigentlichen Inhalte des Feldes Kategorien <\/b>in die Tabelle tblProdukteKategorien <\/b>übertragen. Wie wir weiter oben gesehen haben, können wir per DLookup <\/b>leicht auf die Liste der zugeordneten Werte zugreifen:<\/p>\n Wenn wir allerdings per DAO-Recordset auf den Inhalt des entsprechenden Feldes zugreifen wollen, erhalten wir einen Fehler (siehe Bild 17).<\/p>\n Bild 17: Fehler beim Zugriff auf die Kategorien<\/span><\/b><\/p>\n Lassen wir uns hier den Typ von rst!Kategorien <\/b>im Direktbereich ausgeben, erhalten wir:<\/p>\n Das heißt, dass Access beim Zugriff über DLookup <\/b>wohl noch die Daten zusammenstellt, die intern gespeichert werden. Also gehen wir davon aus, dass wir auf die so gespeicherten Elemente auf ähnliche Weise zugreifen können, wie es auch bei Anlagefeldern ist – hier finden wir innerhalb des Anlagefeldes ein Recordset vor, aus dem wir die tatsächlichen Werte des Anlagefeldes auslesen können. Bei den mehrwertigen Feldern ist dies allerdings etwas einfacher als bei Anlagefeldern.<\/p>\n Wir nutzen dazu die Prozedur aus Listing 2. Diese öffnet ein Recordset auf Basis der Tabelle tblProdukte<\/b> und referenziert dieses mit der Variablen rst<\/b>. Dieses durchläuft die Prozedur anschließend in einer Do While<\/b>-Schleife.<\/p>\n Listing 2: Übertragen der Zuordnung der Kategorien zu den Produkten<\/span><\/b><\/p>\n Darin erfasst sie den Primärschüsselwert des aktuellen Produkts mit der Variablen lngProduktID<\/b>. Dann referenziert sie das Feld Kategorien <\/b>mit der Variablen fld3<\/b>, dessen Inhalt der Eigenschaft Value <\/b>dann von einem weiteren Recordset namens rstKategorien <\/b>erfasst wird.<\/p>\n Auch dieses Recordset durchlaufen wir in einer Do While<\/b>-Schleife und erhalten so Zugriff auf jeden einzelnen Eintrag der Kategorien<\/b>-Wertliste.<\/p>\n Wir lesen den Wert der Kategorie aus dem Feld Value <\/b>in die Variable strKategorie <\/b>ein. Dann holen wir uns aus der Tabelle tblKategorien<\/b>, die ja bereits alle Kategorien enthält, den Primärschlüsselwert dieser Kategorie und speichern ihn mit lngKategorieID<\/b>.<\/p>\n Es kann jedoch sein, dass eine Kategorie, obwohl sie einem Produkt zugeordnet ist, nicht in der Tabelle tblKategorien <\/b>enthalten ist. Wie kann das geschehen? Das beschreiben wir weiter unten. Jedenfalls landet dann mithilfe der Nz<\/b>-Funktion der Wert 0 <\/b>in der Variablen lngKategorieID<\/b>. Ist lngKategorieID <\/b>nach diesem Schritt 0<\/b>, fragen wir den Benutzer, ob die benannte Kategorie aus strKategorie <\/b>in der Tabelle tblKategorien <\/b>angelegt werden soll. Ist das gewünscht, schreibt die folgende INSERT INTO<\/b>-Anweisung den fehlenden Datensatz in die Tabelle tblKategorien<\/b> und liest den Primärschlüsselwert des neuen Datensatzes in die Variable lngKategorieID <\/b>ein.<\/p>\n Ist lngKategorieID <\/b>danach nicht 0<\/b>, wird ein neuer Datensatz in der Tabelle tblProdukteKategorien <\/b>angelegt und somit eine neue Verknüpfung von Produkten und Kategorien.<\/p>\n Auf diese Weise werden alle Produkte und Kategorien durchlaufen und entsprechende Datensätze in der Tabelle tblProdukteKategorien <\/b>angelegt.<\/p>\n Ein beispielhaftes Ergebnis der Zuordnung von Produkten zu Kategorien in der Tabelle tblProdukteKategorien <\/b>sehen wir in Bild 18.<\/p>\n Bild 18: Zuordnung von Produkten zu Kategorien<\/span><\/b><\/p>\n Oben haben wir Code in die Prozedur eingebaut für den Fall, dass wir im Feld Kategorien <\/b>der Tabelle tblProdukte <\/b>eine Kategorie finden, die nicht in der Tabelle tblKategorien <\/b>vorhanden ist.<\/p>\n Wie aber kann es überhaupt dazu kommen, dass eine Kategorie verlorengeht? Das demonstrieren wir wie folgt. Wir legen einen neuen Datensatz namens Produkt 5 <\/b>in der Tabelle tblProdukte <\/b>an.<\/p>\n Dann öffnen wir das Auswahlfeld Kategorien <\/b>und klicken unten auf die Schaltfläche zum Bearbeiten der Einträge.<\/p>\n Dies öffnet den Dialog Listenelemente bearbeiten<\/b>, dem wir eine neue Kategorie namens Kategorie 5 <\/b>hinzufügen (siehe Bild 19).<\/p>\n Bild 19: Neue Kategorie für ein Produkt<\/span><\/b><\/p>\n Wir schließen den Dialog wieder und fügen dem Eintrag Produkt 5 <\/b>die Kategorie Kategorie 5<\/b> hinzu (siehe Bild 20).<\/p>\n Bild 20: Zuweisen der neuen Kategorie<\/span><\/b><\/p>\n Jetzt kommt der entscheidende Schritt, der auch ein Grund ist, warum man mehrwertige Felder wie dieses nicht nutzen sollte: Wie öffnen nochmals den Dialog zum Bearbeiten der Kategorien und löschen den Eintrag Kategorie 5 <\/b>wieder (siehe Bild 21).<\/p>\n Bild 21: Wir löschen die neue Kategorie wieder.<\/span><\/b><\/p>\n Wenn wir nun die Liste für eines der übrigen Produkte öffnen, dem wir Kategorie 5 <\/b>nicht zugeordnet haben, sehen wir diesen Eintrag nicht in der Auswahl. Wenn wir jedoch die Liste der Kategorien für Produkt 5 <\/b>öffnen, ist Kategorie 5 <\/b>dort noch vorhanden.<\/p>\n Würden wir so zuerst die Kategorien aus der Wertliste des Feldes Kategorien <\/b>in die Tabelle tblKategorien <\/b>übertragen, würden dort die vier Einträge Kategorie 1<\/b>, Kategorie 2<\/b>, Kategorie 3 <\/b>und Kategorie 4 <\/b>landen.<\/p>\n Kategorie 5<\/b>, die noch dem Produkt Produkt 5 <\/b>zugeordnet ist, landet jedoch nicht dort – außer, wir fügen sie durch die Spezialbehandlung, die wir in der Prozedur eingefügt haben, nachträglich hinzu.<\/p>\n Danach können wir die drei Tabellen ohne Probleme in eine SQL Server-Datenbank migrieren und das Access-Frontend mit diesen Tabellen verknüpfen.<\/p>\n Sofern wir das ursprüngliche mehrwertige Feld Kategorien <\/b>noch nicht aus der Tabelle tblProdukte <\/b>entfernt haben, erscheint zwar nach wie vor die Fehlermeldung, aber diese können wir nun ignorieren. Der Ordnung halber sollten wir aber, nachdem wir die Daten aus diesem Feld in die beiden Tabellen tblKategorien <\/b>und tblProdukteKategorien <\/b>exportiert haben, entfernen.<\/p>\n Fehlt nur noch, dass Sie diese Prozeduren einsetzen können, um Ihre eigenen mehrwertigen Felder in richtige Beziehungen umzuwandeln. Den Aufwand, diese Funktionen nun an die bei Ihnen oder bei Ihrem Kunden vorhandene Tabelle mit mehrwertigem Feld anzupassen, ist hoch und fehleranfällig. Dann bauen wir lieber gleich zwei passende Prozeduren. Außerdem statten wir diese Prozeduren gleich noch mit einem Extra aus: Sie nehmen Ihnen auch noch das Erstellen der Lookup-Tabelle und der Verknüpfungstabelle ab. Die erste Prozedur heißt MehrwertigeWertlisteInTabelle <\/b>und wird wie folgt aufgerufen:<\/p>\n Sie ist in Listing 3 abgebildet. Gegenüber der Prozedur KategorienInTabelle <\/b>enthält sie noch zwei Anweisungen, die gleich noch die Tabelle zum Aufnehmen der Daten der Wertliste des mehrwertigen Feldes erstellt. Die zweite Prozedur heißt MehrwertigeWertlistenzuordnungInTabelle <\/b>und erwartet die Namen der ganzen beteiligten Tabellen und Felder als Parameter – wie in diesem Aufruf:<\/p>\n Listing 3: Lookuptabelle für die Werte des mehrwertigen Feldes erstellen und füllen<\/span><\/b><\/p>\n Sie legt die Verknüpfungstabelle an und stellt gleich noch die beiden Beziehungen zu der ursprünglichen Tabelle und der neu erstellten Lookuptabelle her. Dazu müssen wir ausnahmsweise das ADODB-Connection<\/b>-Objekt mit seiner Execute<\/b>-Methode nutzen, da wir mit DAO-Execute <\/b>keine Lösch- und Aktualisierungsweitergabe definieren können. Diese Prozedur finden Sie in Listing 4.<\/p>\n\n
![\"Anlegen](\"..\/fileadmin\/_temp_\/2024_04\/pic_1493_001.png\")
<\/p>\n\n
![\"Einstellen](\"..\/fileadmin\/_temp_\/2024_04\/pic_1493_002.png\")
<\/p>\n![\"Hinzufügen](\"..\/fileadmin\/_temp_\/2024_04\/pic_1493_003.png\")
<\/p>\nMehrwertige Wertliste im Formular<\/h2>\n
![\"Einsatz](\"..\/fileadmin\/_temp_\/2024_04\/pic_1493_004.png\")
<\/p>\nAnalyse der Daten<\/h2>\n
Debug.Print<\/span> DLookup(\"Kategorien\", \"tblProdukte\")\r\nKategorie 1; Kategorie 3<\/pre>\nDebug.Print<\/span> CurrentDb.TableDefs(\"tblProdukte\").Fields(\"Kategorien\").Properties(\"RowSource\")\r\n\"Kategorie 1\";\"Kategorie 2\";\"Kategorie 3\";\"Kategorie 4\"<\/pre>\nMigration dieser Tabelle zum SQL Server<\/h2>\n
\n
![\"Zu](\"..\/fileadmin\/_temp_\/2024_04\/pic_1493_005.png\")
<\/p>\n![\"Verbinden](\"..\/fileadmin\/_temp_\/2024_04\/pic_1493_006.png\")
<\/p>\n![\"Die](\"..\/fileadmin\/_temp_\/2024_04\/pic_1493_007.png\")
<\/p>\n![\"Es](\"..\/fileadmin\/_temp_\/2024_04\/pic_1493_008.png\")
<\/p>\n![\"Ein](\"..\/fileadmin\/_temp_\/2024_04\/pic_1493_009.png\")
<\/p>\n![\"Das](\"..\/fileadmin\/_temp_\/2024_04\/pic_1493_010.png\")
<\/p>\n![\"Bei](\"..\/fileadmin\/_temp_\/2024_04\/pic_1493_011.png\")
<\/p>\nMehrwertige Wertlisten korrekt migrieren<\/h2>\n
Von der mehrwertigen Wertliste zur m:n-Beziehung<\/h2>\n
\n
Erstellen der Tabelle tblKategorien<\/h2>\n
![\"Tabelle](\"..\/fileadmin\/_temp_\/2024_04\/pic_1493_012.png\")
<\/p>\nErstellen der Tabelle tblProdukteKategorien<\/h2>\n
![\"Verknüpfungstabelle](\"..\/fileadmin\/_temp_\/2024_04\/pic_1493_013.png\")
<\/p>\nEinrichten der Beziehungen<\/h2>\n
![\"Anlegen](\"..\/fileadmin\/_temp_\/2024_04\/pic_1493_014.png\")
<\/p>\nÜbertragen der Kategorien in die Tabelle tblKategorien<\/h2>\n
![\"Zu](\"..\/fileadmin\/_temp_\/2024_04\/pic_1493_015.png\")
<\/p>\n\"Kategorie 1\";\"Kategorie 2\";\"Kategorie 3\";\"Kategorie 4\"<\/pre>\n
Public Sub <\/span>KategorienInTabelle()\r\n Dim <\/span>db As <\/span>DAO.Database\r\n Dim <\/span>strKategorien As String<\/span>\r\n Dim <\/span>strKategorieArray() As String<\/span>\r\n Dim <\/span>lngKategorie As Long<\/span>\r\n Dim <\/span>strKategorie As String<\/span>\r\n Set<\/span> db = CurrentDb\r\n strKategorien = db.TableDefs(\"tblProdukte\").Fields(\"Kategorien\").Properties(\"RowSource\")\r\n strKategorieArray = Split<\/span>(strKategorien, \";\")\r\n For lngKategorie = LBound<\/span>(strKategorieArray) To UBound<\/span>(strKategorieArray)\r\n strKategorie = strKategorieArray(lngKategorie)\r\n db.Execute \"INSERT INTO tblKategorien(Kategorie) VALUES(\" & strKategorie & \")\", dbFailOnError\r\n Next<\/span> lngKategorie\r\nEnd Sub<\/span><\/pre>\ndb.Execute \"INSERT INTO tblKategorien(Kategorie) VALUES(''\" & strKategorie & \"'')\", dbFailOnError<\/pre>\n![\"Gefüllte](\"..\/fileadmin\/_temp_\/2024_04\/pic_1493_016.png\")
<\/p>\nAbfangen doppelter Kategorien<\/h2>\n
On Error Resume Next<\/span>\r\ndb.Execute ...\r\nOn Error Goto 0<\/pre>\nstrKategorie = Replace<\/span>(strKategorie, \"''\", \"''''\")<\/pre>\nÜbertragen der zugeordneten Kategorien in die Tabelle tblProdukteKategorien<\/h2>\n
DLookup(\"Kategorien\", \"tblProdukte\")\r\nKategorie 1; Kategorie 3<\/pre>\n
![\"Fehler](\"..\/fileadmin\/_temp_\/2024_04\/pic_1493_017.png\")
<\/p>\n Typename(rst!Kategorien)\r\nField3<\/pre>\n
Public Sub <\/span>KategoriezuordnungenInTabelle()\r\n Dim <\/span>db As <\/span>DAO.Database\r\n Dim <\/span>rst As <\/span>DAO.Recordset\r\n Dim <\/span>strKategorie As String<\/span>\r\n Dim <\/span>fld3 As <\/span>DAO.Field3\r\n Dim <\/span>rstKategorien As <\/span>DAO.Recordset\r\n Dim <\/span>lngProduktID As Long<\/span>\r\n Dim <\/span>lngKategorieID As Long<\/span>\r\n Set<\/span> db = CurrentDb\r\n Set<\/span> rst = db.OpenRecordset(\"SELECT ProduktID, Kategorien FROM tblProdukte\", dbOpenDynaset)\r\n Do While<\/span> Not<\/span> rst.EOF\r\n lngProduktID = rst!ProduktID\r\n Set<\/span> fld3 = rst!Kategorien\r\n Set<\/span> rstKategorien = fld3.Value\r\n Do While<\/span> Not<\/span> rstKategorien.EOF\r\n strKategorie = rstKategorien!Value\r\n lngKategorieID = Nz(DLookup(\"KategorieID\", \"tblKategorien\", \"Kategorie = ''\" & strKategorie & \"''\"), 0)\r\n If <\/span>lngKategorieID = 0 Then<\/span>\r\n If MsgBox<\/span>(\"Kategorie ''\" & strKategorie & \"'' noch nicht vorhanden. Anlegen?\", vbYesNo + vbExclamation, _\r\n \"Fehlende Kategorie\") = vbYes Then\r\n strKategorie = Replace<\/span>(strKategorie, \"''\", \"''''\")\r\n db.Execute \"INSERT INTO tblKategorien(Kategorie) VALUES(''\" & strKategorie & \"'')\", dbFailOnError\r\n lngKategorieID = db.OpenRecordset(\"SELECT @@IDENTITY\").Fields(0)\r\n End If<\/span>\r\n End If<\/span>\r\n If <\/span>Not<\/span> lngKategorieID = 0 Then<\/span>\r\n On Error Resume Next<\/span>\r\n db.Execute \"INSERT INTO tblProdukteKategorien(ProduktID, KategorieID) VALUES(\" & lngProduktID _\r\n & \", \" & lngKategorieID & \")\", dbFailOnError\r\n On Error GoTo<\/span> 0\r\n End If<\/span>\r\n rstKategorien.MoveNext<\/span>\r\n Loop<\/span>\r\n rst.MoveNext<\/span>\r\n Loop<\/span>\r\nEnd Sub<\/span><\/pre>\n![\"Zuordnung](\"..\/fileadmin\/_temp_\/2024_04\/pic_1493_018.png\")
<\/p>\nWie können Kategorien verlorengehen?<\/h2>\n
![\"Neue](\"..\/fileadmin\/_temp_\/2024_04\/pic_1493_019.png\")
<\/p>\n![\"Zuweisen](\"..\/fileadmin\/_temp_\/2024_04\/pic_1493_020.png\")
<\/p>\n![\"Wir](\"..\/fileadmin\/_temp_\/2024_04\/pic_1493_021.png\")
<\/p>\nFlexible einsetzbare Prozeduren<\/h2>\n
MehrwertigeWertlisteInTabelle \"tblProdukte\", \"Kategorien\", \"tblKategorien\", \"KategorieID\", \"Kategorie\"<\/pre>\n
Public Sub <\/span>MehrwertigeWertlisteInTabelle(strBasistabelle As String<\/span>, strBasisfeld As String<\/span>, _\r\n strLookuptabelle As String<\/span>, strLookupPK As String<\/span>, strLookupfeld As String<\/span>)\r\n Dim <\/span>db As <\/span>DAO.Database\r\n Dim <\/span>strWerte As String<\/span>\r\n Dim <\/span>strWertArray() As String<\/span>\r\n Dim <\/span>lngWert As Long<\/span>\r\n Dim <\/span>strWert As String<\/span>\r\n Set<\/span> db = CurrentDb\r\n db.Execute \"CREATE TABLE \" & strLookuptabelle & \"(\" & strLookupPK & \" AUTOINCREMENT PRIMARY KEY, \" _\r\n & strLookupfeld & \" Text(255) NOT NULL);\", dbFailOnError\r\n db.Execute \"CREATE UNIQUE INDEX idx\" & strLookupfeld & \" ON \" & strLookuptabelle & \" (\" & strLookupfeld _\r\n & \");\", dbFailOnError\r\n strWerte = db.TableDefs(strBasistabelle).Fields(strBasisfeld).Properties(\"RowSource\")\r\n strWertArray = Split<\/span>(strWerte, \";\")\r\n For lngWert = LBound<\/span>(strWertArray) To UBound<\/span>(strWertArray)\r\n strWert = strWertArray(lngWert)\r\n strWert = Replace<\/span>(strWert, \"''\", \"''''\")\r\n On Error Resume Next<\/span>\r\n db.Execute \"INSERT INTO \" & strLookuptabelle & \"(\" & strLookupfeld & \") VALUES(\" & strWert & \")\", dbFailOnError\r\n On Error Resume Next<\/span>\r\n Next<\/span> lngWert\r\nEnd Sub<\/span><\/pre>\nMehrwertigeWertlistenzuordnungInTabelle \"tblProdukte\", \"ProduktID\", \"Kategorien\", \"tblKategorien\", \"KategorieID\", \"Kategorie\", \"tblProdukteKategorien\", \"ProduktKategorieID\", \"ProduktID\", \"KategorieID\"<\/pre>\n
Public Sub <\/span>MehrwertigeWertlistenzuordnungInTabelle(strBasistabelle As String<\/span>, strPKBasistabelle As String<\/span>, _\r\n strLookupfeld As String<\/span>, strLookuptabelle As String<\/span>, strLookuptabellePK As String<\/span>, strLookuptabelleWert As <\/span>_\r\n String, strMNTabelle As String<\/span>, strMNPK As String<\/span>, strMFK As String<\/span>, strNFK As String<\/span>)\r\n Dim <\/span>db