<\/p>\n
![\"Datenmodell](\"..\/fileadmin\/_temp_\/2020_06\/pic_1276_001.png\")
<\/p>\n
Bild 1: Datenmodell der Beispieldatenbank<\/span><\/b><\/p>\n Wenn Sie eine Anwendung bereitstellen wollen, die bereits Daten enthält und deren Daten sowohl von Ihnen als auch vom Benutzer der Anwendung erweitert werden sollen, müssen Sie einige Dinge beachten. Wir gehen zur Vereinfachung davon aus, dass nur neue Daten hinzukommen und keine vorhandenen Daten geändert werden – das würde die Aufgabe noch schwieriger gestalten. Als Beispiel soll eine Datenbank mit Produkten dienen, die sowohl aus einem Basiskatalog gefüttert werden, der gelegentlich erweitert wird, als auch vom Benutzer der Anwendung. Wie Sie das überhaupt machen, welche Probleme das mit sich bringt und wie Sie diese lösen, zeigen wir in diesem Beitrag.<\/b><\/p>\n Im Beispiel verwenden wir zwei Tabellen, damit die möglichen Probleme schnell deutlich werden: Die Tabelle tblProdukte <\/b>enthält die Produktdaten sowie die Zuordnung zu einer Kategorie. Die Kategorien werden in der zweiten Tabelle tblKategorien <\/b>gespeichert (siehe Bild 1). Der Benutzer soll sowohl neue benutzerdefinierte Kategorien als auch Produkte anlegen können. Genauso soll der Basiskatalog von Produkten und Kategorien erweiterbar sein.<\/p>\n Bild 1: Datenmodell der Beispieldatenbank<\/span><\/b><\/p>\n Angenommen, es gibt im Basiskatalog drei Kategorien mit den Primärschlüsselwerten 1<\/b>, 2 <\/b>und 3<\/b>. Außerdem gibt es einige Produkte, die mit diesen Kategorien verknüpft sind. Nun fügt der Benutzer eine neue Kategorie hinzu, die standardmäßig den nächsten freien Autowert erhält, zum Beispiel den Wert 4<\/b>, und fügt außerdem einige Produkte hinzu, die mit dieser neuen Kategorie verknüpft sind.<\/p>\n Wenn der Herausgeber der Produktdatenbank nun dem Basiskatalog eine neue Kategorie hinzufügt, wird diese dort ebenfalls den Primärschlüsselwert 4 <\/b>erhalten. Wenn wir die neuen Daten des Basiskatalogs nun in die bereits vom Anwender geänderte Datenbank übertragen wollen, müssen wir den Primärschlüsselwert einer der Kategorien ändern – entweder für die aus dem Basiskatalog oder für die vom Benutzer hinzugefügte Kategorie.<\/p>\n Da sowohl im Basiskatalog als auch im der vom Benutzer erweiterten Datenbank bereits Produkte enthalten sind, die mit der neuen Kategorie verknüpft sind, müssen wir folglich auch die Fremdschlüsselfelder dieser Einträge anpassen.<\/p>\n Man würde hier wohl die Daten des Benutzers anpassen, damit die Daten des Basiskatalogs in allen Kopien der Anwendung die gleichen Primärschlüsselwerte hat.<\/p>\n Das ist technisch kein Problem, allerdings ist es ein Aufwand, den man sich sparen könnte. Ein Weg ist, GUIDs als Primärschlüsselwerte zu verwenden. Allerdings bringt dies wieder eigene Probleme mit sich, die wir im Beitrag GUIDs im Primärschlüsselfeld nutzen <\/b>in der folgenden Ausgabe zeigen (www.access-im-unternehmen.de\/1278<\/b>).<\/p>\n Alternative: Eigener Nummernkreis für den Anwender<\/b><\/p>\n Also wählen wir einen alternativen Weg: Wir gehen davon aus, dass die Anzahl der Produkte und Kategorien, die dem Basiskatalog hinzugefügt werden, endlich ist und wählen mit ausreichender Sicherheit eine maximale Menge aus – sagen wir 1.000.000.<\/p>\n Wenn der Benutzer einen neuen Produkt-Datensatz anlegt, soll dieser einen Autowert erhalten, der größer als 1.000.000 ist. Wie erledigen wir das<\/p>\n Dazu fügen wir einfach einen Datensatz hinzu, dessen Primärschlüsselfeld den Wert 1.000.000 <\/b>aufweist. Und keine Sorge: Sie müssen dazu nicht manuell die entsprechende Anzahl Datensätze eingeben. Wir fügen diesen Datensatz gezielt mit einer Prozedur wie der folgenden hinzu:<\/p>\n Die Prozedur führt eine INSERT INTO<\/b>-Abfrage aus, die einen Datensatz hinzufügt, der den vorgegebenen Wert für das Feld KategorieID <\/b>enthält. Das ist übrigens die einzige Methode, wie Sie trotz Autowert einen eigenen ID-Wert zu einer Tabelle hinzufügen können – mit der DAO-Methode AddNew <\/b>ist das nicht möglich.<\/p>\n Wenn der Benutzer nun einen neuen Datensatz zur Tabelle tblKategorien <\/b>hinzufügt, erhält dieser direkt den nächsthöheren Wert (siehe Bild 2).<\/p>\n Bild 2: Neuer Benutzer-Datensatz<\/span><\/b><\/p>\n Der Dummy-Datensatz hat nur einen Nachteil: Er erscheint beispielsweise im Nachschlagefeld zur Auswahl einer Kategorie. Wie können wir das ändern Dazu gibt es zwei Möglichkeiten:<\/p>\n Bild 3: Neuer Benutzer-Datensatz ohne Dummy-Datensatz<\/span><\/b><\/p>\n<\/ul>\n In der ersten Variante stellen wir die Eigenschaft Datensatzherkunft <\/b>des Feldes KategorieID <\/b>im Entwurf der Tabelle tblProdukte <\/b>wie folgt ein:<\/p>\n Damit erscheint der Eintrag nicht in diesem Nachschlagefeld und auch nicht in Kombinationsfeldern in Formularen, die auf Basis des Nachschlagefeldes angelegt werden (siehe Bild 4).<\/p>\n Bild 4: Anpassen der Datensatzherkunft des Nachschlagefeldes<\/span><\/b><\/p>\n ID vor Weitergabe löschen<\/b><\/p>\n Bei der zweiten Variante legen wir wir mit der oben genannten Prozedur einen Datensatz mit dem Wert 1.000.000 an und löschen diesen direkt wieder. <\/p>\n Dazu erweitern wir die Prozedur AutowertSetzen <\/b>um die entsprechende DELETE<\/b>-Anweisung:<\/p>\n Da Access sich den höchsten angelegten Autowert merkt, auch wenn man diesen wieder löscht, wird der erste Datensatz des Benutzers in der Tabelle tblKategorien <\/b>den Primärschlüsselwert 1.000.001 <\/b>erhalten.<\/p>\n Wie aber können wir sicherstellen, dass der Benutzer die Datenbank nicht komprimiert und damit den Autowert-Zähler zurücksetzt – sodass der Autowert eines neuen Datensatzes für unser Beispiel den Wert 4 <\/b>liefert<\/p>\n Auch das lässt sich erledigen: Wir verlegen einfach den Aufruf der Prozedur AutowertSetzen <\/b>an den Start der Anwendung. Allerdings erweitern wir diese noch leicht, indem wir eine Prüfung wie in Listing 1 voranstellen. Hier kontrollieren wir, ob der höchste Wert des Feldes KategorieID <\/b>kleiner als 1.000.000 <\/b>ist. Ist das der Fall, wurde die Datenbank offensichtlich komprimiert oder der Autowert wurde noch nicht auf 1.000.000 <\/b>eingestellt. Also holen wir dies nach, indem wir einen neuen Datensatz mit diesem Autowert hinzufügen und diesen direkt wieder löschen. Damit erhält der erste vom Benutzer zur Tabelle tblKategorien <\/b>hinzugefügte Datensatz den Wert 1.000.001<\/b>.<\/p>\n <\/p>\n Listing 1: Setzen des Autowertes, wenn dieser nicht hoch genug ist<\/span><\/b><\/p>\n Diese Prozedur müssen wir nur noch beim Start der Anwendung aufrufen – dazu legen Sie beispielsweise ein Makro namens AutoExec <\/b>an, das die Makroaktion CodeAusführen <\/b>enthält und die Funktion AutowertSetzen() <\/b>aufruft – die Sub<\/b>-Prozedur müssen Sie dazu in eine Function<\/b>-Prozedur ändern.<\/p>\n Den Entwurf des Makros finden Sie in Bild 5.<\/p>\n Bild 5: Makro zum Aufrufen der Funktion zum Einstellen des Autowerts<\/span><\/b><\/p>\n Damit wird auch nach dem Komprimieren direkt wieder 1.000.000 <\/b>als letzter Autowert eingestellt, wenn der größte Wert des Feldes KategorieID <\/b>kleiner als 1.000.000 <\/b>ist.<\/p>\n Parametrisieren der Funktion AutowertSetzen<\/b><\/p>\n In der Beispieldatenbank haben wir zwei Tabellen, die wir mit dieser Funktion mit einem neuen Start-Autowert ausstatten müssen. Man könnte die Funktion AutowertSetzen<\/b> parametrisieren, indem man beispielsweise den Namen der Tabelle und des Primärschlüsselfeldes sowie den gewünschten Autowert übergibt und diese Informationen dann in den enthaltenen Anweisungen verarbeitet.<\/p>\n Das Problem ist nur, dass Tabellen meist Einschränkungen für Felder enthalten – zum Beispiel würde unsere Tabelle tblKategorie <\/b>es nicht erlauben, beim Anlegen eines neuen Datensatzes keinen Wert in das Feld Kategorie <\/b>einzugeben.<\/p>\n Auf diese Weise müssten wir alle Felder mit Einschränkungen wie dieser füllen – und das gelingt mit Parametern nicht. Wir müssen also die INSERT INTO<\/b>– und die DELETE<\/b>-Aufrufe individuell in die Prozedur AutowerteSetzen <\/b>eintragen. Dabei müssen wir auch eventuelle Verknüpfungen wie hier über das Fremdschlüsselfeld KategorieID <\/b>der Tabelle tblProdukte <\/b>berücksichtigen.<\/p>\n In diesem Fall verwenden wir direkt den neu zur Tabelle tblKategorien <\/b>hinzugefügten Datensatz als Kategorie des neu zur Tabelle tblProdukte <\/b>hinzuzufügenden Produkts (siehe Listing 2).<\/p>\n Listing 2: Setzen des Autowertes für zwei verknüpfte Tabellen<\/span><\/b><\/p>\n Wir müssen auch auf die Reihenfolge achten und zuerst die Tabellen mit dem an der Beziehung beteiligten Primärschlüsselfeld anlegen. Das Löschen geschieht dann in umgekehrter Reihenfolge.<\/p>\n Und nun wird es kompliziert: Es kann ja auch sein, dass der Benutzer zwar einen Datensatz für die Tabelle tblProdukte <\/b>angelegt hat, aber nicht für die Tabelle tblKategorien <\/b>– weil er für das neue Produkt eine der mit dem Basiskatalog gelieferten Kategorien ausgewählt hat.<\/p>\n In diesem Fall würde unsere Funktion nicht funktionieren, weil wir ja nur prüfen, ob in der Tabelle tblKategorien <\/b>nur Datensätze mit einem Primärschlüsselwert kleiner als 1.000.000 <\/b>existieren. Dann fügen wir jeweils einen neuen Datensatz mit dem Primärschlüsselwert 1.000.000 <\/b>hinzu, was dazu führt, dass der folgende Datensatz in beiden Tabellen den Autowert 1.000.001 <\/b>erhält.<\/p>\n Hat der Benutzer dann aber schon ein Produkt mit dem Autowert 1.000.001 <\/b>angelegt, führt das zu einem Fehler. Dies stellen Sie wie folgt nach:<\/p>\n Bild 6: Doppelter Primärschlüsselwert<\/span><\/b><\/p>\n<\/ul>\n Wir müssen die Funktion AutowertSetzen<\/b> also noch um eine individuelle Betrachtung der Tabellen erweitern, die wie in Listing 3 aussieht. Hier lesen wir nun für jede Tabelle den aktuell größten Wert des Primärschlüsselfeldes ein und behandeln die Tabellen einzeln. Eine weitere Änderung ist, dass wir die Werte für die Fremdschlüsselfelder der zum Setzen des Autowertes angelegten Datensätze auf die jeweils als maximal ermittelten Werte der verknüpften Tabelle einstellen. Wir ermitteln also beispielsweise den größten Wert des Feldes KategorieID <\/b>in der Tabelle tblKategorien <\/b>und nutzen diesen für das Fremdschlüsselfeld KategorieID <\/b>der Tabelle tblProdukte<\/b>.<\/p>\n Listing 3: Setzen des Autowertes, wenn dieser nicht hoch genug ist – erweiterte Fassung<\/span><\/b><\/p>\n Daten-Update aus erweitertem Basiskatalog hinzufügen<\/b><\/p>\n Damit kommen wir zum zweiten Teil der Aufgabe: Wie können wir neue Daten aus dem Basiskatalog zu den Datenbankanwendungen beim Benutzer hinzufügen Hier gibt es wieder verschiedene Möglichkeiten, zum Beispiel die folgenden:<\/p>\n In beiden Fällen sollen immer alle Datensätze vorhanden sein und es soll versucht werden, diese in die Tabellen der Datenbank zu schreiben. Fehler beim Versuch, vorhandene Datensätze zu überschreiben, sollen ignoriert werden (beziehungsweise protokolliert). So landen dann auf jeden Fall alle Datensätze in der Datenbank, die dort noch nicht vorhanden sind. Wir wählen an dieser Stelle die erste Variante, da die zweite noch das Erstellen der benötigten SQL-Anweisungen erfordern würde.<\/p>\n Wir erstellen also eine neue Datenbank, welche die gleichen Tabellen enthält wie die Zieldatenbank. Außerdem fügen wir dieser Datenbank die Funktion Datenupdate<\/b> aus Listing 4 hinzu. Zusätzlich fügen wir auch dieser Datenbank ein AutoExec<\/b>-Makro hinzu, das diesmal allerdings die Funktion Datenupdate <\/b>aufruft.<\/p>\n Listing 4: Kopieren der Daten<\/span><\/b><\/p>\n Die einfachste Version der Funktion Datenupdate <\/b>definiert in der Variablen strZiel <\/b>die Zieldatenbank. Dazu verwenden wir den mit CurrentProject.Path <\/b>ermittelten Pfad plus den bekannten Dateinamen. Wir setzen also voraus, dass die Datenbank mit den Basisdaten vor dem Starten in das Verzeichnis der Zieldatenbank kopiert wird. Dann referenzieren wir das aktuelle Database<\/b>-Objekt und rufen für jede Tabelle mit anzufügenden Daten die Execute<\/b>-Methode auf.<\/p>\n Die verwendeten INSERT INTO<\/b>-Anweisungen verwenden das IN<\/b>-Schlüsselwort, das die Zieldatenbank der Operation angibt. Außerdem legen wir als Quelle alle Felder und Datensätze der Tabellen tblKategorien <\/b>und tblProdukte <\/b>fest. Schließlich achten wir auch hier auf die Reihenfolge: Wir fügen bei verknüpften Tabellen zuerst die Daten der Tabellen ein, die das an der Beziehung beteiligte Primärschlüsselfeld enthält – also tblKategorien <\/b>vor tblProdukte<\/b>.<\/p>\n Wir haben den Tabellen der Beispieldatenbank mit dem Basiskatalog jeweils einen vierten Datensatz hinzufügt, welcher in beiden Fällen den Primärschlüsselwert 4 <\/b>hat.<\/p>\n Das Ergebnis aus Bild 7 überzeugt – die in der Datenbank mit dem Basiskatalog angelegten Datensätze wurden genau unter den gewünschten Primärschlüsselwerten zu den beiden Tabellen tblKategorien <\/b>und tblProdukte <\/b>hinzugefügt.<\/p>\n Bild 7: Zieldatenbank mit je einem neuen Datensatz<\/span><\/b><\/p>\n Aber warum gab es keine Fehlermeldung, obwohl wir auch versucht haben, bereits vorhandene Datensätze anzufügen Weil wir den Parameter dbFailOnError <\/b>nicht angegeben haben. In diesem Fall werden keine Fehlermeldungen ausgeben, auch wenn welche auftreten.<\/p>\n Fügen wir diesen Parameter hinzu und starten die Datenbank mit dem Basiskatalog erneut, erhalten wir die Fehlermeldung aus Bild 8. In diesem Fall ist die Verwendung der Execute<\/b>-Methode ohne dbFailOnError <\/b>jedoch praktischer.<\/p>\n<\/p>\nPublic Sub <\/span>AutowertSetzen()\r\n Dim <\/span>db As <\/span>DAO.Database\r\n Set<\/span> db = CurrentDb\r\n db.Execute \"INSERT INTO tblKategorien(KategorieID, Kategorie) VALUES(1000000, 'Dummy')\", dbFailOnError\r\nEnd Sub<\/span><\/pre>\n![\"Neuer](\"..\/fileadmin\/_temp_\/2020_06\/pic_1276_002.png\")
<\/p>\n\n
![\"Neuer](\"..\/fileadmin\/_temp_\/2020_06\/pic_1276_003.png\")
<\/p>\nSELECT [KategorieID], [Kategorie] FROM tblKategorien WHERE NOT KategorieID = 1000000;<\/pre>\n
![\"Anpassen](\"..\/fileadmin\/_temp_\/2020_06\/pic_1276_004.png\")
<\/p>\nPublic Sub <\/span>AutowertSetzen()\r\n ...\r\n db.Execute \"DELETE FROM tblKategorien WHERE KategorieID = 1000000\", dbFailOnError\r\nEnd Sub<\/span><\/pre>\nPublic Function <\/span>AutowertSetzen()\r\n Dim <\/span>db As <\/span>DAO.Database\r\n Dim <\/span>rst As <\/span>DAO.Recordset\r\n Set<\/span> db = CurrentDb\r\n Set<\/span> rst = db.OpenRecordset(\"SELECT Max(KategorieID) FROM tblKategorien\", dbOpenDynaset)\r\n If <\/span>rst(0) < 1000000 Then<\/span>\r\n db.Execute \"INSERT INTO tblKategorien(KategorieID, Kategorie) VALUES(1000000, 'Dummy')\", dbFailOnError\r\n db.Execute \"DELETE FROM tblKategorien WHERE KategorieID = 1000000\", dbFailOnError\r\n End If<\/span>\r\nEnd Function<\/span><\/pre>\n![\"Makro](\"..\/fileadmin\/_temp_\/2020_06\/pic_1276_005.png\")
<\/p>\nPublic Function <\/span>AutowertSetzen()\r\n Dim <\/span>db As <\/span>DAO.Database\r\n Dim <\/span>rst As <\/span>DAO.Recordset\r\n Set<\/span> db = CurrentDb\r\n Set<\/span> rst = db.OpenRecordset(\"SELECT Max(KategorieID) FROM tblKategorien\", dbOpenDynaset)\r\n If <\/span>rst(0) < 1000000 Then<\/span>\r\n db.Execute \"INSERT INTO tblKategorien(KategorieID, Kategorie) VALUES(1000000, 'Dummy')\", dbFailOnError\r\n db.Execute \"INSERT INTO tblProdukte(ProduktID, Produktbezeichnung, KategorieID) VALUES(1000000, \" _\r\n & \"'Dummy', 1000000)\", dbFailOnError\r\n db.Execute \"DELETE FROM tblProdukte WHERE ProduktID = 1000000\", dbFailOnError\r\n db.Execute \"DELETE FROM tblKategorien WHERE KategorieID = 1000000\", dbFailOnError\r\n End If<\/span>\r\nEnd Function<\/span><\/pre>\n\n
![\"Doppelter](\"..\/fileadmin\/_temp_\/2020_06\/pic_1276_006.png\")
<\/p>\nPublic Function <\/span>AutowertSetzen()\r\n Dim <\/span>db As <\/span>DAO.Database\r\n Dim <\/span>rstKategorien As <\/span>DAO.Recordset\r\n Dim <\/span>rstProdukte As <\/span>DAO.Recordset\r\n Set<\/span> db = CurrentDb\r\n Set<\/span> rstKategorien = db.OpenRecordset(\"SELECT Max(KategorieID) FROM tblKategorien\", dbOpenDynaset)\r\n Set<\/span> rstProdukte = db.OpenRecordset(\"SELECT Max(ProduktID) FROM tblProdukte\", dbOpenDynaset)\r\n If <\/span>rstKategorien(0) < 1000000 Then<\/span>\r\n db.Execute \"INSERT INTO tblKategorien(KategorieID, Kategorie) VALUES(1000000, 'Dummy')\", dbFailOnError\r\n End If<\/span>\r\n If <\/span>rstProdukte(0) < 1000000 Then<\/span>\r\n db.Execute \"INSERT INTO tblProdukte(ProduktID, Produktbezeichnung, KategorieID) VALUES(1000000, 'Dummy', \" _\r\n & rstKategorien(0) & \")\", dbFailOnError\r\n db.Execute \"DELETE FROM tblProdukte WHERE ProduktID = 1000000\", dbFailOnError\r\n End If<\/span>\r\n If <\/span>rstKategorien(0) < 1000000 Then<\/span>\r\n db.Execute \"DELETE FROM tblKategorien WHERE KategorieID = 1000000\", dbFailOnError\r\n End If<\/span>\r\nEnd Function<\/span><\/pre>\n\n
Public Function <\/span>Datenupdate()\r\n Dim <\/span>db As <\/span>DAO.Database\r\n Dim <\/span>strZiel As String<\/span>\r\n Set<\/span> db = CurrentDb\r\n strZiel = CurrentProject.Path & \"Datenupdates_Ziel.accdb\"\r\n db.Execute \"INSERT INTO tblKategorien IN '\" & strZiel & \"' SELECT * FROM tblKategorien\"\r\n db.Execute \"INSERT INTO tblProdukte IN '\" & strZiel & \"' SELECT * FROM tblProdukte\"\r\nEnd Function<\/span><\/pre>\n![\"Zieldatenbank](\"..\/fileadmin\/_temp_\/2020_06\/pic_1276_007.png\")
<\/p>\n![\"Fehler](\"..\/fileadmin\/_temp_\/2020_06\/pic_1276_008.png\")
<\/p>\n