Manchmal benötigt man eine 1:1-Kopie der Daten aus einer Access-Tabelle in einer anderen Tabelle – vielleicht in der gleichen, vielleicht aber auch in einer anderen Datenbank. Das ist mit entsprechenden Einfügeabfragen kein Problem, solange kein anderes Datenbanksystem wie etwa der SQL Server als Ziel infrage kommt – und Sie gleichzeitig die Daten nicht nur einfach einfügen, sondern auch noch die Inhalte der Autowertfelder beibehalten wollen. Dieser Beitrag zeigt praktische Erfahrungen und Lösungsweisen für spezielle Migrationsvorhaben auf.
Warum von Tabelle zu Tabelle kopieren
Bei mir ist die oben erwähnte Aufgabe aufgetreten, als ich die Daten einer lokalen Suedsturm-Datenbank in das neu erstellte Datenmodell einer LocalDB-SQL Server-Datenbank kopieren wollte. Ich habe die Tabellen der LocalDB-Datenbank dann in die aktuelle Access-Datenbank eingebunden, sodass der komplette Satz der Tabellen jeweils zweimal vorlag – einmal unter den richtigen Namen als lokale Tabellen und einmal mit dem Präfix 1 als eingebundene Tabellen (also etwa tblArtikel und tblArtikel1).
Kopieren per Drag and Drop
Der erste Ansatz, die Tabelleninhalte von A nach B zu kopieren, war der per Drag and Drop. Also die Quelltabelle öffnen, mit Strg + A alle Datensätze markieren und mit Strg + C kopieren und somit in die Zwischenablage einfügen. Dann die Zieltabelle öffnen und versuchen, die Daten einzufügen. Der erste Versuch: einfach wild ein Feld markiert und Strg + V zum Einfügen betätigt. Das gelang natürlich nicht, weil die Struktur des markierten Bereichs nicht mit dem einzufügenden Inhalt übereinstimmt. Wenn Sie dann den kompletten neuen, leeren Datensatz in der Zieltabelle markieren und erneut Strg + V betätigen, klappt es – die Daten landen in der Zieltabelle (s. Bild 1).
Bild 1: Kopieren der Inhalte einer Tabelle in eine baugleiche Tabelle
Schon fertig
Es klappt also allein mit Bordmitteln – das wird ja ein kurzer Beitrag! Doch leider war das nicht alles, denn wir haben hier den Idealfall erwischt. Wenn Sie nun feststellen, dass irgendetwas beim Kopieren nicht wie gewünscht funktioniert hat, etwa weil eines der Zielfelder den falschen Datentyp hat oder weil Sie vielleicht noch mal neu beginnen wollen, weil die Daten der per Fremdschlüsselfeld verknüpften Daten noch gar nicht kopiert wurden, treten die ersten Probleme auf.
Wenn Sie beispielhaft die verknüpfte Tabelle tblAnreden leeren und die Daten erneut kopieren, erhalten Sie beispielsweise das Ergebnis aus Bild 2. Das sieht nun gar nicht mehr so gut aus, denn die Werte des Primärschlüsselfeldes AnredeID entsprechen nicht mehr denen aus der Originaltabelle. Dies wird zu Problemen führen, wenn Sie die Datensätze der Tabelle tblKunden kopieren, deren Fremdschlüsselfeld AnredeID ja die Werte der ursprünglichen Tabelle enthält. Hier wird dann also mit den Datensätzen der Tabelle tblAnreden verknüpft, welche die Werte 1 und 2 enthalten, aber nicht die mit den Werten 5 und 6. Wir müssten also im schlimmsten Fall auch noch die Fremdschlüsselfelder anpassen und auf die neuen Werte mappen.
Bild 2: Erneutes Kopieren liefert andere Primärschlüsselwerte.
Sie sehen also: Wir haben nur zufällig genau die gleichen Daten wie in der Ausgangstabelle erhalten, weil die Autowert-Funktion der Zieltabelle gerade auf den gleichen Startwert wie die einzufügenden Datensätze eingestellt war.
Die Autowert-Funktion der Zieltabelle wird uns noch weitere Probleme bescheren: Sie hat ja üblicherweise die Aufgabe, vom zuletzt eingefügten Wert ausgehend die nächste Ganzzahl als neuen Wert für das Primärschlüsselfeld zu ermitteln. In der Regel werden die Datensätze also von 1 bis n durchnummeriert. Sollten wir also irgendwann einmal einen Datensatz gelöscht haben, folgt das nächste Dilemma: Die Datensätze werden nun ab diesem Loch wiederum anders nummeriert als in der Originaltabelle.
Die Lösung: INSERT INTO
Es gibt leider keine Möglichkeit, Access mitzuteilen, dass es die Werte eines mit Autowert-Funktion ausgestatteten Primärschlüsselfeldes beim Kopieren über die Benutzeroberfläche beibehalten soll. Das Einfügen von Datensätzen auf diese Art und Weise wird Access immer als manuelles Einfügen einzelner Datensätze interpretieren, und somit werden die Autowerte vom System vergeben (in diesem Fall übrigens von der LocalDB-Instanz – siehe Beitrag Access und LocalDB, www.access-im-unternehmen.de/1057). Zum Glück gibt es aber ja auch noch codegesteuerte Möglichkeiten, Datensätze zu kopieren. Also probieren wir es aus!
Also leeren wir die Tabelle tblAnreden1 wieder, um es per VBA zu probieren. Diesmal wollen wir es mit einer INSERT INTO-Abfrage probieren, die einfach nur alle Felder der Quelltabelle in die entsprechenden Felder der Zieltabelle kopiert. Dazu brauchen wir noch nicht einmal alle Felder anzugeben, sondern können das Sternchen (*) als Platzhalter für alle Felder verwenden. Die Abfrage zum Kopieren der Inhalte der Tabelle tblAnreden sieht wie folgt aus:
INSERT INTO tblAnreden1 SELECT * FROM tblAnreden
Der hintere Teil legt fest, welche Daten eingefügt werden sollen, der vordere Teil gibt das Ziel an. Wir erstellen noch eine kleine VBA-Prozedur, welche uns das Ausführen dieser SQL-Anweisung abnimmt und zusätzlich eine Erfolgsmeldung liefert:
Public Sub AnredenKopieren() Dim db As DAO.Database Dim strSQL As String Dim lngAnzahl As Long Set db = CurrentDb strSQL = "INSERT INTO tblAnreden1 SELECT * FROM tblAnreden" db.Execute strSQL, dbFailOnError lngAnzahl = db.RecordsAffected MsgBox "Hinzugefügte Datensätze: " & lngAnzahl End Sub
Die Abfrage speichern wir in der Variablen strSQL. Diese wiederum übergeben wir als ersten Parameter für die Methode Execute des Database-Objekts für die aktuelle Datenbank. Damit Fehler an die Benutzeroberfläche gemeldet werden, geben wir als zweiten Parameter den Wert dbFailOnError mit. Die Eigenschaft RecordsAffected liefert immer die Anzahl der von der letzten Aktionsabfrage betroffenen Datensätze.
Und es gelingt – wir erhalten eine Erfolgsmeldung über zwei angefügte Datensätze und die Tabelle tblAnreden1 zeigt die neuen Datensätze mit den Original-Primärschlüsselwerten an (s. Bild 3).
Bild 3: Mit der INSERT INTO-Anweisung gelingt der Kopiervorgang.
Nun bekommen wir nur noch ein Problem, wenn bereits Datensätze mit den Primärschlüsselwerten der einzufügenden Datensätze vorhanden sind.
Sollten wir also nun beispielsweise erneut die beiden Anreden kopieren, sind ja bereits zwei Datensätze mit den Primärschlüsselwerten 1 und 2 vorhanden – und da das Primärschlüsselfeld immer nur eindeutige Werte enthalten darf, sollte beim Einfügen ein Fehler ausgelöst werden.
Wenn wir dies mit einer lokalen Tabelle, also einer Access-Tabelle, als Ziel durchführen (wir haben diese tblAnreden2 genannt), löst dies den Fehler aus Bild 4 aus.
Bild 4: Fehler beim Einfügen eines Datensatzes mit einem bereits vorhandenen Primärschlüsselwert in eine lokale Tabelle
Handelt es sich bei der Zieltabelle um eine per ODBC verknüpfte Tabelle, wie es bei der Tabelle tblAnreden1 der Fall ist, erscheint die Meldung aus Bild 5. Während wir mit der Meldung für die lokale Tabelle noch etwas anfangen können, liefert diese Fehlermeldung nur wenig wirkliche Informationen. Allerdings können wir noch weitere Hinweise erhalten, wenn wir die VBA-Prozedur, die den Fehler ausgelöst hat, erweitern.
Bild 5: Fehler beim Einfügen eines Datensatzes mit einem bereits vorhandenen Primärschlüsselwert in eine per ODBC verknüpfte Tabelle
Fehlermeldungen aufbohren
Wenn ein Fehler durch einen Zugriff per ODBC auf eine verknüpfte Tabelle entsteht, liefert Access nämlich immer nur die Standardfehlermeldung mit der Nummer 3146. Weitere Informationen liefert dann die Errors-Auflistung des DBEngine-Objekts. Diese enthält auf jeden Fall den auch schon von VBA gemeldeten Fehler 3146, gegebenenfalls aber auch noch weitere Fehler. Im Falle des Anlegens von Daten mit bereits vorhandenem Primärschlüsselwert finden wir beispielsweise mit folgender Anweisung während des Debuggens des Fehlers die Anzahl 3:
Debug.Print DBEngine.Errors.Count
Wir können dann über die Errors-Auflistung direkt auf die Fehlermeldungen der übrigen Fehler zugreifen.
Debug.Print DBEngine.Errors(1).Description
liefert dann beispielsweise einen weiteren Fehler mit dem folgenden Text:
[Microsoft][ODBC Driver 11 for SQL Server][SQL Server]The statement has been terminated.
Und der Fehler mit dem Index 2 lautet so:
[Microsoft][ODBC Driver 11 for SQL Server][SQL Server]Violation of PRIMARY KEY constraint ''''PK_tblAnreden''''. Cannot insert duplicate key in object ''''dbo.tblAnreden''''. The duplicate key value is (1).
Dies nutzen wir aus, indem wir die Prozedur zum Kopieren um eine ausführlichere Fehlerbehandlung ergänzen (s. Listing 1).
Public Sub AnredenKopieren() Dim db As DAO.Database Dim strSQL As String Dim lngAnzahl As Long Dim strFehler As String Dim i As Integer Set db = CurrentDb strSQL = "INSERT INTO tblAnreden1 SELECT * FROM tblAnreden" On Error Resume Next db.Execute strSQL, dbFailOnError Select Case Err.Number Case 3022 MsgBox Err.Description Case 3146 For i = DBEngine.Errors.Count - 1 To 0 Step -1 strFehler = strFehler & DBEngine.Errors(i).Description & vbCrLf & vbCrLf Next i MsgBox strFehler Case 0 lngAnzahl = db.RecordsAffected MsgBox "Hinzugefügte Datensätze: " & lngAnzahl Case Else MsgBox Err.Number & " " & Err.Description End Select On Error GoTo 0 End Sub
Listing 1: Kopieren von Tabellen mit ausführlicher Fehlerbehandlung
Hier benötigen wir zunächst zwei weitere Variablen namens strFehler und i als Laufvariable.
Vor die Execute-Methode setzen wir die Zeile On Error Resume Next, damit eventuell auftretende Fehler nicht über die eingebaute Fehlerbehandlung abgehandelt werden. Danach fragen wir dann in einer Select Case-Bedingung den Wert der Eigenschaft Err.Number ab. Lautet dieser 3146, handelt es sich um einen ODBC-Fehler und wir wollen weitere Informationen ermitteln. Dazu hinterlegen wir eine For…Next-Schleife mit der Laufvariablen i, die alle Elemente der Errors-Auflistung von DBEngine durchläuft und diese nacheinander an die String-Variable strFehler anhängt. Dies liefert dann für den Fehler durch die bereits vorhandenen Primärschlüsselwerte den Fehler aus Bild 6.
Bild 6: ODBC-Fehler mit Hintergrundinformationen
An dieser Stelle muss man sich dann darüber im Klaren sein, welche Datensätze man überschreibt – ob es sich um bereits vorhandene Datensätze handelt, die geschützt werden müssen oder ob die Datensätze überschrieben werden können.
Für den Fall, dass Sie die Daten aus den Tabellen zweier Datenbanken zusammenführen müssen, wäre eine andere Strategie angezeigt als die in diesem Beitrag beschriebene – hier wollen wir nur die kompletten Daten einer Tabelle in eine andere, möglichst leere Tabelle übertragen. Für das Zusammenführen von Daten müssten Sie das Zielsystem die jeweiligen Autowerte bestimmen lassen und die Fremdschlüsselwerte der damit verknüpften Tabellen entsprechend aktualisieren. Dies soll aber nicht Thema dieses Beitrags sein.
Reihenfolge beachten!
Wenn Sie mehrere Tabellen auf die oben beschriebene Weise kopieren möchten, können Sie das nicht in beliebiger Reihenfolge erledigen. Wenn Sie beispielsweise die Daten der Tabellen tblArtikel und der Tabelle tblKategorien und tblLieferanten kopieren (siehe Prozedur ArtikelKopieren im Modul mdlBeispiele_DatenKopieren), und zwar in dieser Reihenfolge, erhalten Sie den Fehler aus Bild 7.
Bild 7: Fehler durch das Füllen von Fremdschlüsselfeldern ohne Vorhandensein der Datensätze mit den entsprechenden Primärschlüsselfeldern
Die Originaltabelle tblArtikel enthält im ersten Datensatz beispielsweise den Wert 1 im Fremdschlüsselfeld LieferantenID. In der verknüpften Tabelle ist aber noch gar kein Datensatz vorhanden, und somit auch keiner mit dem Wert 1 im Primärschlüsselfeld. In der Zieldatenbank ist für dieses Fremdschlüsselfeld jedoch referenzielle Integrität definiert, was bedeutet, dass das Feld LieferantID der Tabelle tblArtikel nur Werte aufnehmen darf, die bereits in der Tabelle tblLieferanten vorhanden sind.
Was tun wir in diesem Fall Ganz einfach: Wir kopieren natürlich zuerst die Tabellen, welche nur einen Primärschlüssel, aber keinen Fremdschlüssel für die Verknüpfung mit anderen Tabellen enthalten. In diesem Fall kopieren wir also zuerst die beiden Tabellen tblLieferanten und tblKategorien, bevor wir die Tabelle tblArtikel kopieren.
Parametrisiertes Kopieren
Und bevor wir nun für jede Tabelle eine neue Kopie der Prozedur AnredenKopieren erstellen und dort nur die Zeile mit der Zuweisung der SQL-Anweisung an die Variable strSQL ändern, übergeben wir die variablen Daten doch lieber per Parameter. Wir verwenden also die beiden Parameter strQuelle und strZiel zur Angabe der jeweiligen Quell- und Zieltabelle. In der Variablen strSQL setzen wir dann die benötigte SQL-Anweisung zusammen. Der aktuelle Stand sieht nun wie in Listing 2 aus.
Public Sub TabelleKopieren(strQuelle As String, strZiel As String) Dim db As DAO.Database Dim lngAnzahl As Long Dim strFehler As String Dim i As Integer Dim strSQL As String Set db = CurrentDb On Error Resume Next strSQL = "INSERT INTO " & strZiel & " SELECT * FROM " & strQuelle db.Execute strSQL, dbFailOnError Select Case Err.Number Case 3022 MsgBox Err.Description Case 3146 For i = DBEngine.Errors.Count - 1 To 0 Step -1 strFehler = strFehler & DBEngine.Errors(i).Description & vbCrLf & vbCrLf Next i MsgBox strFehler Case 0 lngAnzahl = db.RecordsAffected MsgBox "Hinzugefügte Datensätze: " & lngAnzahl Case Else MsgBox Err.Number & " " & Err.Description End Select On Error GoTo 0 End Sub
Listing 2: Kopieren von Tabellen mit Parametern für die SQL-Anweisung
Zum Kopieren etwa der drei Tabellen tblArtikel, tblLieferanten und tblKategorien würden wir folgende Anweisungen aufrufen:
Public Sub Beispiel_TabellenKopieren() Dim db As DAO.Database Set db = CurrentDb db.Execute "DELETE FROM tblArtikel", dbFailOnError db.Execute "DELETE FROM tblKategorien", dbFailOnError db.Execute "DELETE FROM tblLieferanten", dbFailOnError TabelleKopieren "tblKategorien", "tblKategorien1" TabelleKopieren "tblLieferanten", "tblLieferanten1" TabelleKopieren "tblArtikel", "tblArtikel1" End Sub
Da wir diese Prozedur sicher des öfteren testweise aufrufen, ist es sinnvoll, das Löschen der vorhandenen Datensätze in der Zieltabelle zu Beginn durchzuführen. Danach rufen wir die Methode TabelleKopieren für die drei Tabellen auf.
Das sieht dann wie in Listing 3 aus – die zu füllenden Tabellen leeren wir vorab noch, damit der Fehler wegen bereits vorhandener Identitätswerte nicht auftritt.
Public Sub Beispiel_TabellenKopieren() Dim db As DAO.Database Set db = CurrentDb db.Execute "DELETE FROM tblArtikel1", dbFailOnError db.Execute "DELETE FROM tblKategorien1", dbFailOnError + dbSeeChanges db.Execute "DELETE FROM tblLieferanten1", dbFailOnError TabelleKopieren "tblKategorien", "tblKategorien1" TabelleKopieren "tblLieferanten", "tblLieferanten1" TabelleKopieren "tblArtikel", "tblArtikel1" End Sub
Listing 3: Aufruf der Prozedur zum Kopieren von Tabellen
Allerdings erhalten wir nach dem Start für den dritten Aufruf der Prozedur TabelleKopieren den Fehler aus Bild 8. Was heißt das nun wieder
Ende des frei verfügbaren Teil. Wenn Du mehr lesen möchtest, hole Dir ...
den kompletten Artikel im PDF-Format mit Beispieldatenbank
diesen und alle anderen Artikel mit dem Jahresabo