Anlage-Feld zum SQL Server migrieren

2025, 3/2025, SQL Server und Co.

Anlage-Felder erlauben es seit Access 2010, Bilder und andere Dateien direkt in Access-Tabellen zu speichern. Sie bieten ein praktisches Popup-Menü, mit dem man die enthaltenen Dateien verwalten kann. Enthält ein Anlage-Feld Bilddateien, können diese mit dem Bild-Steuerelement von Access direkt in Formularen und Berichten angezeigt werden. Wenn man seine Tabellen zum SQL Server migrieren möchte, wird dies jedoch zu einem Problem: Im SQL Server gibt es nämlich kein Pendant zum Anlage-Feld, sondern nur Alternativen wie beispielsweise den varbinary(max)-Datentyp. Damit können wir zwar die Dateien im binären Format im SQL Server speichern, aber rundherum ergeben sich einige Fragen: Wie zeige ich Bilddateien an, die in einem solchen Feld gespeichert sind? Wie gehe ich mit Anlage-Feldern um, die nicht nur eine Anlage enthalten? All dies erläutern wir in diesem und weiteren Beiträgen.

Anlage-Felder sind flexibel einsetzbar. Grundsätzlich kann man in ihnen Dateien speichern, und zwar so viele, wie man möchte. Der Grund dafür ist, dass Access im Hintergrund eine eigene Tabelle für die einzelnen Anlagen eines Anlage-Feldes bereithält.

Das dies der Fall ist, werden wir später sehen, wenn wir mit zwei verschachtelten Recordsets auf die einzelnen Datensätze eines Anlage-Feldes zugreifen.

Aber man kann nicht nur einfach Dateien in Anlage-Feldern speichern. Wenn es sich bei den Dateien um Bilddateien handelt, können wir diese sogar beispielsweise im Anlage-Steuerelement oder im Bild-Steuerelement (mit wesentlich mehr Aufwand) anzeigen.

Um die in einem Anlage-Feld enthaltenen Dateien in einem Formular zu verwalten, brauchen wir nur das Anlagefeld in den Tabellenentwurf zu ziehen. Klicken wir dieses dann an, sehen wir ein kleines Popup-Menü mit drei Befehlen, von denen die ersten beiden zum Navigieren zwischen den enthaltenen Dateien und die dritte zum Öffnen des Dialogs zum Verwalten der enthaltenen Dateien verwendet wird (siehe Bild 1).

Anzeigen von Bildern aus einem Anlage-Feld

Bild 1: Anzeigen von Bildern aus einem Anlage-Feld

Wir gehen an dieser Stelle davon aus, dass wir bereits eine Beispieltabelle mit den drei Feldern AnlageID (Primärschlüsselfeld), Bezeichnung (Textfeld) und Anlagefeld (Anlage-Feld) erstellt haben, die wir als Datensatzquelle des Formulars verwenden.

Anlage-Felder im SQL Server?

Zum Problem werden Anlage-Felder, wenn wir sie zum SQL Server migrieren wollen. Im Beitrag Bilder im SQL Server mit varbinary(max) (www.access-im-unternehmen.de/1538) zeigen wir, wie man Bild-Dateien in einem varbinary(max)-Feld des SQL Servers speichert und wie man die Bilder daraus in einem Bild-Steuerelement in Access anzeigt.

Im vorliegenden Artikel schauen wir uns genauer an, wie eine Tabelle mit Anlage-Feld zu einer entsprechenden SQL Server-Tabelle migriert werden kann. Dabei ist auch hier unser Ziel, ein entsprechendes varbinary(max)-Feld zu erstellen und die Inhalte unseres Anlage-Feldes automatisiert zum SQL Server zu übertragen.

Zuvor müssen wir jedoch ein wenig planen.

Migration mit dem SQL Server Migration-Assistant: Wie Anlage-Felder berücksichtigen?

Der SQL Server Migration Assistant ist das beste Werkzeug, wenn es um eine weitgehend automatisierte Migration der Tabellen von Access zu einer SQL Server-Datenbank geht.

Es werden mögliche Probleme angezeigt und es können alle Tabellen, Felder, Beziehungen, weitere Eigenschaften und auch noch die Daten migriert werden.

Leider bietet der SQL Server Migration Assistant keine Möglichkeit, ein Feld mit dem Datentyp Anlage in ein entsprechendes Feld zu migrieren. Der Workaround des SQL Server Migration Assistant ist es, eine Meldung anzuzeigen, dass der Attachment-Datentyp nicht migriert werden kann, und stattdessen immerhin das Feld als varchar(8000)-Feld anzulegen.

Hier haben wir nun zwei Möglichkeiten:

  • Wir können die Situation so aufgreifen, wie sie ist, und für alle Anlage-Felder in der Quelldatenbank das dafür erstellte varchar(8000)-Feld in ein varbinary(max)-Feld umwandeln und die entsprechenden Dateien in dieses Feld übertragen.
  • Oder wir lassen die Tabelle bei der Migration aus und migrieren sie komplett von Hand. Das würde jedoch bedeuten, dass wir auch die Beziehungen, an denen diese Tabelle beteiligt ist, nachträglich hinzufügen müssen. Das scheint auf jeden Fall die aufwendigere Variante zu sein, vor allem deshalb, weil die Migration die übrigen Felder der Tabelle korrekt durchführt.

Außerdem müssen wir noch beachten, dass ein Anlage-Feld auch immer mehr als eine Datei enthalten kann. Wie oben erwähnt, befindet sich hinter einem Anlage-Feld immer eine intern gepflegte Tabelle, die für jede enthaltene Datei einen eigenen Datensatz anlegt.

Wenn wir dies korrekt im SQL Server abbilden wollen, brauchen wir also eigentlich sogar eine eigene Tabelle für die Dateien.

Da es jedoch auch Fälle gibt, in denen die Anzahl der enthaltenen Dateien auf eine begrenzt ist, schauen wir uns zunächst diese einfachere Methode an.

Nochmal zusammengefasst die Ausgangssituation: Wir haben eine Migration durchgeführt und es fehlen nur noch die Anlage-Felder und ihre Daten, die als varbinary(max)-Felder angelegt werden sollen.

Also lauten die Voraussetzungen:

  • Wir haben eine Access-Tabelle mit einem Anlage-Feld.
  • Wir haben diese Tabelle bereits zum SQL Server migriert, wobei das Feld mit dem Datentyp varchar(8000) angelegt wurde.

Wir werden also zunächst eine Prozedur erstellen, der wir den Namen der Tabelle und des Feldes auf Access-Seite übergeben sowie den Namen der Tabelle und des Feldes in der SQL Server-Datenbank.

Diese Prozedur soll nun folgende Schritte ausführen:

  • Das vorhandene Feld mit dem Datentyp varchar(8000) löschen.
  • Ein neues Feld mit dem Datentyp varbinary(max) anlegen.
  • Den Inhalt des Anlage-Feldes für alle Datensätze der Quelltabelle in das neue varbinary(max)-Feld übertragen.

Vorbereitung

Wir benötigen einen Verweis auf die Bibliothek Microsoft ActiveX Data Objects 6.1 Library, den wir über den Verweise-Dialog des VBA-Editors hinzufügen können.

Prozeduren zum Anlegen und Übertragen erstellen

Aus Gründen der Wartbarkeit wollen wir die Funktion auf mehrere Routinen aufteilen. Die erste, die wir uns anschauen, soll die Voraussetzungen liefern, um überhaupt Bilddaten in die SQL Server-Tabelle schreiben zu können.

Zuallererst legen wir jedoch die Verbindungszeichenfolge fest, die wir für den Zugriff per ADODB auf die Datenbank benötigen. Sie sieht wie folgt aus und erfordert noch das Ersetzen der Platzhalter [SERVER] und [DATABASE] durch die bei Ihnen vorliegenden Werte:

Public Const cStrConnection As String = "Provider=MSOLEDBSQL;Data Source=[SERVER];Initial Catalog=[DATABASE];Integrated Security=SSPI;"

Danach erstellen wir die erste Funktion namens VarbinaryFeldAnlegenOderAnpassen (siehe Listing 1).

Public Function VarbinaryFeldAnlegenOderAnpassen(cnn As ADODB.Connection, strSQLTabelle As String, _
         strSQLFeld As String, Optional lngErrNumber As Long, Optional strErrDescription As String) As Boolean
     Dim rst As ADODB.Recordset
     Dim strSQL As String
     Dim intResult As VbMsgBoxResult
     Set rst = New ADODB.Recordset
     strSQL = "SELECT DATA_TYPE, CHARACTER_MAXIMUM_LENGTH FROM INFORMATION_SCHEMA.COLUMNS " & _
              "WHERE TABLE_NAME = ''" & strSQLTabelle & "'' AND COLUMN_NAME = ''" & strSQLFeld & "''"
     rst.Open strSQL, cnn, adOpenStatic, adLockReadOnly
     If Not rst.EOF Then
         If rst.Fields("DATA_TYPE").Value <> "varbinary" Or rst.Fields("CHARACTER_MAXIMUM_LENGTH").Value <> -1 Then
             intResult = MsgBox("Das Feld " & strSQLFeld & " ist kein varbinary(max). Soll es geändert werden?", _
                 vbYesNo + vbQuestion, "Feld anpassen?")
             If intResult = vbYes Then
                 On Error Resume Next
                 cnn.Execute "ALTER TABLE " & strSQLTabelle & " DROP COLUMN " & strSQLFeld
                 cnn.Execute "ALTER TABLE " & strSQLTabelle & " ADD " & strSQLFeld & " varbinary(max)"
                 If Not Err.Number = 0 Then
                     lngErrNumber = Err.Number
                     strErrDescription = Err.Description
                     Exit Function
                 End If
                 On Error GoTo 0
             End If
         End If
     Else
         intResult = MsgBox("Das Feld ''" & strSQLFeld & "'' ist noch nicht vorhanden. Soll es mit dem " _
             & "Datentyp varbinarx(max) angelegt werden?", vbYesNo, "Feld nicht vorhanden")
         If intResult = vbYes Then
             On Error Resume Next
             cnn.Execute "ALTER TABLE " & strSQLTabelle & " ADD " & strSQLFeld & " varbinary(max)"
             If Not Err.Number = 0 Then
                 strErrDescription = Err.Description
                 lngErrNumber = Err.Number
                 Exit Function
             End If
             On Error GoTo 0
         End If
     End If
     VarbinaryFeldAnlegenOderAnpassen = True
     rst.Close
End Function

Listing 1: Funktion zum Anlegen von varbinary(max)-Feldern oder Umwandeln von anderen Felddatentypen in varbinary(max)

Sie erwartet die folgenden Parameter:

  • cnn: ADODB.Connection-Objekt für die zu verwendende Verbindung
  • strSQLTabelle: Name der zu verwendenden Tabelle auf dem SQL Server
  • strSQLFeld: Name des anzupassenden/zu erstellenden Bildfeldes auf dem SQL Server

Die Funktion liefert den Wert True zurück, wenn das Feld vorhanden ist (gegebenenfalls durch Anpassung oder Erstellung).

Die Funktion erstellt als Erstes ein neues ADODB.Recordset und referenziert es mit der Variablen rst.

Dann stellt es in der Variablen strSQL eine Abfrage zusammen, die in unserem Beispiel wie folgt aussieht:

SELECT DATA_TYPE, CHARACTER_MAXIMUM_LENGTH FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = ''tblBilder'' AND COLUMN_NAME = ''Bild''

Diese Abfrage ermittelt den Datentyp und die Anzahl Zeichen des Feldes, das noch den Datentyp varchar(8000) hat und das wir ersetzen wollen.

Diese Abfrage öffnen wir mit der Open-Methode des Recordsets und prüfen in einer If…Then-Bedingung, ob überhaupt ein entsprechender Datensatz gefunden wurde, sprich: ob das Feld überhaupt vorhanden ist.

Falls ja, prüft die zweite If…Then-Bedingung, ob der Datentyp des Feldes ungleich varbinary ist oder ob die maximale Zeichenanzahl für dieses Feld ungleich -1 ist (was MAX entspricht). In diesem Fall erscheint eine Meldung, die fragt, ob der Datentyp des Feldes geändert werden soll.

Antwortet der Benutzer mit Ja, führt die Funktion bei deaktivierter Fehlerbehandlung zwei SQL-Anweisungen aus.

  • Die erste Anweisung löscht das bestehende Feld mit dem angegebenen Namen.
  • Die zweite fügt das neue Feld mit dem Datentyp varbinary(max) hinzu.

Löst dies einen Fehler aus, werden die beiden Rückgabeparameter lngErrNumber und strErrDescription mit den entsprechenden Werten aus der Err-Klasse gefüllt und die Funktion wird verlassen.

Es kann auch geschehen, dass das Feld, dessen Name wir mit strSQLFeld übergeben haben, noch nicht vorhanden ist. Dann erscheint eine Meldung, die den Benutzer fragt, ob es mit den Datentyp varbinary(max) angelegt werden soll. Antwortet der Benutzer mit Ja, wird dieses Feld zur Tabelle aus strSQLTabelle hinzugefügt. Tritt hier ein Fehler auf, verfahren wir mit den Fehlerinformationen wie oben.

Damit haben wir bereits das benötigte Feld mit dem Datentyp varbinary(max) vorbereitet.

Kopieren der Daten von Anlagefeld zum varbinary(max)-Feld

Hier stellt sich die Frage, wie wir im Detail beim Kopieren vorgehen wollen. Der Prozess ist ohnehin individuell, weil jedes Bild einzeln vom Anlage-Feld in ein Byte-Array übertragen und dann in das varbinary(max)-Feld eingetragen werden muss.

Wir können nun verschiedene Möglichkeiten wählen. Die erste Frage ist:

  • Soll die Prozedur nur die Inhalte der Anlage-Felder in die entsprechenden varbinary(max)-Felder kopieren?
  • Oder soll diese direkt den vollständige Datensatz kopieren?

Es wird vermutlich im Laufe der Zeit mal den einen, mal den anderen Fall geben. Weiter oben haben wir bereits diskutiert, dass es vermutlich oft vorkommt, dass das vollständige Datenmodell bis auf die Anlage-Felder mit Tools wie dem SQL Server Migration Assistant zum SQL Server übertragen wird.

Wir tun uns also in den meisten Fällen einen Gefallen, indem wir den Teil, der das Anlage-Feld in das varbinary(max)-Feld überträgt, in einer eigenen Funktion realisieren.

Wir können dann beispielsweise die folgenden Varianten für aufrufende Funktionen wählen:

  • Eine Prozedur, die alle Datensätze der Access-Tabelle mit dem Anlage-Feld durchläuft und zunächst alle Daten außer dem Anlage-Feld in die SQL Server-Tabelle kopiert. Für jeden aktuellen Datensatz wird dann die oben beschriebene Funktion zum Übertragen der Daten aus dem Anlage-Feld aufgerufen.
  • Oder wir führen das Kopieren der einfachen Daten in einer INSERT INTO-Anweisung für alle Datensätze gleichzeitig durch und durchlaufen anschließend eine Schleife mit allen Datensätzen, wo wir die Inhalte der Anlage-Felder nachziehen.

In beiden Fällen benötigen wir eine eigene Prozedur, die davon ausgeht, dass der eigentliche Datensatz bereits kopiert wurde und nur noch der Inhalt der Anlage-Felder übertragen werden muss.

Funktion zum Übertragen der Anlage-Felder

Daher programmieren wir die Funktion namens CopyAttachmentToVarBinaryMax so, dass wir mit den Parametern die folgenden Informationen übergeben:

  • strAccessTable: Name der verwendeten Quelltabelle in Access
  • strAccessField: Name des Anlage-Feldes in der Access-Tabelle
  • strAccessPKField: Name des Primärschlüsselfeldes in der Access-Tabelle
  • lngAccessPKID: Wert des Primärschlüsselfeldes für den zu übertragenden Datensatz in der Access-Tabelle
  • strSQLTable: Name der Zieltabelle auf dem SQL Server
  • strSQLField: Name des varbinary(max)-Feldes in der Zieltabelle
  • strSQLPKField: Name des Primärschlüsselfeldes der SQL Server-Tabelle
  • lngSQLPKID: Wert des Primärschlüsselfeldes für den zu übertragenden Datensatz in der SQL Server-Tabelle
  • cnn: ADODB-Verbindung
  • strErrorMessage: Außerdem verwenden wir noch einen Parameter, der Informationen zu einem eventuell aufgetretenen Fehler zurückgibt.

Die Funktion ist die Steuerzentrale für verschiedene Schritte (siehe Listing 2). Sie sorgt zunächst dafür, dass die im Anlagefeld gespeicherte Datei auf der Festplatte gespeichert wird. Dazu nutzt sie die Funktion SaveAttachmentToFile. Das Ergebnis ist der Pfad zu dieser Datei.

Public Function CopyAttachmentToVarBinaryMax(strAccessTable As String, strAccessField As String, _
         strAccessPKField As String, lngAccessPKID As Long, strSQLTable As String, strSQLField As String, _
         strSQLPKField As String, lngSQLPKID As Long, cnn As ADODB.Connection, _
         Optional strErrorMessage As String) As Boolean
     Dim bytData() As Byte
     Dim strFilepath As String
     
     If SaveAttachmentToFile(strAccessTable, strAccessField, strAccessPKField, lngAccessPKID, strFilepath, _
             strErrorMessage) = False Then
         Exit Function
     Else
         If LoadFileToByteArray(strFilepath, bytData) = True Then
             If AddByteArrayToVarBinaryMax(cnn, strSQLTable, strSQLField, strSQLPKField, lngSQLPKID, bytData, _
                     strErrorMessage) = True Then
                 CopyAttachmentToVarBinaryMax = True
             Else
                 CopyAttachmentToVarBinaryMax = False
             End If
         End If
     End If
End Function

Listing 2: Funktion zum Kopieren von Attachment-Inhalten in ein varbinary(max)-Feld

War der Aufruf dieser Funktion erfolgreich, folgt die nächste Funktion, mit der wir den Inhalt der Datei in ein Byte-Array einlesen. Diese Funktion heißt LoadFileToByteArray. Sie liefert mit dem Parameter bytData das Byte-Array zurück.

War auch dies erfolgreich, folgt der nächste Funktionsaufruf, diesmal für die Funktion AddByteArrayToVarBinaryMax. Diese soll schließlich das Byte-Array in das angegebene Feld der entsprechenden Tabelle eintragen.

Funktion zum Speichern einer Anlage im Dateisystem

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

Schreibe einen Kommentar