{"id":55001056,"date":"2016-10-01T00:00:00","date_gmt":"2020-05-22T15:25:07","guid":{"rendered":"http:\/\/access-im-unternehmen.aix-dev.de\/aiu\/?p=1056"},"modified":"-0001-11-30T00:00:00","modified_gmt":"-0001-11-30T00:00:00","slug":"Daten_von_Access_zum_SQL_Server_kopieren","status":"publish","type":"post","link":"https:\/\/access-im-unternehmen.de\/Daten_von_Access_zum_SQL_Server_kopieren\/","title":{"rendered":"Daten von Access zum SQL Server kopieren"},"content":{"rendered":"

\"\"<\/p>\n

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.<\/b><\/p>\n

Warum von Tabelle zu Tabelle kopieren<\/h2>\n

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 <\/b>als eingebundene Tabellen (also etwa tblArtikel <\/b>und tblArtikel1<\/b>).<\/p>\n

Kopieren per Drag and Drop<\/h2>\n

Der erste Ansatz, die Tabelleninhalte von A nach B zu kopieren, war der per Drag and Drop. Also die Quelltabelle öffnen, mit Strg + A <\/b>alle Datensätze markieren und mit Strg + C <\/b>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<\/b> 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 <\/b>betätigen, klappt es – die Daten landen in der Zieltabelle (s. Bild 1).<\/p>\n

\"Kopieren<\/p>\n

Bild 1: Kopieren der Inhalte einer Tabelle in eine baugleiche Tabelle<\/span><\/b><\/p>\n

Schon fertig<\/h2>\n

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.<\/p>\n

Wenn Sie beispielhaft die verknüpfte Tabelle tblAnreden<\/b> 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 <\/b>entsprechen nicht mehr denen aus der Originaltabelle. Dies wird zu Problemen führen, wenn Sie die Datensätze der Tabelle tblKunden <\/b>kopieren, deren Fremdschlüsselfeld AnredeID <\/b>ja die Werte der ursprünglichen Tabelle enthält. Hier wird dann also mit den Datensätzen der Tabelle tblAnreden <\/b>verknüpft, welche die Werte 1 <\/b>und 2 <\/b>enthalten, aber nicht die mit den Werten 5 <\/b>und 6<\/b>. Wir müssten also im schlimmsten Fall auch noch die Fremdschlüsselfelder anpassen und auf die neuen Werte mappen.<\/p>\n

\"Erneutes<\/p>\n

Bild 2: Erneutes Kopieren liefert andere Primärschlüsselwerte.<\/span><\/b><\/p>\n

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.<\/p>\n

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.<\/p>\n

Die Lösung: INSERT INTO<\/h2>\n

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<\/b>, www.access-im-unternehmen.de\/1057<\/b>). Zum Glück gibt es aber ja auch noch codegesteuerte Möglichkeiten, Datensätze zu kopieren. Also probieren wir es aus!<\/p>\n

Also leeren wir die Tabelle tblAnreden1 <\/b>wieder, um es per VBA zu probieren. Diesmal wollen wir es mit einer INSERT INTO<\/b>-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<\/b> sieht wie folgt aus:<\/p>\n

INSERT INTO tblAnreden1 SELECT * FROM tblAnreden<\/pre>\n
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:<\/p>\n
Public Sub <\/span>AnredenKopieren()\r\n     Dim <\/span>db As <\/span>DAO.Database\r\n     Dim <\/span>strSQL As String<\/span>\r\n     Dim <\/span>lngAnzahl As Long<\/span>\r\n     Set<\/span> db = CurrentDb\r\n     strSQL = \"INSERT INTO tblAnreden1  SELECT * FROM tblAnreden\"\r\n     db.Execute strSQL, dbFailOnError\r\n     lngAnzahl = db.RecordsAffected\r\n     MsgBox<\/span> \"Hinzugefügte Datensätze: \" & lngAnzahl\r\nEnd Sub<\/span><\/pre>\n
Die Abfrage speichern wir in der Variablen strSQL<\/b>. Diese wiederum übergeben wir als ersten Parameter für die Methode Execute <\/b>des Database<\/b>-Objekts für die aktuelle Datenbank. Damit Fehler an die Benutzeroberfläche gemeldet werden, geben wir als zweiten Parameter den Wert dbFailOnError <\/b>mit. Die Eigenschaft RecordsAffected <\/b>liefert immer die Anzahl der von der letzten Aktionsabfrage betroffenen Datensätze.<\/p>\n
Und es gelingt – wir erhalten eine Erfolgsmeldung über zwei angefügte Datensätze und die Tabelle tblAnreden1<\/b> zeigt die neuen Datensätze mit den Original-Primärschlüsselwerten an (s. Bild 3).<\/p>\n
\"Mit<\/p>\n
Bild 3: Mit der INSERT INTO-Anweisung gelingt der Kopiervorgang.<\/span><\/b><\/p>\n
Nun bekommen wir nur noch ein Problem, wenn bereits Datensätze mit den Primärschlüsselwerten der einzufügenden Datensätze vorhanden sind.<\/p>\n
Sollten wir also nun beispielsweise erneut die beiden Anreden kopieren, sind ja bereits zwei Datensätze mit den Primärschlüsselwerten 1 <\/b>und 2 <\/b>vorhanden – und da das Primärschlüsselfeld immer nur eindeutige Werte enthalten darf, sollte beim Einfügen ein Fehler ausgelöst werden.<\/p>\n
Wenn wir dies mit einer lokalen Tabelle, also einer Access-Tabelle, als Ziel durchführen (wir haben diese tblAnreden2<\/b> genannt), löst dies den Fehler aus Bild 4 aus.<\/p>\n
\"Fehler<\/p>\n
Bild 4: Fehler beim Einfügen eines Datensatzes mit einem bereits vorhandenen Primärschlüsselwert in eine lokale Tabelle<\/span><\/b><\/p>\n
Handelt es sich bei der Zieltabelle um eine per ODBC verknüpfte Tabelle, wie es bei der Tabelle tblAnreden1 <\/b>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.<\/p>\n
\"Fehler<\/p>\n
Bild 5: Fehler beim Einfügen eines Datensatzes mit einem bereits vorhandenen Primärschlüsselwert in eine per ODBC verknüpfte Tabelle<\/span><\/b><\/p>\n
Fehlermeldungen aufbohren<\/h2>\n
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<\/b>. Weitere Informationen liefert dann die Errors<\/b>-Auflistung des DBEngine<\/b>-Objekts. Diese enthält auf jeden Fall den auch schon von VBA gemeldeten Fehler 3146<\/b>, 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<\/b>:<\/p>\n
Debug.Print<\/span> DBEngine.Errors.Count<\/pre>\n
Wir können dann über die Errors<\/b>-Auflistung direkt auf die Fehlermeldungen der übrigen Fehler zugreifen.<\/p>\n
Debug.Print<\/span> DBEngine.Errors(1).Description<\/pre>\n
liefert dann beispielsweise einen weiteren Fehler mit dem folgenden Text:<\/p>\n
[Microsoft][ODBC Driver 11 for SQL Server][SQL Server]The statement has been terminated.<\/pre>\n
Und der Fehler mit dem Index 2 <\/b>lautet so:<\/p>\n
[Microsoft][ODBC Driver 11 for SQL Server][SQL Server]Violation of \r\nPRIMARY KEY constraint ''''PK_tblAnreden''''. Cannot insert duplicate key \r\nin object ''''dbo.tblAnreden''''. The duplicate key value is (1).<\/pre>\n
Dies nutzen wir aus, indem wir die Prozedur zum Kopieren um eine ausführlichere Fehlerbehandlung ergänzen (s. Listing 1).<\/p>\n
Public Sub <\/span>AnredenKopieren()\r\n     Dim <\/span>db As <\/span>DAO.Database\r\n     Dim <\/span>strSQL As String<\/span>\r\n     Dim <\/span>lngAnzahl As Long<\/span>\r\n     Dim <\/span>strFehler As String<\/span>\r\n     Dim <\/span>i As Integer<\/span>\r\n     Set<\/span> db = CurrentDb\r\n     strSQL = \"INSERT INTO tblAnreden1 SELECT * FROM tblAnreden\"\r\n     On Error Resume Next<\/span>\r\n     db.Execute strSQL, dbFailOnError\r\n     Select Case Err.Number\r\n         Case <\/span>3022\r\n             MsgBox<\/span> Err.Description\r\n         Case <\/span>3146\r\n             For i = DBEngine.Errors.Count - 1 To 0 Step -1\r\n                 strFehler = strFehler & DBEngine.Errors(i).Description & vbCrLf<\/span> & vbCrLf<\/span>\r\n             Next<\/span> i\r\n             MsgBox<\/span> strFehler\r\n         Case <\/span>0\r\n             lngAnzahl = db.RecordsAffected\r\n             MsgBox<\/span> \"Hinzugefügte Datensätze: \" & lngAnzahl\r\n         Case Else<\/span>\r\n             MsgBox<\/span> Err.Number & \" \" & Err.Description\r\n     End Select<\/span>\r\n     On Error GoTo<\/span> 0\r\nEnd Sub<\/span><\/pre>\n
Listing 1: Kopieren von Tabellen mit ausführlicher Fehlerbehandlung<\/span><\/b><\/p>\n
Hier benötigen wir zunächst zwei weitere Variablen namens strFehler <\/b>und i <\/b>als Laufvariable.<\/p>\n
Vor die Execute<\/b>-Methode setzen wir die Zeile On Error Resume Next<\/b>, damit eventuell auftretende Fehler nicht über die eingebaute Fehlerbehandlung abgehandelt werden. Danach fragen wir dann in einer Select Case<\/b>-Bedingung den Wert der Eigenschaft Err.Number <\/b>ab. Lautet dieser 3146<\/b>, handelt es sich um einen ODBC-Fehler und wir wollen weitere Informationen ermitteln. Dazu hinterlegen wir eine For…Next<\/b>-Schleife mit der Laufvariablen i<\/b>, die alle Elemente der Errors<\/b>-Auflistung von DBEngine <\/b>durchläuft und diese nacheinander an die String<\/b>-Variable strFehler <\/b>anhängt. Dies liefert dann für den Fehler durch die bereits vorhandenen Primärschlüsselwerte den Fehler aus Bild 6.<\/p>\n
\"ODBC-Fehler<\/p>\n
Bild 6: ODBC-Fehler mit Hintergrundinformationen<\/span><\/b><\/p>\n
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.<\/p>\n
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.<\/p>\n
Reihenfolge beachten!<\/h2>\n
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 <\/b>und der Tabelle tblKategorien <\/b>und tblLieferanten <\/b>kopieren (siehe Prozedur ArtikelKopieren <\/b>im Modul mdlBeispiele_DatenKopieren<\/b>), und zwar in dieser Reihenfolge, erhalten Sie den Fehler aus Bild 7.<\/p>\n
\"Fehler<\/p>\n
Bild 7: Fehler durch das Füllen von Fremdschlüsselfeldern ohne Vorhandensein der Datensätze mit den entsprechenden Primärschlüsselfeldern<\/span><\/b><\/p>\n
Die Originaltabelle tblArtikel <\/b>enthält im ersten Datensatz beispielsweise den Wert 1 <\/b>im Fremdschlüsselfeld LieferantenID<\/b>. In der verknüpften Tabelle ist aber noch gar kein Datensatz vorhanden, und somit auch keiner mit dem Wert 1 <\/b>im Primärschlüsselfeld. In der Zieldatenbank ist für dieses Fremdschlüsselfeld jedoch referenzielle Integrität definiert, was bedeutet, dass das Feld LieferantID <\/b>der Tabelle tblArtikel <\/b>nur Werte aufnehmen darf, die bereits in der Tabelle tblLieferanten <\/b>vorhanden sind.<\/p>\n
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 <\/b>und tblKategorien<\/b>, bevor wir die Tabelle tblArtikel <\/b>kopieren. <\/p>\n
Parametrisiertes Kopieren<\/h2>\n
Und bevor wir nun für jede Tabelle eine neue Kopie der Prozedur AnredenKopieren <\/b>erstellen und dort nur die Zeile mit der Zuweisung der SQL-Anweisung an die Variable strSQL <\/b>ändern, übergeben wir die variablen Daten doch lieber per Parameter. Wir verwenden also die beiden Parameter strQuelle <\/b>und strZiel <\/b>zur Angabe der jeweiligen Quell- und Zieltabelle. In der Variablen strSQL <\/b>setzen wir dann die benötigte SQL-Anweisung zusammen. Der aktuelle Stand sieht nun wie in Listing 2 aus.<\/p>\n
Public Sub <\/span>TabelleKopieren(strQuelle As String<\/span>, strZiel As String<\/span>)\r\n     Dim <\/span>db As <\/span>DAO.Database\r\n     Dim <\/span>lngAnzahl As Long<\/span>\r\n     Dim <\/span>strFehler As String<\/span>\r\n     Dim <\/span>i As Integer<\/span>\r\n     Dim <\/span>strSQL As String<\/span>\r\n     Set<\/span> db = CurrentDb\r\n     On Error Resume Next<\/span>\r\n     strSQL = \"INSERT INTO \" & strZiel & \" SELECT * FROM \" & strQuelle\r\n     db.Execute strSQL, dbFailOnError\r\n     Select Case Err.Number\r\n         Case <\/span>3022\r\n             MsgBox<\/span> Err.Description\r\n         Case <\/span>3146\r\n             For i = DBEngine.Errors.Count - 1 To 0 Step -1\r\n                 strFehler = strFehler & DBEngine.Errors(i).Description & vbCrLf<\/span> & vbCrLf<\/span>\r\n             Next<\/span> i\r\n             MsgBox<\/span> strFehler\r\n         Case <\/span>0\r\n             lngAnzahl = db.RecordsAffected\r\n             MsgBox<\/span> \"Hinzugefügte Datensätze: \" & lngAnzahl\r\n         Case Else<\/span>\r\n             MsgBox<\/span> Err.Number & \" \" & Err.Description\r\n     End Select<\/span>\r\n     On Error GoTo<\/span> 0\r\nEnd Sub<\/span><\/pre>\n
<\/p>\n
Listing 2: Kopieren von Tabellen mit Parametern für die SQL-Anweisung<\/span><\/b><\/p>\n
Zum Kopieren etwa der drei Tabellen tblArtikel<\/b>, tblLieferanten <\/b>und tblKategorien <\/b>würden wir folgende Anweisungen aufrufen:<\/p>\n
Public Sub <\/span>Beispiel_TabellenKopieren()\r\n     Dim <\/span>db As <\/span>DAO.Database\r\n     Set<\/span> db = CurrentDb\r\n     db.Execute \"DELETE FROM tblArtikel\", dbFailOnError\r\n     db.Execute \"DELETE FROM tblKategorien\", dbFailOnError\r\n     db.Execute \"DELETE FROM tblLieferanten\", dbFailOnError\r\n     TabelleKopieren \"tblKategorien\", \"tblKategorien1\"\r\n     TabelleKopieren \"tblLieferanten\", \"tblLieferanten1\"\r\n     TabelleKopieren \"tblArtikel\", \"tblArtikel1\"\r\nEnd Sub<\/span><\/pre>\n
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 <\/b>für die drei Tabellen auf.<\/p>\n
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.<\/p>\n
Public Sub <\/span>Beispiel_TabellenKopieren()\r\n     Dim <\/span>db As <\/span>DAO.Database\r\n     Set<\/span> db = CurrentDb\r\n     db.Execute \"DELETE FROM tblArtikel1\", dbFailOnError\r\n     db.Execute \"DELETE FROM tblKategorien1\", dbFailOnError + dbSeeChanges\r\n     db.Execute \"DELETE FROM tblLieferanten1\", dbFailOnError\r\n     TabelleKopieren \"tblKategorien\", \"tblKategorien1\"\r\n     TabelleKopieren \"tblLieferanten\", \"tblLieferanten1\"\r\n     TabelleKopieren \"tblArtikel\", \"tblArtikel1\"\r\nEnd Sub<\/span><\/pre>\n
Listing 3: Aufruf der Prozedur zum Kopieren von Tabellen<\/span><\/b><\/p>\n
Allerdings erhalten wir nach dem Start für den dritten Aufruf der Prozedur TabelleKopieren<\/b> den Fehler aus Bild 8. Was heißt das nun wieder<\/p>\n
\"Fehler<\/p>\n
Bild 8: Fehler durch die Eigenschaft IDENTITY_INSERT<\/span><\/b><\/p>\n
Cannot insert explicit value for identity column in table ''''tblArtikel'''' \r\nwhen IDENTITY_INSERT is set to OFF.<\/pre>\n
Eine kleine Recherche nach dem Schlüsselwort IDENTITY_INSERT <\/b>ergibt, dass es normalerweise nicht vorgesehen ist, dass man die Identitätswerte eines Datensatzes selbst vergibt. Diese SQL Server-Eigenschaft hat normalerweise für alle Tabellen den Wert OFF<\/b>. Sie kann nur für eine Tabelle gleichzeitig den Wert ON <\/b>annehmen.<\/p>\n
Wenn sie den Wert ON <\/b>besitzt, muss man den Wert der Identitätsspalte mit angeben. Das hört sich logisch an – man kann so per T-SQL temporär dafür sorgen, dass man individuelle Identitätswerte eintragen kann, wenn diese sonst automatisch vom System vergeben werden. Allerdings sind unsere Tests nicht so eindeutig. Wenn Sie beispielsweise die Prozedur Beispiel_TabelleKopieren <\/b>ausführen, werden die ersten beiden Tabellen mit den Daten gefüllt, ohne dass wir an irgendeiner Stelle IDENTITY_INSERT <\/b>auf ON <\/b>stellen. Erst bei der dritten Tabelle, hier tblArtikel<\/b>, tritt der Fehler auf. <\/p>\n
Noch verwirrender wird es, wenn wir noch einen Kopiervorgang für die Tabelle tblAnreden <\/b>voranstellen:<\/p>\n
TabelleKopieren \"tblAnreden\", \"tblAnreden1\"\r\nTabelleKopieren \"tblKategorien\", \"tblKategorien1\"\r\nTabelleKopieren \"tblLieferanten\", \"tblLieferanten1\"\r\nTabelleKopieren \"tblArtikel\", \"tblArtikel1\"<\/pre>\n
Dann gelingt das Einfügen in die Tabelle tblAnreden1<\/b>, bei tblKategorien1 <\/b>erscheint der obige Fehler, das Einfügen in tblLieferanten1 <\/b>gelingt wieder und bei tblArtikel1 <\/b>gibt es wieder den Fehler.<\/p>\n
Wenn wir die Datenbank schließen und wieder öffnen, sieht das Ergebnis mitunter noch anders aus. Wie können wir also sicherstellen, dass wir die Daten inklusive Identitätswerten in die SQL Server-Tabellen übertragen<\/p>\n
IDENTITY_INSERT im SQL Server Management Studio<\/h2>\n
Schauen wir uns an, ob es an Access liegt, und testen ein paar SQL-Anweisungen direkt im SQL Server Management Studio. Wenn wir die folgende Anweisung absetzen, erhalten wir den gleichen Fehler wie unter Access:<\/p>\n
INSERT INTO tblAnreden(AnredeID, Anrede) VALUES(3,''''bla'''');<\/pre>\n
Wir müssen zunächst angeben, dass für diese Tabelle Identitätswerte individuell geschrieben werden sollen, und zwar mit folgender Anweisung:<\/p>\n
SET IDENTITY_INSERT dbo.tblAnreden ON;<\/pre>\n
Danach können wir dann mit der zuvor verwendeten Anweisung einen neuen Datensatz zur Tabelle tblAnreden <\/b>hinzufügen.<\/p>\n
Hier ist das Bild also etwas eindeutiger: Wenn wir nicht zuvor IDENTITY_INSERT <\/b>für die betroffene Tabelle auf ON <\/b>einstellen, können wir keine individuellen Identitätswerte festlegen. Unter Access scheint dies also in manchen Fällen etwas aufgeweicht zu werden, wobei wir nicht wissen, in welchen Fällen.<\/p>\n
Auch beeinflusst das Setzen von IDENTITY_INSERT <\/b>vom SQL Server Management Studio aus nicht das Verhalten beim Schreiben von Daten unter Access – es scheint also sitzungsbezogen zu sein.<\/p>\n
Wenn wir also im SQL Server Management Studio IDENTITY_INSERT <\/b>für die Tabelle tblKategorien <\/b>auf ON <\/b>setzen, können wir noch lange keine Datensätze mit individuellen Identitätswerten per ODBC von Access aus hinzufügen.<\/p>\n
Also versuchen wir, einen Weg zu finden, von Access aus zuvor anzugeben, dass die jeweilige Tabelle mit individuellen Identitätswerten gefüllt werden soll. Leider gelingt dies nicht wie gewünscht: Wir können zwar eine Pass-Through-Abfrage der folgenden Art absetzen, was erfolgreich verläuft:<\/p>\n
SET IDENTITY_INSERT tblAnreden ON\r\nINSERT INTO tblAnreden(AnredeID, Anrede) VALUES(101, ''''Test'''')\r\nSET IDENTITY_INSERT tblAnreden OFF<\/pre>\n
Allerdings legen wir damit nur einen Datensatz an, dessen Werte wir direkt in der Abfrage explizit festlegen. Wir wollen aber die Daten der lokalen Tabelle in die LocalDB-Tabelle schreiben. Auf die lokalen Tabellen erhalten wir aber in einer Pass-Through-Abfrage keinen Zugriff.<\/p>\n
Experimente<\/h2>\n
Danach folgten zahlreiche Experimente, wie wir die Daten aus den sieben Access-Tabellen in die entsprechenden Tabellen des SQL Servers kopieren können – unter Beibehaltung des Identitätswertes wohlgemerkt, denn das ist ja das große Problem dabei. Wenn Sie einfach nur die Daten kopieren wollten, ohne diese Werte beizubehalten, wäre das zwar grundsätzlich einfacher möglich, allerdings müssten Sie dann wiederum beim Kopieren von Tabellen mit Fremdschlüsselwerten zunächst herausfinden, wie sich die Primärschlüsselwerte der verknüpften Tabellen beim Anfügen geändert haben.<\/p>\n
Das Ergebnis dieser Experimente ist folgende Vorgehensweise (s. Bild 9): Wir erstellen für jeden einzelnen Datensatz einen Satz von SQL-Anweisungen, der etwa wie folgt aussieht:<\/p>\n
\"SQL-Anweisungen,<\/p>\n
Bild 9: SQL-Anweisungen, um einen Datensatz unter Beibehaltung des Identitätswertes zu kopieren<\/span><\/b><\/p>\n
SET IDENTITY_INSERT tblAnreden ON\r\nSET NOCOUNT ON\r\nINSERT INTO tblAnreden(AnredeID, Anrede) VALUES(1, ''''Herr'''')\r\nSELECT @@ROWCOUNT AS AnzahlEingefuegt\r\nSET IDENTITY_INSERT tblAnreden OFF<\/pre>\n
Diesen senden wir dann per ODBC an den SQL Server beziehungsweise LocalDB und lassen diesen ausführen. Warum für jeden Datensatz einzeln Das hat sich so entwickelt: Zuerst durchlief die Routine, die dies erledigt, eine Schleife, die alle INSERT INTO<\/b>-Aufrufe für eine Tabelle zusammenstellt und diese dann im Anschluss an den SQL Server schickte. Allerdings traten dabei sehr viele Fehler aus unterschiedlichen Gründen auf – mal war ein Hochkomma zu viel, dann war das Datum falsch formatiert oder ein Feld, das keine Nullwerte annehmen durfte, wurde mit einem Nullwert beschickt. Also haben wir, um die Fehler nach und nach auszumerzen, nicht alle INSERT INTO<\/b>-Anweisungen für eine Tabelle in einem Rutsch an den Server geschickt sondern nur jeweils eine. Auf diese Weise ließen sich die Fehlermeldungen einfacher auswerten. Solange die Datenmenge in akzeptabler Ausführungszeit kopiert werden kann, ist dieser Weg in Ordnung; wenn es zu langsam wird, kann man die Schleife allerdings auch wieder umgestalten – also so, dass in der Schleife nur die INSERT INTO<\/b>-Elemente zusammengestellt werden.<\/p>\n
Kopieren per ODBC<\/h2>\n
Die Prozedur TabelleKopieren_IDENTITY_INSERT <\/b>erwartet Parameter (s. Listing 4):<\/p>\n
Public Sub <\/span>TabelleKopieren_IDENTITY_INSERT(strQuelle As String<\/span>, strZiel As String<\/span>)\r\n     Dim <\/span>db As <\/span>DAO.Database\r\n     Dim <\/span>lngAnzahl As Long<\/span>\r\n     Dim <\/span>strFehler As String<\/span>, strSQL As String<\/span>\r\n     Dim <\/span>i As Integer<\/span>\r\n     Dim <\/span>qdf As <\/span>DAO.QueryDef\r\n     Dim <\/span>rst As <\/span>DAO.Recordset, rstDatensaetze As <\/span>DAO.Recordset\r\n     Dim <\/span>strFelder As String<\/span>\r\n     Dim <\/span>strWerte As String<\/span>\r\n     Dim <\/span>strInsert As String<\/span>\r\n     Set<\/span> db = CurrentDb\r\n     db.QueryDefs.Delete \"qryTemp\"\r\n     On Error GoTo<\/span> 0\r\n     Set<\/span> qdf = db.CreateQueryDef(\"qryTemp\")\r\n     qdf.Connect = DLookup(\"Verbindungszeichenfolge\", \"tblVerbindungszeichenfolgen\", _\r\n         \"VerbindungszeichenfolgeID = 9\")\r\n     Set<\/span> rstDatensaetze = db.OpenRecordset(strQuelle, dbOpenDynaset)\r\n     strFelder = FelderErmitteln(db, strQuelle)\r\n     Do While<\/span> Not<\/span> rstDatensaetze.EOF\r\n         strSQL = \"SET IDENTITY_INSERT \" & strZiel & \" ON\" & vbCrLf<\/span>\r\n         strWerte = WerteErmitteln(rstDatensaetze)\r\n         strInsert = \"INSERT INTO \" & strZiel & \"(\" & strFelder & \") VALUES(\" & strWerte & \")\" & vbCrLf<\/span>\r\n         strSQL = strSQL & \"SET NOCOUNT ON\" & vbCrLf<\/span>\r\n         strSQL = strSQL & strInsert\r\n         strSQL = strSQL & \"SELECT @@ROWCOUNT AS AnzahlEingefuegt\" & vbCrLf<\/span>\r\n         strSQL = strSQL & \"SET IDENTITY_INSERT \" & strZiel & \" OFF\" & vbCrLf<\/span>\r\n         qdf.SQL = strSQL\r\n         qdf.ReturnsRecords = True<\/span>\r\n         On Error Resume Next<\/span>\r\n         Set<\/span> rst = qdf.OpenRecordset\r\n         Select Case Err.Number\r\n             Case <\/span>3146\r\n                 For i = DBEngine.Errors.Count - 1 To 0 Step -1\r\n                     strFehler = strFehler & DBEngine.Errors(i).Description & vbCrLf<\/span> & vbCrLf<\/span>\r\n                 Next<\/span> i\r\n                 Debug.Print<\/span> \"Tabelle: \" & strQuelle\r\n                 Debug.Print<\/span> strFehler\r\n             Case <\/span>0\r\n                 lngAnzahl = lngAnzahl + rst.Fields(0)\r\n             Case Else<\/span>\r\n                 MsgBox<\/span> Err.Number & \" \" & Err.Description\r\n         End Select<\/span>\r\n         On Error GoTo<\/span> 0\r\n         rstDatensaetze.MoveNext<\/span>\r\n     Loop<\/span>\r\n     Debug.Print<\/span> \"Hinzugefügte Datensätze in Tabelle ''''\" & strZiel & \"'''': \" & lngAnzahl\r\nEnd Sub<\/span><\/pre>\n
Listing 4: Prozedur zum Zusammenstellen von INSERT INTO-Anweisungen<\/span><\/b><\/p>\n