ADODB: Gespeicherte Prozeduren ausführen

Recordsets sind zentrale Bestandteile beim Zugriff auf Daten mit ADODB. Sie bieten umfangreiche Funktionen zur Navigation, Bearbeitung, Filterung und Analyse von Daten. In diesem Artikel zeigen wir eine vollständige Übersicht aller Eigenschaften und Methoden des Recordset-Objekts und erläutern diese jeweils ausführlich mit praktischen Beispielen. Besonderes Augenmerk legen wir auf die Ereignisse eines Recordsetes. Im Gegensatz zum DAO-Recordset bietet das ADODB-Recordset nämlich die Möglichkeit, auf verschiedene Ereignisse zu reagieren – beispielsweise auf Änderungen im Datensatz.

Beispieldatenbank

Die Beispiele dieses Artikels findest Du in der Beispieldatenbank ADODB_GespeicherteProzedurenAusfuehren.accdb.

Anwendungszweck für die vorgestellten Beispiele

Die einfachste Möglichkeit, Daten aus Tabellen einer SQL Server-Datenbank in Access anzuzeigen und zu bearbeiten ist die Verwendung von Tabellenverknüpfungen per ODBC.

Diese kann man einfach als Datensatzquelle von Formularen, Berichten oder Steuerelementen angeben – genau wie bei herkömmlichen Access-Tabellen.

Manchmal ist jedoch eine ungebundene Darstellung gewünscht, beispielsweise weil man aus Sicherheitsgründen keine Tabellenverknüpfungen im Frontend verfügbar machen möchte, weil diese einfach ausgelesen werden können.

Für diesen Fall bietet es sich an, die Daten auf SQL Server-Seite per gespeicherter Prozedur bereitzustellen und diese per ADODB-Recordset in Formularen und Steuerelementen anzuzeigen.

Für die Anzeige etwa der Daten eines Mitarbeiters könnte man ein solches Recordset mit ADODB per Zugriff auf eine entsprechende gespeicherte Prozedur füllen und die Daten der einzelnen Felder in ungebundenen Steuerelementen anzeigen.

Möchte man diese wieder speichern, verwendet man eine entsprechende gespeicherte Prozedur, welche die per Parameter übergebenen Daten über eine UPDATE-Anweisung in der Tabelle im SQL Server speichert.

Das Gleiche gilt für neu angelegte Datensätze, wobei man hier eine INSERT INTO-Anweisung nutzt. Und mit DELETE können wir einen angegebenen Datensatz auch wieder löschen.

Die nachfolgenden Beispiele zeigen, wie die gespeicherten Prozeduren aufgebaut sein müssen und wie wir diese per VBA und ADODB nutzen können.

Beispieldatenbank auf dem SQL Server anlegen

Um die Beispiele dieses Artikels auszuprobieren, benötigst Du eine SQL-Server-Datenbank mit einigen Tabellen und den entsprechenden gespeicherten Prozeduren. Im Modul mdlSQLServerDB findest Du den Code, den Du im SQL Server Management Studio ausführen kannst, um die Tabellen mit Beispieldaten und die gespeicherten Prozeduren anlegen kannst.

Dazu gehst Du wie folgt vor:

• Lege im SQL Server eine neue Datenbank namens Test_GespeicherteProzeduren an.
• Öffne eine neue Abfrage im Kontext dieser Datenbank.
• Gehe in das Modul mdlSQLServerDB und entferne die Kommentarzeichen – am einfachsten durch Markieren des Inhalts und Auswahl des Kontextmenü-Eintrags Auskommentierung des Blocks aufheben (sollte dieser nicht sichtbar sein, blende mit Ansicht|Symbolleisten|Bearbeiten die entsprechende Symbolleiste ein).
• Kopiere den Code in das Abfragefenster im SQL Server Management Studio.
• Führe den Code mit F5 aus. Dies legt alle benötigten Objekte in der Datenbank an (siehe Bild 1).



Bild 1: Die SQL Server-Datenbank mit den angelegten Objekten

Das Datenmodell ist schnell erklärt: Die Tabelle tblMitarbeiter ist die Haupttabelle.

Sie ist über das Feld AbteilungID mit der Tabelle tblAbteilungen verknüpft.

Definieren von Konstanten für Server und Datenbank

Da wir in allen VBA-Prozeduren auf den gleichen Server und die gleiche Datenbank zugreifen wollen, definieren wir diese als Konstanten:

Public Const cStrServer As String = "amvDesktop2023"
Public Const cStrDatabase As String = "Test_GespeicherteProzeduren"

Diese müssen an die jeweiligen Gegebenheiten angepasst werden.

Aufbau der gespeicherten Prozeduren

Es gibt vier verschiedene Arten von gespeicherten Prozeduren:

• zum Ermitteln der Daten einer Tabelle,
• zum Anlegen eines Datensatzes,
• zum Bearbeiten eines Datensatzes und
• zum Löschen eines Datensatzes.

Gespeicherte Prozedur zum Ermitteln der Daten einer Tabelle

In diesem Beispiel wollen wir direkt mit einer gespeicherten Prozedur einsteigen, mit der wir einen oder alle Einträge der Tabelle tblMitarbeiter ermitteln. Die gespeicherte Prozedur heißt spMitarbeiterSelectNachMitarbeiterID und ist in Listing 1 abgebildet.

CREATE PROCEDURE [dbo].[spMitarbeiterSelectNachMitarbeiterID]
    @MitarbeiterID INT = NULL
AS
SET NOCOUNT ON
SELECT  MitarbeiterID,
        Vorname,
        Nachname,
        Email,
        Eintrittsdatum,
        Gehalt,
        ErstelltVon,
        ErstelltAm
FROM    tblMitarbeiter  
WHERE   (@MitarbeiterID IS NULL OR MitarbeiterID = @MitarbeiterID)
ORDER BY Nachname, Vorname

Listing 1: Gespeicherte Prozedur zum Ermitteln von Mitarbeitern

Sie nimmt den Parameter @MitarbeiterID entgegen und wertet diesen in der WHERE-Klausel aus.

Hier verwenden wir einen Trick, um sicherzustellen, dass die gespeicherte Prozedur auf zwei Arten arbeitet:

• erstens um alle Datensätze der Tabelle zurückzugeben, wenn kein Parameter übergeben wurde und
• zweitens, um nur den Datensatz mit dem per Parameter übergebenen Wert zu ermitteln.

Wie können wir das in einer gespeicherten Prozedur berücksichtigen? Indem wir den Parameter @MitarbeiterID als optionalen Parameter definieren, der als Standardwert NULL erhält.

Diesen werten wir in der WHERE-Klausel so aus, dass wir prüfen, ob entweder @MitarbeiterID gleich NULL ist oder der Wert des Feldes MitarbeiterID gleich @MitarbeiterID ist.

Wird kein Parameter übergeben, trifft für jeden Datensatz die Bedingung @MitarbeiterID IS NULL zu und alle Datensätze zurückgegeben. Ist @MitarbeiterID nicht NULL, wird nur der Datensatz zurückgegeben, dessen Wert im Feld MitarbeiterID dem Wert aus @MitarbeiterID entspricht.

Aufruf der SELECT-Prozedur per VBA

Um die gespeicherte Prozedur spMitarbeiterSelectNachMitarbeiterID per ADODB aufzurufen, sieht der Code wie in Listing 2 aus. Zunächst wird eine Verbindung zur SQL-Server-Datenbank aufgebaut, indem ein neues ADODB.Connection-Objekt erstellt und mit dem Verbindungsstring geöffnet wird.

Public Sub Beispiel_Select()
    Dim cnn As ADODB.Connection
    Dim cmd As ADODB.Command
    Dim rst As ADODB.Recordset
    Dim strConnection As String
    Set cnn = New ADODB.Connection
    strConnection = "Provider=SQLOLEDB;Data Source=" & cStrServer _
        & ";Initial Catalog=" & cStrDatabase & ";Integrated Security=SSPI;"
    cnn.Open strConnection
    Set cmd = New ADODB.Command
    cmd.ActiveConnection = cnn
    cmd.CommandText = "spMitarbeiterSelectNachMitarbeiterID"
    cmd.CommandType = adCmdStoredProc
    Set rst = New ADODB.Recordset
    rst.CursorLocation = adUseClient
    rst.Open cmd, , adOpenStatic, adLockOptimistic
    Debug.Print "Datensätze: " & rst.RecordCount
    Do While Not rst.EOF
        Debug.Print rst!Nachname
        rst.MoveNext
    Loop
    rst.Close
    cnn.Close
    Set rst = Nothing
    Set cmd = Nothing
    Set cnn = Nothing
End Sub

Listing 2: Aufruf der gespeicherten Prozedur zum Ermitteln aller Mitarbeiter

Anschließend wird ein ADODB.Command-Objekt erstellt, dem die Verbindung, der Name der gespeicherten Prozedur sowie der Befehlstyp adCmdStoredProc zugewiesen werden. Der Befehlstyp teilt ADODB mit, dass es sich um eine gespeicherte Prozedur handelt und nicht etwa um eine direkte SQL-Anweisung.

Danach wird ein ADODB.Recordset-Objekt erstellt. Die Eigenschaft CursorLocation wird auf adUseClient gesetzt, was bedeutet, dass die Daten vollständig auf den Client übertragen werden – dies ist notwendig, damit die Eigenschaft RecordCount die korrekte Anzahl der Datensätze liefert. Mit adOpenStatic und adLockOptimistic wird das Recordset schließlich geöffnet.

Anschließend kann die Anzahl der zurückgegebenen Datensätze über RecordCount ausgegeben und die Datensätze in einer Schleife durchlaufen werden.

Am Ende werden Recordset und Verbindung sauber geschlossen und die Objektvariablen auf Nothing gesetzt, um den Speicher freizugeben.

Aufruf der SELECT-Prozedur mit Parameter

Die Prozedur Beispiel_Select_NachMitarbeiterID in Listing 3 zeigt, wie der Aufruf mit einem konkreten Parameter funktioniert.

Public Sub Beispiel_Select_NachMitarbeiterID()
    Dim cnn As ADODB.Connection
    Dim cmd As ADODB.Command
    Dim rst As ADODB.Recordset
    Dim prm As ADODB.Parameter
    Dim strConnection As String
    Dim lngMitarbeiterID As Long
    lngMitarbeiterID = 1
    Set cnn = New ADODB.Connection
    strConnection = "Provider=SQLOLEDB;Data Source=" & cStrServer & ";Initial Catalog=" & cStrDatabase _
        & ";Integrated Security=SSPI;"    
    cnn.Open strConnection
    Set cmd = New ADODB.Command
    cmd.ActiveConnection = cnn
    cmd.CommandText = "spMitarbeiterSelectNachMitarbeiterID"
    cmd.CommandType = adCmdStoredProc
    Set prm = cmd.CreateParameter("@MitarbeiterID", adInteger, adParamInput, _
        4, lngMitarbeiterID)
    cmd.Parameters.Append prm
    Set rst = New ADODB.Recordset
    rst.CursorLocation = adUseClient
    rst.Open cmd, , adOpenStatic, adLockOptimistic
    Debug.Print "Datensätze: " & rst.RecordCount
    Do While Not rst.EOF
        Debug.Print rst!Nachname
        rst.MoveNext
    Loop
    rst.Close
    cnn.Close
    Set rst = Nothing
    Set cmd = Nothing
    Set cnn = Nothing
End Sub

Listing 3: Aufruf der gespeicherten Prozedur zum Ermitteln eines Mitarbeiters

Der Aufbau ist identisch mit dem vorherigen Beispiel – Verbindung öffnen, Command-Objekt konfigurieren – mit einem wesentlichen Unterschied:

Vor dem Öffnen des Recordsets wird der Parameter @MitarbeiterID mit dem gewünschten Wert übergeben, um nach diesem zu filtern.

In diesem Beispiel ist das der Wert 1, der in der Variablen lngMitarbeiterID gespeichert ist.

Um einen Parameter zu übergeben, deklarieren wir zuvor eine Variable namens prm mit dem Datentyp Parameter. Den Parameter erstellen wir mit der Funktion CreateParameter. Dieser übergeben wir die folgenden Parameter:

• Name des Parameters, wie er in der gespeicherten Prozedur benannt wurde, hier @MitarbeiterID
• Datentyp des Parameters wie er in der gespeicherten Prozeduren verwendet wird, hier adInteger
• Angabe, ob es sich um einen Eingabe- oder Ausgabeparameter oder beides handelt, hier adParamInput für einen Eingabeparameter
• Angabe der Größe des Wertes in Bytes, hier 4 für adInteger
• zu übergebender Wert, hier die Mitarbeiter-ID

Da die gespeicherte Prozedur den Parameter als optional mit dem Standardwert NULL definiert hat, liefert sie ohne Parameter alle Datensätze der Tabelle zurück. Wird hingegen ein konkreter Wert übergeben, schränkt die WHERE-Klausel das Ergebnis auf genau den Datensatz mit der entsprechenden MitarbeiterID ein. Das Recordset enthält in diesem Fall also genau einen Datensatz, sofern ein Mitarbeiter mit dieser ID existiert.

Wie im vorherigen Beispiel wird wieder die Anzahl der zurückgelieferten Datensätze ausgegeben. Außerdem geben wir den Nachnamen aller Datensätze aus.

Die hier statisch gehaltene Prozedur Beispiel_Select_NachMitarbeiterID können wir nun beispielsweise in eine Funktion umwandeln, der wir die Mitarbeiter-ID des zu ermittelnden Datensatzes übergeben und die das Recordset mit dem Ergebnis als Funktionswert zurückliefert.

Gespeicherte Prozedur zum Hinzufügen eines Mitarbeiters

Damit kommen wir zu den gespeicherten Prozeduren zum Manipulieren der vorhandenen Daten und wir starten mit dem Hinzufügen eines Datensatzes.

Die gespeicherte Prozedur spMitarbeiterInsert legt einen neuen Mitarbeiter in der Tabelle tblMitarbeiter an. In der Anweisung zum Erstellen dieser Prozedur aus Listing 4 definieren wir zunächst alle Parameter, mit denen wir die Daten für den neuen Datensatz übergeben wollen.

CREATE PROCEDURE [dbo].[spMitarbeiterInsert]
    @AbteilungID    INT,
    @Vorname        NVARCHAR(100),
    @Nachname       NVARCHAR(100),
    @Email          NVARCHAR(255)   = NULL,
    @Eintrittsdatum DATE            = NULL,
    @Gehalt         DECIMAL(10,2)   = NULL,
    @IDNEW          INT             = -1 OUTPUT
AS
SET NOCOUNT ON
INSERT INTO tblMitarbeiter (AbteilungID, Vorname, Nachname, Email, 
    Eintrittsdatum, Gehalt)
VALUES (@AbteilungID, @Vorname, @Nachname, @Email, @Eintrittsdatum, @Gehalt)
SET @IDNEW = SCOPE_IDENTITY()

Listing 4: Gespeicherte Prozedur zum Hinzufügen eines Mitarbeiters