Im Beitrag „Anlage-Feld zum SQL Server migrieren“ (www.access-im-unternehmen.de\/1542) haben wir gezeigt, wie Inhalte von Anlagefeldern einer Access-Datenbank in ein varbinary(max)-Feld einer SQL Server-Datenbank übertragen werden können. Dort sind wir davon ausgegangen, dass jedes Anlagefeld immer nur eine Datei enthält, was in vielen Fällen ausreichend ist. Was aber, wenn der Entwickler das Anlagefeld für mehrere Dateien vorgesehen hat – beispielsweise, um ein oder mehrere Produktbilder zu einem Produkt zu speichern? In diesem Fall müssen wir umdenken, denn wir können optimalerweise immer nur eine Datei in einem varbinary(max)-Feld speichern. Zum Speichern mehrerer Dateien müssen wir uns also einen Workaround überlegen. Wie dieser aussieht, schauen wir uns im vorliegenden Beitrag an.<\/b><\/p>\n
Anlagefelder mit einem oder mehreren Dateien<\/h2>\n
Anlagefelder speichern ihre Anlagen in einer internen Tabelle, die von außen erst einmal nicht sichtbar ist. Deshalb ist es auch möglich, dass für einen einzelnen Datensatz mehr als eine Anlage hinterlegt werden kann. <\/p>\n
Im Beispiel verwenden wir die Tabelle tblProdukte<\/b>, die in der Entwurfsansicht wie in Bild 1 aussieht.<\/p>\n
![\"Tabelle](\"..\/fileadmin\/_temp_\/2025_03\/pic_1546_001.png\")
<\/p>\n
Bild 1: Tabelle zum Speichern von Produkten mit Bildern<\/span><\/b><\/p>\n In der Datenblattansicht haben wir der Tabelle drei Bilder zum Anlagefeld hinzugefügt (siehe Bild 2).<\/p>\n Bild 2: Tabelle mit mehreren Anlagen<\/span><\/b><\/p>\n Dass es in einem Anlagefeld ein Objekt wie eine Tabelle geben muss, sehen wir, wenn wir eine Tabelle mit einem Anlagefeld in eine neue, leere Abfrage ziehen. Hier fügen wir einmal alle Felder der Tabelle einschließlich der Felder des Anlagefeldes zum Entwurfsraster hinzu (siehe Bild 3).<\/p>\n Bild 3: Die Untertabelle in einem Anlagefeld<\/span><\/b><\/p>\n Wechseln wir hier in die Datenblattansicht, sehen wir, dass die Idee, dass wir es hier mit einer internen Untertabelle zu tun haben, nicht allzu abwegig ist. Aus einem Datensatz werden nun direkt drei Datensätze, was sonst nur geschieht, wenn wir eine verknüpfte Tabelle mit in den Abfrageentwurf ziehen (siehe Bild 4).<\/p>\n Bild 4: Die Felder eines Anlagefeldes in der Datenblattansicht<\/span><\/b><\/p>\n Betrachten wir nun die Aufgabe, die Inhalte einer solchen Tabelle in eine SQL Server-Datenbank zu übertragen, sehen wir erst einmal kein derartiges Konstrukt, indem wir mehrere Datensätze pro Feld speichern könnten. Letztlich ist das, was wir hier abgebildet sehen, nichts anderes als zwei Tabellen, die per 1:n-Beziehung miteinander verknüpft sind. Und das lässt sich wiederum auch im SQL Server abbilden – wenn auch mit Unterstützung durch eine weitere Tabelle.<\/p>\n Wir erstellen also im SQL Server zwei Tabellen, um die Daten dieser einen Tabelle mit Anlagefeld abzubilden. Diese sehen wie folgt aus:<\/p>\n Um die Tabellen schnell zu erstellen, können wir das folgende Skript nutzen:<\/p>\n Danach können wir uns an die Erstellung des Codes zum Übertragen der Daten samt Bilddaten begeben.<\/p>\n Im Beitrag Anlage-Feld zum SQL Server migrieren <\/b>(www.access-im-unternehmen.de\/1542<\/b>) haben wir bereits eine Funktion programmiert, die wir komplett über Parameter steuern können.<\/p>\n Im vorliegenden Beitrag benötigen wir eine ähnliche Prozedur, die allerdings noch einige weitere Parameter benötigt. Immerhin sollen die Daten aus ursprünglich einer Tabelle nun auf zwei Tabellen aufgeteilt werden.<\/p>\n Zuvor legen wir fest, wie wir hier vorgehen wollen. Möchten wir eine ähnliche Prozedur schreiben, wie wir sie im oben genannten Beitrag angelegt haben, die für eine bereits bestehende Migration inklusive Daten nur noch die Inhalte der Anlagefelder hinterherkopiert? Dies ist in den Fällen interessant, wo man die übrige Migration bereits vollständig durchgeführt hat, beispielsweise mit einem Tool wie SQL Server Migration Assistant.<\/p>\n In diesem Beitrag wollen wir jedoch eine Lösung kreieren, welche die Inhalte der Tabellen vollständig überträgt.<\/p>\n Wir gehen also von den leeren Tabellen tblProdukte <\/b>und tblProduktbilder <\/b>auf dem SQL Server aus und wollen alle enthaltenen Informationen dorthin übertragen.<\/p>\n Wir werden also in den folgenden Schritten vorgehen:<\/p>\n Für die Parametrisierung benötigen wir also die folgenden Daten:<\/p>\n Den Start machen wir mit der Prozedur TabelleMitMehrfachanlagenZumSQLServer<\/b> (siehe Listing 1). Diese erstellt einen Verweis auf das aktuelle Database<\/b>-Objekt sowie eine Connection auf die entsprechende SQL Server-Datenbank.<\/p>\n Listing 1: Aufruf der Funktionen zum Kopieren von Tabellen mit Anlagefeldern mit mehreren Dateien<\/span><\/b><\/p>\n Die dafür notwendige Verbindungszeichenfolge haben wir in diesem Fall wie folgt in einer Konstanten hinterlegt:<\/p>\n Die Verbindung öffnet die Prozedur dann mit der Open<\/b>-Methode. Außerdem füllen wir ein Recordset mit den Datensätze der Tabelle tblProdukte <\/b>und durchlaufen dieses in einer Do While<\/b>-Schleife über alle Datensätze.<\/p>\n Darin kopieren wir zunächst die Daten dieser Tabelle mit Ausnahme des Anlagefeldes. Dazu erstellen wir eine entsprechende INSERT INTO<\/b>-Anweisung.<\/p>\n Diese wollen wir mithilfe eines Command<\/b>-Objekts ausführen, dem wir die Verbindung aus cnn <\/b>zuweisen und für das wir die INSERT INTO<\/b>-Anweisung als CommandText <\/b>hinterlegen.<\/p>\n Die INSERT INTO<\/b>-Anweisung hat die Besonderheit, dass wir in ihr mit OUTPUT INSERTED.ProduktID <\/b>direkt den Primärschlüsselwert des neu erstellten Datensatzes ermitteln. Um diesen später auszulesen, speichern wir das Ergebnis der Execute<\/b>-Methode in einem Recordset namens rstResults<\/b>.<\/p>\n Mit dieser ProduktID <\/b>ausgestattet rufen wir die Funktion CopyAttachmentToVarBinaryMax_Multi <\/b>auf. Dieser übergeben wir die oben genannten Parameter für das Kopieren der Datensätze der Tabelle tblProdukte <\/b>in die beiden Tabellen tblProdukte <\/b>und tblProduktbilder <\/b>auf dem SQL Server.<\/p>\n War die Ausführung dieser Funktion und der von dieser aufgerufenen Unterfunktionen erfolgreich, erscheint eine entsprechende Meldung.<\/p>\n Auf diese Weise werden alle Datensätze durchlaufen und zum SQL Server übertragen.<\/p>\n Die Funktion CopyAttachmentToVarBinaryMax_Multi <\/b>ist die Steuerzentrale des Vorgangs (siehe Listing 2). Sie erstellt ein Recordset auf Basis der Tabelle mit den zu kopierenden Daten des aktuellen Datensatzes der aufrufenden Tabelle inklusive Anlagen. Außerdem erstellt sie ein weiteres Recordset namens rstAttachments<\/b>, das die Datensätze in der internen Tabelle mit den Anlagen des Feldes Produktbilder <\/b>enthält.<\/p>\n Listing 2: Funktion zum Kopieren der Attachments einer Tabelle in eine SQL Server-Tabelle<\/span><\/b><\/p>\n Sie durchläuft nun alle Datensätze des Recordsets rstAttachments<\/b>, also alle Anlagen des aktuellen Datensatzes, in einer Do While<\/b>-Schleife. Darin ruft sie zunächst die Funktion SaveAttachmentToFile_Multi <\/b>auf, die dafür sorgt, dass das Attachment des aktuellen Datensatzes des Recordsets rstAttachments <\/b>im Dateisystem gespeichert wird und den Namen der angelegten Datei zurückliefert. <\/p>\n War dieser Vorgang erfolgreich, ruft die Funktion CopyAttachmentToVarBinaryMax_Multi <\/b>die nächste Funktion LoadFileToByteArray <\/b>auf. Diese lädt die zuvor im Dateisystem gespeicherte Datei in ein Byte-Array, das als Parameter zurückgegeben wird. War auch dies erfolgreich, folgt die nächste Funktion namens AddByteArrayToVarBinaryMax_Multi<\/b>, die schließlich den Inhalt des Byte-Arrays in das varbinary(max)<\/b>-Feld der Zieltabelle tblProduktbilder <\/b>speichert. Für jeden erfolgreichen Vorgang wird der Zähler lngCopycount <\/b>um 1 erhöht, damit wir anschließend prüfen können, ob alle Anlagen erfolgreich gespeichert wurden.<\/p>\n Entspricht nach dem Durchlaufen aller Attachments der Wert aus lngCopycount <\/b>der Anzahl der Datensätze aus rstAttachments<\/b>, war der Vorgang insgesamt erfolgreich und die Funktion gibt den Wert True <\/b>zurück. Anderenfalls erscheint eine entsprechende Fehlermeldung.<\/p>\n Die oben erwähnte Funktion SaveAttachmentToFile_Multi <\/b>ermittelt den Dateinamen aus dem Feld Filename <\/b>des übergebenen Recordsets mit den Anlagen und liest daraus die Dateiendung aus (siehe Listing 3). Mit dem aktuellen Datenbankpfad, dem Dateinamen temp <\/b>und der Dateiendung wird der Dateipfad zusammengesetzt.<\/p>\n Listing 3: Funktion zum Speichern der Attachments in einer Datei<\/span><\/b><\/p>\n Die Prozedur durchläuft anschließend solange eine Do While<\/b>-Schleife, in der sie die eventuell bereits unter diesem Namen vorhandene Datei löscht, bis diese nicht mehr vorhanden ist. Anschließend erstellt sie diese mit der SaveToFile<\/b>-Methode aus dem aktuellen Anlagefeld neu.<\/p>\n Die Funktion LoadFileToByteArray <\/b>aus Listing 4 dient dazu, ein ADODB.Stream<\/b>-Objekt zu erstellen und dieses mit dem Inhalt aus der zuvor gespeicherten Datei zu füllen.<\/p>\n Listing 4: Funktion zum Einlesen des Attachments aus einer Datei in ein Byte-Array<\/span><\/b><\/p>\n Dazu nutzt sie die LoadFromFile<\/b>-Methode des Stream<\/b>-Objekts und gibt das Byte<\/b>-Array über den Rückgabeparameter bytData <\/b>zurück.<\/p>\n Die letzte Funktion namens AddByteArrayToVarBinaryMax_Multi <\/b>aus Listing 5 dient dazu, den Inhalt des Byte-Arrays im Anlagefeld eines neuen Datensatzes der Tabelle tblProduktbilder <\/b>zu speichern. Dazu erstellt sie ein neues Command<\/b>-Objekt namens cmd<\/b>.<\/p>\n![\"Tabelle](\"..\/fileadmin\/_temp_\/2025_03\/pic_1546_002.png\")
<\/p>\n![\"Die](\"..\/fileadmin\/_temp_\/2025_03\/pic_1546_003.png\")
<\/p>\n![\"Die](\"..\/fileadmin\/_temp_\/2025_03\/pic_1546_004.png\")
<\/p>\nAnlagen mit mehreren Dateien zum SQL Server migrieren<\/h2>\n
\n
CREATE TABLE tblProdukte ( ProduktID INT IDENTITY(1,1) PRIMARY KEY, Produkt NVARCHAR(255) NOT NULL);CREATE TABLE tblProduktbilder ( ProduktbildID INT IDENTITY(1,1) \r\n PRIMARY KEY, ProduktID INT NOT NULL, Produktbild VARBINARY(MAX) NOT NULL, CONSTRAINT FK_Produktbilder_Produkte FOREIGN KEY (ProduktID) REFERENCES tblProdukte (ProduktID) ON DELETE CASCADE);<\/pre>\n
Notwendiger Parameter zum Übertragen der Tabelle inklusive Anlagefeld<\/h2>\n
\n
\n
Aufruf der Funktionen<\/h2>\n
Public Sub <\/span>TabelleMitMehrfachanlagenZumSQLServer()\r\n Dim <\/span>db As <\/span>DAO.Database\r\n Dim <\/span>cnn As <\/span>ADODB.Connection\r\n Dim <\/span>rst As <\/span>DAO.Recordset\r\n Dim <\/span>strErrorMessage As String<\/span>\r\n Dim <\/span>strSQL As String<\/span>\r\n Dim <\/span>cmd As <\/span>ADODB.Command\r\n Dim <\/span>rstResult As <\/span>ADODB.Recordset\r\n Dim <\/span>lngID As Long<\/span>\r\n Dim <\/span>bolKopiert As Boolean<\/span>\r\n Set<\/span> db = CurrentDb\r\n Set<\/span> cnn = New<\/span> ADODB.Connection\r\n cnn.Open cStrConnection\r\n Set<\/span> rst = db.OpenRecordset(\"SELECT * FROM tblProdukte\", dbOpenDynaset)\r\n Do While<\/span> Not<\/span> rst.EOF\r\n strSQL = \"INSERT INTO tblProdukte(Produkt) OUTPUT INSERTED.ProduktID VALUES(''\" & rst!Produkt & \"'');\"\r\n Set<\/span> cmd = New<\/span> ADODB.Command\r\n cmd.ActiveConnection = cnn\r\n cmd.CommandText = strSQL\r\n cmd.CommandType = adCmdText\r\n Set<\/span> rstResult = cmd.Execute()\r\n If <\/span>Not<\/span> rstResult.EOF Then<\/span>\r\n lngID = rstResult!ProduktID\r\n bolKopiert = CopyAttachmentToVarBinaryMax_Multi(\"tblProdukte\", \"Produktbilder\", \"ProduktID\", _\r\n rst!ProduktID, lngID, \"tblProduktbilder\", \"Produktbild\", \"ProduktID\", cnn, db, strErrorMessage)\r\n If <\/span>bolKopiert = True<\/span> Then<\/span>\r\n MsgBox<\/span> \"Datensatz \" & rst!ProduktID & \" erfolgreich kopiert.\"\r\n Else<\/span>\r\n MsgBox<\/span> \"Datensatz \" & rst!ProduktID & \" nicht kopiert.\" & vbCrLf<\/span> & vbCrLf<\/span> & strErrorMessage, _\r\n vbCr<\/span>itical + vbOKOnly, \"Fehler beim Kopieren\"\r\n End If<\/span>\r\n End If<\/span>\r\n rst.MoveNext<\/span>\r\n Loop<\/span>\r\n rst.Close\r\n Set<\/span> rst = Nothing\r\n Set<\/span> db = Nothing\r\n cnn.Close\r\n Set<\/span> cnn = Nothing\r\nEnd Sub<\/span><\/pre>\nPublic <\/span>Const cStrConnection As String<\/span> = \"Provider=MSOLEDBSQL;Data Source=amvdesktop2023;Initial Catalog=Anlagen;Integrated Security=SSPI;\"<\/pre>\nFunktion zum Durchführen des Kopiervorgangs<\/h2>\n
Public Function <\/span>CopyAttachmentToVarBinaryMax_Multi(strAccessTable As String<\/span>, strAccessField As String<\/span>, _\r\n strAccessPKField As String<\/span>, lngAccessPKID As Long<\/span>, lngSQLPKID As Long<\/span>, strSQLAttachmentTable As String<\/span>, _\r\n strSQLVarBinaryMaxField As String<\/span>, strSQLAttachmentFKField As String<\/span>, cnn As <\/span>ADODB.Connection, _\r\n db As <\/span>DAO.Database, Optional<\/span> strErrorMessage As String<\/span>) As Boolean<\/span>\r\n Dim <\/span>bytData() As Byte<\/span>\r\n Dim <\/span>strFilepath As String<\/span>\r\n Dim <\/span>rst As <\/span>DAO.Recordset\r\n Dim <\/span>rstAttachments As <\/span>DAO.Recordset\r\n Dim <\/span>lngCopycount As Long<\/span>\r\n Set<\/span> rst = db.OpenRecordset(\"SELECT \" & strAccessField & \" FROM \" & strAccessTable & \" WHERE \" & strAccessPKField _\r\n & \" = \" & lngAccessPKID, dbOpenDynaset)\r\n Set<\/span> rstAttachments = rst.Fields(strAccessField).Value\r\n Do While<\/span> Not<\/span> rstAttachments.EOF\r\n If <\/span>SaveAttachmentToFile_Multi(rstAttachments, strFilepath) = False<\/span> Then<\/span>\r\n Exit Function<\/span>\r\n Else<\/span>\r\n If <\/span>LoadFileToByteArray(strFilepath, bytData) = True<\/span> Then<\/span>\r\n If AddByteArrayToVarBinaryMax_Multi(cnn, strSQLAttachmentTable, strSQLVarBinaryMaxField, _\r\n strSQLAttachmentFKField, lngSQLPKID, bytData, strErrorMessage) = True<\/span> Then\r\n lngCopycount = lngCopycount + 1\r\n End If<\/span>\r\n End If<\/span>\r\n End If<\/span>\r\n On Error Resume Next<\/span>\r\n Kill strFilepath\r\n On Error GoTo<\/span> 0\r\n rstAttachments.MoveNext<\/span>\r\n Loop<\/span>\r\n If <\/span>lngCopycount = rstAttachments.RecordCount Then<\/span>\r\n CopyAttachmentToVarBinaryMax_Multi = True<\/span>\r\n Else<\/span>\r\n strErrorMessage = \"Nur \" & lngCopycount & \" von \" & rstAttachments.RecordCount & \" Datensätze kopiert.\"\r\n CopyAttachmentToVarBinaryMax_Multi = False<\/span>\r\n End If<\/span>\r\nEnd Function<\/span><\/pre>\nFunktion zum Speichern der Anlagen in eine Datei<\/h2>\n
Public Function <\/span>SaveAttachmentToFile_Multi(rst As <\/span>DAO.Recordset, strFilepath As String<\/span>) As Boolean<\/span>\r\n Dim <\/span>strFilename As String<\/span>\r\n Dim <\/span>strFileExtension As String<\/span>\r\n strFilename = rst(\"FileName\")\r\n strFileExtension = Mid<\/span>(strFilename, InStrRev<\/span>(strFilename, \".\"))\r\n strFilepath = CurrentProject.Path & \"\\temp\" & strFileExtension\r\n On Error Resume Next<\/span>\r\n Do While<\/span> Not<\/span> Len<\/span>(Dir(strFilepath)) = 0\r\n Kill strFilepath\r\n DoEvents\r\n Loop<\/span>\r\n On Error GoTo<\/span> 0\r\n \r\n rst.Fields(\"FileData\").SaveToFile strFilepath\r\n SaveAttachmentToFile_Multi = True<\/span>\r\nEnd Function<\/span><\/pre>\nFunktion zum Füllen eines Byte-Arrays aus der Datei<\/h2>\n
Public Function <\/span>LoadFileToByteArray(strFilepath As String<\/span>, bytData() As Byte<\/span>) As Boolean<\/span>\r\n Dim <\/span>objStream As <\/span>ADODB.Stream\r\n Set<\/span> objStream = New<\/span> ADODB.Stream\r\n With<\/span> objStream\r\n .Type = adTypeBinary\r\n .Open\r\n .LoadFromFile strFilepath\r\n bytData = .Read\r\n .Close\r\n LoadFileToByteArray = True<\/span>\r\n End With<\/span>\r\nEnd Function<\/span><\/pre>\nFunktion zum Speichern des Byte-Arrays in einem varbinary(max)-Feld<\/h2>\n
Public Function <\/span>AddByteArrayToVarBinaryMax_Multi(cnn As <\/span>ADODB.Connection, strAttachmentTable As String<\/span>, _\r\n strAttachmentField As String<\/span>, strAttachmentFKField As String<\/span>, lngSQLPKID As Long<\/span>, bytData() As Byte<\/span>, _\r\n Optional<\/span> strErrorMessage As String<\/span>)\r\n Dim <\/span>cmd As <\/span>ADODB.Command\r\n Dim <\/span>lngRecordsAffected As Long<\/span>\r\n Dim <\/span>rst As <\/span>ADODB.Recordset\r\n Dim <\/span>prm As <\/span>ADODB.Parameter\r\n Set<\/span> cmd = New<\/span> ADODB.Command\r\n Set<\/span> rst = New<\/span> ADODB.Recordset\r\n With<\/span> cmd\r\n .ActiveConnection = cnn\r\n .CommandType = adCmdText\r\n .CommandText = \"INSERT INTO \" & strAttachmentTable & \"(\" & strAttachmentFKField & \", \" & strAttachmentField _\r\n & \") VALUES(\" & lngSQLPKID & \", ?) \"\r\n If <\/span>Not<\/span> IsEmpty(bytData) Then<\/span>\r\n Set<\/span> prm = .CreateParameter(\"img\", adLongVarBinary, adParamInput, UBound<\/span>(bytData) - LBound<\/span>(bytData) + 1, _\r\n bytData)\r\n .Parameters.Append prm\r\n Else<\/span>\r\n strErrorMessage = \"Fehler: Die Anlage enthält keine Daten und kann nicht gespeichert werden.\"\r\n GoTo Exit_Function\r\n End If<\/span>\r\n .Execute lngRecordsAffected\r\n End With<\/span>\r\n If <\/span>lngRecordsAffected = 0 Then<\/span>\r\n strErrorMessage = \"Es wurde keine Anlage hinzugefügt.\"\r\n GoTo Exit_Function\r\n Else<\/span>\r\n AddByteArrayToVarBinaryMax_Multi = True<\/span>\r\n End If<\/span>\r\nExit_Function:\r\n