Lies diesen Artikel und viele weitere mit einem kostenlosen, einwöchigen Testzugang.
Im Beitrag „RDBMS-Zugriff per VBA: Verbindungen“ haben wir die Grundlage für den Zugriff auf SQL Server-Datenbanken geschaffen. Nun gehen wir einen Schritt weiter: Wir wollen mit den dort beschriebenen Methoden etwa zum Zusammenstellen einer Verbindungszeichenfolge auf die Daten einer SQL Server-Datenbank zugreifen. Dabei lernen Sie eine Reihe interessanter Funktionen kennen, die den Zugriff deutlich vereinfachen und die auch noch überaus performant sind.
Der eingangs erwähnte Beitrag RDBMS-Zugriff per VBA: Verbindungen (www.access-im-unternehmen.de/1054) hat die Werkzeuge dafür geliefert, dass Sie Verbindungszeichenfolgen aus einer Tabelle zusammenstellen und diese zum Aufbau einer Verbindung nutzen können. Im vorliegenden Beitrag nutzen wir vor allem die mit der Funktion Standardverbindungszeichenfolge ermittelte Verbindungszeichenfolge, um auf die Tabellen der SQL Server-Datenbank zuzugreifen. Sie können aber natürlich auch eine manuell als String zusammengestellte Verbindungszeichenfolge nutzen.
Beispieldatenbank
Wir verwenden weiterhin die im ersten Teil der Beitragsreihe vorgestellte Datenbank Suedsturm auf Basis des LocalDB-Datenbanksystems. Sie können aber natürlich auch eine Datenbank über den SQL Server nutzen (s. Bild 1).
Bild 1: Die in diesem Beitrag verwendete Verbindungszeichenfolge im Formular
Die Verknüpfung zu den Tabellen der SQL Server-Datenbank erstellen Sie am einfachsten mit dem Formular frmTabellenVerknuepfen, das wir im Beitrag SQL Server-Tools (www.access-im-unternehmen.de/1061) vorstellen.
Recordsets auf Basis von SQL Server-Daten
Recordsets sind unter Access ein häufig genutztes Mittel für den Zugriff auf die Daten einer Tabelle oder Abfrage. Natürlich werden Sie diese auch füllen wollen, wenn die Daten Ihrer Datenbank längst in einer SQL Server-Datenbank gelandet sind. Daher schauen wir uns nun zunächst an, wie Sie Recordsets auf der Basis verschiedener Zugriffsarten auf die Daten der SQL Server-Datenbank erstellen und füllen können.
Dabei greifen wir auf einfachem Wege auf die Daten zu, nämlich über eine verknüpfte Tabelle, aber auch auf subtilere Weise – etwa über eine Pass-Through-Abfrage, die sich einer gespeicherten Prozedur als Datenquelle bedient.
Recordset auf Basis einer verknüpften Tabelle
Die einfachste Möglichkeit, per VBA auf die Daten einer verknüpften Tabelle zuzugreifen, ist das DAO-Recordset. Dieses erhalten Sie mit der OpenRecordset-Methode, der Sie als ersten Parameter den Namen der verknüpften Tabelle, als zweiten den Wert dbOpenSnapshot (Daten ändern wollen wir nicht per verknüpfter Tabelle, da zu unperformant – ein rein lesender Zugriff sorgt außerdem für wesentlich weniger Sperren auf dem Server) und als dritten Parameter den Wert dbSeeChanges, um Fehler in Zusammenhang mit Autowerten in der zugrunde liegenden Tabelle zu vermeiden.
Die folgende Beispielprozedur erstellt ein Recordset auf Basis der Tabelle tblArtikel und gibt die Werte der Felder ArtikelID und Artikelname aller Datensätze dieser Tabelle im Direktfenster aus:
Public Sub VerknuepfteTabellePerRecordset() Dim db As DAO.Database Dim rst As DAO.Recordset Set db = CurrentDb Set rst = db.OpenRecordset("SELECT * FROM tblArtikel", _ dbOpenSnapshot, dbSeeChanges) Do While Not rst.EOF Debug.Print rst!ArtikelID, rst!Artikelname rst.MoveNext Loop rst.Close Set rst = Nothing Set db = Nothing End Sub
Wenn Sie die Daten aus der SQL Server-Tabelle nicht nur lesen, sondern auch ändern wollen, verwenden Sie db-OpenDynaset statt dbOpenSnapshot.
Vereinfachungen für den Zugriff auf gespeicherte Prozeduren
Gespeicherte Prozeduren sind prinzipiell Abfragen, die Auswahl- oder Aktionsabfragen enthalten und direkt auf dem SQL Server gespeichert sind und dort ausgeführt werden. Sie liefern nur die benötigten Daten zurück und sind daher meist viel schneller, als wenn Sie etwa mit verknüpften Tabellen arbeiten.
Hinweis: Die ab jetzt vorgestellten Techniken erlauben lediglich den lesenden Zugriff auf die ermittelten Daten.
Wenn Sie ein Recordset auf Basis des Ergebnisses einer gespeicherten Prozedur verwenden möchten, benötigen Sie folgende Dinge:
- die gespeicherte Prozedur in der SQL Server-Datenbank,
- eine Pass-Through-Abfrage in der Access-Datenbank und
- VBA-Code, der auf die Pass-Through-Abfrage zugreift.
Im einfachsten Fall handelt es sich um eine gespeicherte Prozedur, die keine Parameter erwartet. Warum ist dies so unkompliziert Dies liegt in der Natur des Aufrufs einer gespeicherten Prozedur über eine Pass-Through-Abfrage begründet.
Die gespeicherte Prozedur liegt auf dem SQL Server und wird über eine Pass-Through-Abfrage mit der EXEC-Anweisung aufgerufen:
EXEC cbo.spBeispielprozedur
Kein Problem – die Pass-Through-Abfrage kann wie gesehen verwendet werden. Wenn Sie jedoch einen Parameter übergeben müssen, gehört dieser in den SQL-Text der Pass-Through-Abfrage – also beispielsweise so:
EXEC dbo.spBeispielProzedurMitParameter ''''Beispielparameter''''
Beispielparameter ist aber nicht bei jedem Aufruf gleich, sonst brauchten Sie ja keinen Parameter. Es wird also immer ein anderer Wert übergeben – die ID eines zu löschenden Datensatzes, das Vergleichskriterium für eine SELECT-Abfrage et cetera.
Das bedeutet, dass Sie den SQL-Text der Abfrage mit jedem Aufruf neu erstellen müssen.
Als weiteres Kriterium kommt hinzu, dass sich die Verbindungszeichenfolge bei der Arbeit mit einer Kombination aus Access-Frontend und SQL Server-Backend ändern kann – sei es, weil sich der Name des Servers, der Name der Datenbank, die Authentifizierungsmethode oder der zu verwendende Treiber ändert. Die Verbindungszeichenfolge wird an vielen Stellen verwendet, vor allem aber als Eigenschaft der Pass-Through-Abfragen, die sich im Laufe der Entwicklung einer Access-Anwendung mit SQL Server-Backend ansammeln werden.
Aber brauchen Sie all diese Pass-Through-Abfragen überhaupt Letztlich müssen die meisten ohnehin jeweils mit einem neuen SQL-Ausdruck gefüllt werden, da diese etwa verschiedene Parameter verwenden. Warum also den Navigationsbereich mit hunderten von Pass-Through-Abfragen füllen, wenn man diese auch jeweils temporär erzeugen kann Die Antwort ist: Irgendwo müssen wir ja den Code für den Zugriff auf die gespeicherten Abfragen speichern. Dazu bietet sich jeweils eine Pass-Through-Abfrage je gespeicherter Abfrage durchaus an.
Wir verwenden aber später dennoch einen Satz von VBA-Funktionen und -Prozeduren, welche die jeweiligen Pass-Through-Abfragen untersuchen, den enthaltenen T-SQL-Code zum Aufruf der gespeicherten Prozedur entnehmen und die Pass-Through-Abfrage auf Basis der zu übergebenden Parameterwerte und der Verbindungszeichenfolge neu erzeugen und das gewünschte Objekt zurückliefern. Denn: Für den Zugriff auf eine gespeicherte Prozedur benötigen wir gar keine spezielle gespeicherte Pass-Through-Abfrage, sondern lediglich eine zur Laufzeit erstellte Abfrage, die wir mit dem Schlüsselwort EXEC, dem Namen der gespeicherten Prozedur sowie den gegebenenfalls benötigten Parameterwerten füllen.
In den folgenden Abschnitten sehen wir uns an, wie Sie den Zugriff auf gespeicherte Prozeduren per VBA vereinfachen können. Insgesamt stellen wir dort die folgenden Prozeduren vor, die sich allesamt im Modul mdlToolsSQLServer befinden:
- Ausführen einer gespeicherten Prozedur ohne Parameter und Rückgabe des Ergebnisses als Recordset
- Ausführen einer gespeicherten Prozedur mit Parameter und Rückgabe des Ergebnisses als Recordset
- Erstellen einer Pass-Through-Abfrage und Rückgabe des Namens der Abfrage, etwa als Wert der Eigenschaft RecordSource (Formulare, Berichte) oder RowSource (Kombinationsfeld, Lis-ten-feld), ohne Parameter
- Erstellen derselben Pass-Through-Abfrage, diesmal mit der übergabe von Parametern
Recordset aus gespeicherter Prozedur
Die nächste Variante, per Recordset auf die Daten einer SQL Server-Tabelle zuzugreifen, ist der Zugriff auf eine gespeicherte Prozedur (Stored Procedure) über eine Pass-Through-Abfrage.
Eine solche Pass-Through-Abfrage müssen Sie zunächst einmal erstellen. Da wir davon ausgehen, dass Sie neben LocalDB und/oder SQL Server auch das SQL Server Managment Studio installiert haben, erstellen wir die neue gespeicherte Prozedur einfach von dort aus.
Dazu starten Sie das SQL Server Management Studio, verbinden sich mit der entsprechenden Instanz (in unserem Fall mit (localdb)\MSSQLLocalDB) und wechseln zur Datenbank Suedsturm.
Gespeicherte Prozedur erstellen
Hier öffnen Sie das Datenbank-Element Suedsturm und darunter die Elemente Programmierbarkeit und Gespeicherte Prozeduren. Letzteres Element bietet im Kontextmenü den Eintrag Gespeicherte Prozedur an, mit der Sie eine neue Prozedur erstellen können (s. Bild 2).
Bild 2: Anlegen einer gespeicherten Prozedur
Sie können dies allerdings auch direkt über eine neue Abfrage erledigen, die Sie über den Kontextmenü-Eintrag Neue Abfrage des Suedsturm-Elements öffnen. Hier tragen Sie die T-SQL-Anweisung ein, mit der Sie die gespeicherte Abfrage erstellen und die wie folgt aussieht:
CREATE PROC dbo.pAlleArtikel
AS
SELECT dbo.tblArtikel.ArtikelID,
dbo.tblArtikel.Artikelname,
dbo.tblArtikel.AufgenommenAm,
dbo.tblArtikel.Auslaufartikel,
dbo.tblArtikel.BestellteEinheiten,
dbo.tblArtikel.Einzelpreis,
dbo.tblArtikel.KategorieID,
dbo.tblArtikel.Lagerbestand,
dbo.tblArtikel.LieferantID,
dbo.tblArtikel.Liefereinheit,
dbo.tblArtikel.Mindestbestand
FROM dbo.tblArtikel;
Danach führen Sie die Abfrage mit der Taste F5 aus (s. Bild 3). Sie finden nun nach einer Aktualisierung einen neuen Eintrag namens pAlleArtikel unter dem Element Suedsturm|Pro-gram-mierbarkeit|Gespeicherte Prozeduren.
Bild 3: Testen einer gespeicherten Prozedur
Sie können diese testen, indem Sie in einem Abfragefenster die Anweisung EXEC pAlleArtikel; eingeben und diese mit F5 ausführen.
Pass-Through-Abfrage erstellen
Nun erstellen wir in der Access-Datenbank eine Pass-Through-Abfrage, mit der wir über die gespeicherte Prozedur auf die Daten der Tabelle tblArtikel zugreifen wollen.
Legen Sie dazu eine neue, leere Abfrage in der Entwurfsansicht an und schließen Sie den automatisch erscheinenden Dialog Tabelle anzeigen. Nun klicken Sie auf den Ribbon-Eintrag Entwurf|Abfragetyp|Pass-Through (s. Bild 4).
Bild 4: Umwandeln einer Abfrage in eine Pass-Through-Abfrage
Dies ändert vor allem die Ansicht der Abfrage, nämlich in die SQL-Ansicht. Hier sind nun zwei Schritte zu erledigen: Als Erstes geben Sie die Anweisung ein, mit der Sie zuvor schon die gespeicherte Prozedur im SQL Server Management Studio getestet haben, also EXEC dbo.pAlleArtikel. Außerdem müssen Sie der Abfrage noch mitteilen, woher sie ihre Daten beziehen soll. Dies erledigen wir per Hand über die Eigenschaft ODBC-Verbindung, der wir in unserem Beispiel die folgende Zeichenkette übergeben:
ODBC;DRIVER={ODBC Driver 11 for SQL Server};SERVER=(localdb)\MSSQLLocalDB;DATABASE=Suedsturm;Trusted_Connection=Yes
Schließlich muss noch die Eigenschaft Liefert Datensätze auf den Wert Ja eingestellt sein (s. Bild 5).
Bild 5: Eigenschaften der Pass-Through-Abfrage
Anschließend können Sie die Datenblattansicht dieser Abfrage aktivieren und erhalten das gewünschte Ergebnis (s. Bild 6).
Bild 6: Ergebnis der Pass-Through-Abfrage
Per Recordset auf gespeicherte Prozedur zugreifen
Nun wollen wir auch per VBA über die Pass-Through-Abfrage auf die von der gespeicherten Prozedur gelieferten Daten zugreifen.
Auf diese Pass-Through-Abfrage greifen Sie wie folgt zu und durchlaufen dann ihre Datensätze über das zuvor gefüllte Recordset-Objekt:
Public Sub GespeicherteProzedurPerRecordset() Dim db As DAO.Database Dim rst As DAO.Recordset Set db = CurrentDb Set rst = db.OpenRecordset("ptSELECTAlleArtikel") Do While Not rst.EOF Debug.Print rst!ArtikelID, rst!Artikelname rst.MoveNext Loop rst.Close Set rst = Nothing Set db = Nothing End Sub
Hier geschieht rein oberflächlich nicht viel anderes, als wenn Sie wie weiter oben auf eine verknüpfte Tabelle zugreifen – mit dem Unterschied, dass wir hier den Namen der Pass-Through-Abfrage der OpenRecordset-Methode verwenden.
Vorbereitend auf weitere Varianten, in denen wir beispielsweise Parameter verwenden wollen, die in den Entwurf der Abfragen einfließen müssen, schauen wir uns nun eine Alternative an, bei der wir zunächst ein QueryDef-Objekt auf Basis der Pass-Through-Abfrage ptSELECTAlleArtikel erstellen und erst dann mit der OpenRecordset-Methode auf diesem Objekt ein Recordset erstellen:
Public Sub GespeicherteProzedurPerQueryDef() Dim db As DAO.Database Dim qdf As DAO.QueryDef Dim rst As DAO.Recordset Set db = CurrentDb Set qdf = db.QueryDefs("ptSELECTAlleArtikel") Set rst = qdf.OpenRecordset() Do While Not rst.EOF Debug.Print rst!ArtikelID, rst!Artikelname rst.MoveNext Loop rst.Close Set rst = Nothing Set qdf = Nothing Set db = Nothing End Sub
Was geschieht bei geänderter Verbindungszeichenfolge
Wenn Sie die Anwendung auf Ihrem Rechner entwickeln und diese dann beispielsweise an den Kunden weitergeben, werden Sie voraussichtlich eine andere Verbindungszeichenfolge nutzen müssen.
In diesem Fall haben Sie ein Problem: Wir haben ja weiter oben beschrieben, wie Sie einer Pass-Through-Abfrage die Verbindungszeichenfolge mitteilen – und zwar durch direktes Eintragen dieser Zeichenfolge in die Eigenschaft ODBC-Verbindung. Wenn Sie nun eine ganze Reihe dieser Pass-Through-Abfragen nutzen, müssten Sie diese vor jeder Weitergabe an andere Anwender ändern. Diese Arbeit wollen Sie nicht wirklich erledigen, also automatisieren wir dies. Dazu erstellen wir eine Prozedur namens PTAbfrageAktualisieren, die wie in Listing 1 aussieht.
Public Sub PTAbfrageAktualisieren(strPTAbfrage As String, strVerbindungszeichenfolge As String) Dim db As DAO.Database Dim qdf As DAO.QueryDef Dim intStart As Integer Dim intEnde As Integer Dim strVerbindungOhnePWD As String intStart = InStr(1, strVerbindungszeichenfolge, ";PWD") If intStart > 0 Then strVerbindungOhnePWD = Left(strVerbindungszeichenfolge, intStart) intEnde = InStr(intStart + 1, strVerbindungszeichenfolge, ";") If Not intEnde = 0 Then strVerbindungOhnePWD = strVerbindungOhnePWD & Mid(strVerbindungszeichenfolge, intEnde + 1) End If Else strVerbindungOhnePWD = strVerbindungszeichenfolge End If Set db = CurrentDb Set qdf = db.QueryDefs(strPTAbfrage) qdf.Connect = strVerbindungOhnePWD End Sub
Listing 1: Prozedur zum Aktualisieren von Pass-Through-Abfragen
Die Funktion erwartet den Namen der Pass-Through-Abfrage und die Verbindungszeichenfolge als Parameter. Da diese den weiter oben vorgestellten Techniken zufolge mit der Funktion VerbindungszeichenfolgeNachID oder Standardverbindungszeichenfolge zusammengestellt wird, enthält sie bei Benutzung der SQL Server-Authentifizierung gegebenenfalls das Kennwort des aktuellen Be-nut-zers.
Daher enthält die Funktion einige Zeilen Code, welche die Verbindungszeichenfolge auf das Auf-treten des Ausdrucks ;PWD= untersuchen. Ist dieser vorhanden, wird eine neue Ver-bin-dungs-zeichenfolge namens strVerbindungOhne-PWD zusammengestellt, die alle Zeichen bis zum Auf-tre-ten von ;PWD= enthält und gegebenenfalls alle Zeichen hinter dem nachfolgenden Semi-ko-lon.
Dazu ermittelt die Prozedur zunächst mit der InStr-Funktion die Position des ersten Auftretens der Zeichenfolge ;PWD. Ist diese ungleich 0, enthält die Zeichenfolge dieses Element. Eine neue String-Variable namens strVerbindungOhnePWD nimmt dann zunächst den Inhalt der Verbindungszeichenfolge bis zum Beginn von ;PWD auf. Dann ermittelt sie die Position des ersten Semikolons hinter der Zeichenfolge ;PWD, also das Ende des entsprechenden Name-Wert-Paares.
Hat das Ergebnis des entsprechenden Aufrufs der InStr-Funktion den Wert 0, ist die Angabe von PWD das letzte Element der Verbindungszeichenfolge und es sind keine weiteren Schritte nötig – die Verbindungszeichenfolge exklusive PWD-Element befindet sich nun in der Variablen strVerbindungOhnePWD. Anderenfalls gibt es noch mindestens ein weiteres Element hinter dem Name-Wert-Paar mit dem Kennwort – also wird der komplette Rest hinter der ermittelten Position an den bisherigen Inhalt von strVerbindungOhnePWD angehängt.
Nachdem das PWD-Element aus der Verbindungszeichenfolge entfernt und in der Variablen strVerbindungOhnePWD gespeichert wurde, referenziert die Prozedur PTAbfrageAktualisieren die zugrunde liegende Pass-Through-Abfrage und stellt die Verbindungszeichenfolge mit der Connect-Eigenschaft auf den neuen Wert ein.
Die Beispieldatenbank enthält beispielsweise in der Tabelle tblVerbindungszeichenfolgen zwei Beispielverbindungen mit den ID-Werten 9 und 10. Wenn Sie die erste davon verwenden wollen, rufen Sie die Prozedur wie folgt auf:
PTAbfrageAktualisieren "PTSELECTAlleArtikel", VerbindungszeichenfolgeNachID(9)
Wollen Sie dann wechseln, nutzen Sie die andere ID:
PTAbfrageAktualisieren "PTSELECTAlleArtikel", VerbindungszeichenfolgeNachID(10)
Sie können natürlich auch direkt die als Standardverbindungszeichenfolge deklarierte Verbindungszeichenfolge als Parameter angeben:
PTAbfrageAktualisieren "PTSELECTAlleArtikel", Standardverbindungszeichenfolge
Sie können sich zwischendurch den Entwurf der Pass-Through-Abfrage ansehen – die Eigenschaft ODBC-Verbindung enthält jeweils die entsprechende Verbindungszeichenfolge.
Alle Pass-Through-Abfragen aktualisieren
Wenn Sie mit dieser Prozedur alle Pass-Through-Abfragen aktualisieren wollen, durchlaufen Sie in einer weiteren Prozedur alle Einträge der Tabelle MSysObjects, deren Feld Connect einen Wert enthält, der mit ODBC= beginnt, und rufen von dort für jeden Eintrag die Prozedur PT-Ab-frageAktualisieren auf (s. Listing 2).
Public Sub AlleAbfragenAktualisieren() Dim db As DAO.Database Dim qdf As DAO.QueryDef Set db = CurrentDb For Each qdf In db.QueryDefs If InStr(Nz(qdf.Connect,""), "ODBC;") > 0 Then PTAbfrageAktualisieren qdf.Name, Standardverbindungszeichenfolge End If Next qdf End Sub
Listing 2: Prozedur zum Aktualisieren aller Pass-Through-Abfragen mit der aktuellen Standardverbindungszeichenfolge
Wann aktualisieren
Es stellt sich die Frage, wann man etwa die Verbindungszeichenfolge von Pass-Through-Abfragen aktualisiert. Die performanteste Lösung wäre wohl, wenn man dies nur dann durchführt, wenn sich die Standardverbindungszeichenfolge der Anwendung ändert. Wenn Sie also etwa die Instanz der Anwendung auf Ihrem Rechner auf die Weitergabe zum Kunden vorbereiten, könnten Sie einfach die Standardverbindungszeichenfolge ändern (etwa mithilfe des Formulars frmVerbindungszeichenfolgen, das wir im Beitrag SQL Server-Tools, www.access-im-unternehmen.de/1061, vorstellen) und danach die Prozedur AlleAbfragenAktualisieren aufrufen.
Gegebenenfalls fügen Sie den Aufruf dieser Prozedur sogar zu der Prozedur hinzu, die im Formular die aktuelle Verbindung als Standardverbindung einstellt.
Ende des frei verfügbaren Teil. Wenn Du mehr lesen möchtest, hole Dir ...
Testzugang
eine Woche kostenlosen Zugriff auf diesen und mehr als 1.000 weitere Artikel
diesen und alle anderen Artikel mit dem Jahresabo