RDBMS-Zugriff per VBA: Daten abfragen

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.