Möchtest Du den gesamten Artikel lesen? Und vielleicht sogar den Artikel im PDF-Format und die Beispieldateien herunterladen? Dann hole Dir den Artikel gleich hier - völlig kostenlos!
Im Artikel ” Access und SQL Server-FileTables” (www.vbentwickler.de/489) haben wir gezeigt, wie man einer SQL Server-Datenbank eine sogenannte FileTable-Tabelle hinzufügt, in der man Dateien speichern kann, die gleichzeitig in einem vom SQL Server verwalteten Verzeichnis liegen. Im vorliegenden Artikel kommt nun Microsoft Access als Frontend ins Spiel, mit dem wir nicht nur die Dateien in der FileTable-Tabelle im SQL Server verwalten wollen, sondern wir möchten diese am Beispiel von Bilddateien auch in Access-Formularen anzeigen. Letzteres ist leicht realisierbar, denn wir können dem Bild-Steuerelement einfach den Pfad zu der jeweiligen Datei in dem von SQL Server verwalteten Bereich des Dateisystems zuweisen. Etwas aufwendiger ist es, erst einmal über Access an diese Daten in der FileTable-Tabelle zu gelangen. Wie dies gelingt und wie wir die darin gespeicherten Dateien letztlich verwalten können, zeigen wir auf den folgenden Seiten.
Zugriff auf FileTables von Access über eine per ODBC verknüpfte Tabelle
Erst einmal die schlechte Nachricht vorneweg: Ein Zugriff auf die FileTable-Tabelle als eingebundene Tabelle in Access ist nicht möglich.
Die Tabelle lässt sich zwar einbinden und wir können uns auch den Entwurf ansehen (siehe Bild 1).
Bild 1: Entwurfsansicht einer per ODBC eingebundenen FileTable-Tabelle
Aber wir erhalten keinen Zugriff auf die Daten, sondern die Fehlermeldung ODBC-Aufruf fehlgeschlagen, wenn wir versuchen, die Tabelle in der Datenblattansicht anzuzeigen (siehe Bild 2).
Bild 2: Versuch, die Tabellenverknüpfung mit der FileTable-Tabelle in der Datenblattansicht zu öffnen
Woran liegt dies und wie kann man das ändern? Es sollte einen Weg geben, denn Microsoft propagiert ja nicht umsonst seit Jahren, dass man für den Zugriff auf SQL Server-Datenbanken ODBC und DAO nutzen soll.
Mit ADODB können wir problemlos auf die Daten dieser Tabelle zugreifen.
Aber nun wenden wir uns wieder der Frage zu, woran der Zugriff per ODBC-verknüpfter Tabelle und per DAO scheitert.
Das Problem wird durch die beiden Felder path_locator und parent_path_locator der FileTable-Tabelle ausgelöst. Diese haben den Datentyp hierarchyid, der interessanterweise eine Methode namens ToString() bereitstellt. Und siehe da – mit einer gespeicherten Prozedur gelingt auch der Zugriff per DAO. Diese legen wir wie folgt für die Datenbank mit der FileTable-Tabelle an:
CREATE PROC [dbo].[spFileTable_name] AS SELECT stream_id, file_stream, name, path_locator.ToString() AS PathLocator, parent_path_locator.ToString() AS ParentPathLocator, file_type, is_directory FROM dbo.tblFiletable;
Danach legen wir eine neue Abfrage an, die wir in eine PassThrough-Abfrage umwandeln. In dieser legen wir den folgenden SQL-Befehl fest:
EXEC spFileTable_name
Außerdem hinterlegen wir für die Eigenschaft ODBC-Verbindung die Verbindungszeichenfolge zum SQL Server, die beispielsweise wie in Bild 3 aussieht.
Bild 3: PassThrough-Abfrage für den Zugriff auf eine gespeicherte Prozedur
ODBC;DRIVER={ODBC Driver 18 for SQL Server};SERVER=AMVDESKTOP2023\SQLEXPRESS;DATABASE=FileTableDB;Trusted_Connection=Yes;TrustServerCertificate=Yes;OPTION=3;LOG_QUERY=1;
Und siehe da: Die Pass-Through-Abfrage pt_spFileTable liefert alle gewünschten Daten (siehe Bild 4), sogar die aus den Feldern path_locator und parent_path_locator.
Bild 4: Ergebnis der PassThrough-Abfrage
Beispiel Produktbilder
In einer reinen Access-Anwendung würde man, um zu einem Produkt eines oder mehrere Bilder anzuzeigen, den Pfad zur Bilddatei an der entsprechenden Stelle unterbringen.
Wenn es nur ein Produktbild geben soll, könnte man dieses direkt in die Tabelle tblProdukte integrieren, andernfalls würde man eine Tabelle namens tblProduktbilder hinzufügen, in der beispielsweise ein Feld namens Bildbeschreibung und ein weiteres namens Bildpfad gespeichert werden.
Diese Tabelle, nennen wir sie tblProduktbilder, würde dann über ein Fremdschlüsselfeld namens ProduktID mit der Tabelle tblProdukte verknüpft werden.
Gehen wir also zunächst davon aus, dass wir eine sehr einfache Tabelle namens tblProdukte im SQL Server angelegt haben, deren Entwurf in Bild 5 zu sehen ist.
Bild 5: Entwurf der Tabelle tblProdukte im SQL Server
Außerdem aktivieren wir für das Feld ProduktID den Autowert über die Eigenschaft Identitätsspezifikation, die wir unten in den Spalteneigenschaften dieses Feldes finden.
Zwei Beispiele: Ein Bild und mehrere Bilder je Produkt
In den folgenden Abschnitten schauen wir uns gleich beide Konstellationen an:
- jedes Produkt soll nur genau ein Produktbild erhalten und
- jedes Produkt soll ein, kein oder mehrere Produktbilder erhalten.
Beispiel: Ein Bild je Produkt
Als Erstes legen wir eine neue FileTable-Tabelle an, die tblProduktbilder heißen soll.
Dazu verwenden wir die folgende T-SQL-Anweisung, die zunächst die grundlegende FileTable-Tabelle erstellt und für diese das Verzeichnis Produktbilder zu dem Verzeichnis für die Dateien der FileTable-Tabellen des SQL Servers hinzufügt, das wir wie im Artikel Access und SQL Server-FileTables (www.vbentwickler.de/489) beschrieben angelegt haben:
CREATE TABLE dbo.tblProduktbilder
AS FILETABLE
WITH
(
FILETABLE_DIRECTORY = N''Produktbilder'',
FILETABLE_COLLATE_FILENAME = database_default
);
Leider können wir einer FileTable-Tabelle keine eigenen Spalten hinzufügen, sonst hätten wir wie üblich ein Primärschlüsselfeld wie ProduktID und gegebenenfalls noch ein Feld wie Bildbeschreibung hinzufügen können.
Stattdessen können wir aber auch das Primärschlüsselfeld der FileTable-Tabelle verwenden, um die Tabelle tblProdukte über ein geeignetes Fremdschlüsselfeld damit zu verknüpfen.
Dazu fügen wir der Tabelle tblProdukte mit der folgenden T-SQL-Anweisung im SQL Server Management Studio das Feld ProduktbildID hinzu:
ALTER TABLE dbo.tblProdukte ADD ProduktbildID UNIQUEIDENTIFIER NULL;
Danach führen wir die folgende Anweisung aus, um eine Beziehung zwischen den beiden Tabellen herzustellen:
ALTER TABLE dbo.tblProdukte
ADD CONSTRAINT FK_tblProdukte_tblProduktbilder
FOREIGN KEY (ProduktbildID)
REFERENCES dbo.tblProduktbilder (stream_id);
Das Feld ProduktbildID der Tabelle tblProdukte verweist nun auf das Primärschlüsselfeld stream_id der Tabelle tblProduktbilder.
Nun erstellen wir eine gespeicherte Prozedur, die uns die Daten der Tabelle tblProduktbilder in den nachfolgend beschriebenen Feldern liefert:
- ProduktbildID: enthält den Wert des Feldes stream_id
- Bildpfad: enthält den kompletten Pfad, der mit der SQL Server-Funktion CONCAT(FileTableRootPath(), file_stream.GetFileNamespacePath()) ermittelt wird.
Die gespeicherte Prozedur erstellen wir mit dieser Anweisung (zum späteren Ändern muss CREATE durch ALTER ersetzt werden):
CREATE PROC dbo.spProduktbilder
AS
SELECT stream_id AS ProduktbildID,
name As Bildname,
CONCAT(FileTableRootPath(), file_stream.GetFileNamespacePath()) As Bildpfad
FROM tblProduktbilder
WHERE file_type = ''png'' AND is_directory = 0
Sie filtert außerdem nach dem Wert png für das Feld file_type und liefert nur Dateien zurück (is_directory = 0).
Nachdem wir dem Verzeichnis einige Beispielbilder hinzugefügt haben, können wir die gespeicherte Prozedur mit EXEC spProduktbilder ausführen und erhalten das Ergebnis aus Bild 6.
Bild 6: Gespeicherte Prozedur, die alle .png-Dateien liefert
Gespeicherte Prozedur für Bilder per PassThrough-Abfrage in Access verfügbar machen
Damit wechseln wir zur Access-Anwendung. Dieser fügen wir eine Tabellenverknüpfung zur SQL Server-Tabelle tblProdukte hinzu.
Außerdem legen wir eine PassThrough-Abfrage namens pt_spProduktbilder an, mit der wir die Daten der gespeicherten Prozedur spProduktbilder in Access verfügbar machen wollen.
Öffnen wir diese PassThrough-Abfrage, sehen wir in der Datenblattansicht die gleichen Daten, welche die gespeicherte Prozedur im SQL Server Management Studio geliefert hat (siehe Bild 7).
Bild 7: PassThrough-Abfrage, die alle Daten der gespeicherten Prozedur spProduktbilder liefert
Produkte und Produktbilder verwalten
Nun stellt sich die Frage, wie wir einem Produkt ein Bild hinzufügen können. Dies geschieht durch folgende Aktionen:
- Auswahl der gewünschten Bilddatei
- Hinzufügen der Bilddatei zur Tabelle tblProduktbilder
- Ermitteln des Primärschlüsselwertes für den neuen Datensatz der Tabelle tblProduktbilder
- Eintragen dieses Wertes in das Fremdschlüsselfeld ProduktbildID der Tabelle tblProdukte
Auswahl der gewünschten Bilddatei
Die Bilddatei, die wir der FileTable-Tabelle hinzufügen wollen, wollen wir per Dateiauswahl-Dialog ermitteln.
Dazu nutzen wir die folgende Funktion, welche die FileDialog-Klasse der Office-Bibliothek nutzt. Diese Bibliothek müssen wir zunächst über den Verweise-Dialog des VBA-Editors zum Projekt hinzufügen. Die Bibliothek heißt Microsoft Office 16.0 Object Library.
Die Funktion zum Anzeigen und Auslesen des Dateidialogs legen wir wie folgt an:
Public Function ChooseFolder() As String Dim objFileDialog As Office.FileDialog Dim strTemp As String Set objFileDialog = _ Application.FileDialog( _ msoFileDialogFilePicker) With objFileDialog .Title = "Datei auswählen" .ButtonName = "Auswählen" .InitialFilename = CurrentProject.Path & "\" .Filters.Clear .Filters.Add "Bilddateien", "*.png" If .Show = True Then strTemp = .SelectedItems(1) End If End With ChooseFolder = strTemp End Function
Hinzufügen der Bilddatei zur Tabelle tblProduktbilder
Zum Hinzufügen eines Bildes zur Tabelle tblProduktbilder können wir zunächst die folgende T-SQL-Anweisung nutzen:
INSERT INTO dbo.tblProduktbilder (name, file_stream) OUTPUT inserted.stream_id SELECT ''pic001.png'', BulkColumn FROM OPENROWSET( BULK C:\pic001.png, SINGLE_BLOB ) AS FileData;
In dieser sind sowohl der Name des Bildes in der Tabelle tblProduktbilder als auch der Pfad, aus dem das Bild bezogen werden soll, fest verdrahtet.
In der Praxis müssen wir diese Werte jedoch variabel einfügen können. Wir müssen also eine Möglichkeit finden, wie wir dem SQL Server die notwendigen Daten übergeben.
Die erste Idee dazu ist, eine gespeicherte Prozedur anzulegen, welche die entsprechenden Parameter entgegennimmt und diesen Befehl ausführt.
Das ist aus verschiedenen Gründen problematisch, zum Beispiel weil wir den Pfad der einzufügenden Datei in dieser Anweisung nicht einfach als Parameter einfügen können (das ist eine technische Limitierung dieser speziellen Anweisung).
Wir müssten die Anweisung als SQL-String zusammenstellen und sie dann mit sp_executesql ausführen.
Wenn wir die Anweisung mit fest vorgegebenem Pfad in einer gespeicherten Prozedur angeben würden, könnten wir anschließend leicht den Wert des Primärschlüsselfeldes für die Tabelle tblProduktbilder ermitteln (das Primärschlüsselfeld in FileTable-Tabellen ist übrigens das Feld path_locator).
Dazu könnten wir die folgende Abfrage nutzen:
SELECT SCOPE_IDENTITY() AS ID
Wenn wir die Anweisung allerdings als Parameter von sp_executesql ausführen, findet dies in einem anderen Kontext statt als der, auf den wir mit SELECT SCOPE_IDENTITY zugreifen können. Wir müssten also anderweitig ermitteln, welcher Datensatz soeben angelegt wurde.
Dazu können wir zum Beispiel eine Abfrage verwenden, die den Datensatz mit einem Kriterium ermittelt, das den Inhalt des Feldes name des neuen Datensatzes enthält.
Und auch das ist nur eindeutig, wenn wir die FileTable-Tabelle nur zum einfachen Ablegen von Dateien nutzen, ohne Unterverzeichnisse zu verwenden – denn dann könnten wiederum mehrere Datensätze den gleichen Wert im Feld name enthalten, bei unterschiedlichem Wert im Feld parent_path_locator.
Wert des Feldes stream_id für den neuen Datensatz ermitteln
Vorausgesetzt, dass wir die Dateien nur im Hauptverzeichnis der FileTable-Tabelle ablegen, was in den meisten Fällen kein Problem ist, können wir mit diesem Wissen auch gleich eine VBA-Funktion anlegen, welche die T-SQL-Anweisung zum Hinzufügen der Datei zur FileTable-Tabelle hinzufügt und aufgrund des Wertes im Feld name beispielsweise den Wert des Feldes stream_id des neu hinzugefügten Datensatzes ermittelt.
Diesen können wir dann nutzen, um die Tabelle tblProdukte mit dem entsprechenden Datensatz der Tabelle tblProduktbilder zu verknüpfen.
Also legen wir eine Funktion an, die eine entsprechende SQL-Anweisung zusammenstellt, diese in eine temporäre PassThrough-Abfrage schreibt, ein Recordset auf Basis dieser Abfrage erstellt und damit die ID des angelegten Datensatzes ermittelt.
Diese Funktion finden wir in Listing 1. Die Funktion erwartet die beiden folgenden Parameter:
Public Function ProduktbildEinfuegen_PT(ByVal strBildpfad As String, ByVal strBildname As String) As String Dim db As DAO.Database Dim qdf As DAO.QueryDef Dim rst As DAO.Recordset Dim strSQL As String Dim strConnect As String Dim strStreamID As String strConnect = "ODBC;Driver=ODBC Driver 18 for SQL Server;Server=amvDesktop2023\SQLEXPRESS;" _ & "Database=FileTableDB;Trusted_Connection=yes;Encrypt=no;" strSQL = "INSERT INTO dbo.tblProduktbilder (name, file_stream)" & vbCrLf strSQL = strSQL & "OUTPUT inserted.stream_id" & vbCrLf strSQL = strSQL & "SELECT ''" & strBildname & "'', BulkColumn" & vbCrLf strSQL = strSQL & "FROM OPENROWSET(" & vbCrLf strSQL = strSQL & " BULK ''" & strBildpfad & "''," & vbCrLf strSQL = strSQL & " SINGLE_BLOB" & vbCrLf strSQL = strSQL & ") AS FileData;" Set db = CurrentDb Set qdf = db.CreateQueryDef("") With qdf .Connect = strConnect .ReturnsRecords = True .SQL = strSQL Set rst = .OpenRecordset(dbOpenSnapshot) End With If Not rst Is Nothing Then If Not rst.EOF Then strStreamID = Nz(rst.Fields(0).Value, "") End If rst.Close End If Set rst = Nothing Set qdf = Nothing Set db = Nothing ProduktbildEinfuegen_PT = strStreamID End Function
Listing 1: Anlegen eines neuen Bildes in der Tabelle tblProduktbilder
- strBildpfad: Pfad zu der einzufügenden Datei
- strBildname: Name, unter dem das Bild in der Tabelle tblProduktbilder gespeichert werden soll
Die Prozedur stellt zunächst in der Variablen strConnect die Verbindungszeichenfolge für die SQL Server-Datenbank zusammen. Diese musst Du gegebenenfalls an Deine Konstellation anpassen.
Danach stellt sie in der Variablen strSQL die auszuführenden Anweisungen zusammen. Wenn wir vom Wert c:\pic001.png für strBildpfad und pic001.png für strBildname ausgehen, würde der Inhalt von strSQL wie folgt aussehen:
INSERT INTO dbo.tblProduktbilder (name, file_stream) OUTPUT inserted.stream_id SELECT pic001.png, BulkColumn FROM OPENROWSET( BULK C:\pic001.png, SINGLE_BLOB ) AS FileData;
Danach holt die Funktion einen Verweis auf das aktuelle Database-Objekt in die Variable db und legt mit dessen Methode CreateQueryDef eine neue, temporäre Abfrage in der Variablen qdf an.
Für dieses QueryDef-Objekt stellt sie dann die Eigenschaft Connect auf die Verbindungszeichenfolge aus strConnect ein. Die Einstellung True für die Eigenschaft ReturnsRecords legt fest, dass die enthaltenen Anweisungen ein Ergebnis zurückliefern sollen.
Für die Eigenschaft SQL übergeben wir die Anweisungen aus strSQL. Schließlich führen wir die Abfrage mit der OpenRecordset-Methode aus und referenzieren das resultierende Recordset mit der Variablen rst.
Wenn das Recordset vorhanden und nicht leer ist, lesen wir daraus den Wert des ersten Feldes aus, den wir im Falle des Wertes NULL noch mit der Nz-Funktion durch eine leere Zeichenkette ersetzen. Das Ergebnis speichern wir in der Variablen strStreamID.
Danach schließen wir das Recordset und leeren die verwendeten Objektvariablen. Die Funktion gibt den Wert aus strStreamID als Ergebnis zurück.
Der testweise Aufruf dieser Funktion erfolgt so:
Public Sub Test_ProduktbildEinfuegen_PT() Dim strBildpfad As String Dim strBildname As String Dim strStreamID As String strBildname = "pic001.png" strBildpfad = "C:\...\pic001.png" strStreamID = _ ProduktbildEinfuegen_PT(strBildpfad, strBildname) Debug.Print "Neue Stream_ID: " & strStreamID End Sub
Die Prozedur füllt die Parameter strBildpfad und strBildname mit den Testwerten und ruft damit die Funktion auf. Das Ergebnis wird direkt der Variablen strStreamID zugewiesen, deren Inhalt wir am Ende mit Debug.Print im Direktbereich des VBA-Editors ausgeben.
Rufen wir diese Prozedur einmalig auf, legt diese erfolgreich einen Datensatz in der Tabelle tblProduktbilder an. Beim erneuten Aufruf mit den gleichen Parametern erhalten wir allerdings den Fehler ODBC-Aufruf fehlgeschlagen in der folgenden Zeile:
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