RDBMS-Zugriff per VBA: Daten bearbeiten

Im Beitrag »RDBMS-Zugriff per VBA: Verbindungen« haben wir die Grundlage für den Zugriff auf SQL Server-Datenbanken geschaffen. Zudem zeigt der Beitrag »RDBMS-Zugriff per VBA: Daten abfragen«, wie Sie die Daten einer SQL Server-Datenbank ermitteln. Im vorliegenden Teil dieser Beitragsreihe erfahren Sie nun, wie Sie die Daten einer SQL Server-Datenbank bearbeiten.

Aktionsabfragen

Aktionsabfragen sind Abfragen, die Daten ändern – also Lösch-, Aktualisierungs- und An­füge­ab­fra­gen. In reinen Access-Datenbanken führen Sie solche Abfragen aus, indem Sie diese mit dem Abfrage-Entwurf erstellen und direkt ausführen oder per VBA aufrufen oder indem Sie die gewünschte Abfrage als SQL-Ausdruck per Code zusammenstellen und dann mit der Execute-Methode des Database-Objekts ausführen. Für SQL Server-Daten gibt es die folgenden Arten der Ausführung:

• Erstellen einer Aktionsabfrage in Access, die sich auf die Daten einer per ODBC verknüpften Tabelle des SQL Servers bezieht,
• Erstellen einer Pass-Through-Abfrage, welche die Aktionsabfrage enthält und diese direkt an den SQL Server übermittelt,
• Erstellen einer gespeicherten Prozedur, welche die Aktionsabfrage enthält und die notwen­digen Parameter entgegen nimmt – also beispielsweise die ID eines zu löschenden Daten­satzes –, und die über eine Pass-Through-Abfrage aufgerufen wird.

Wenn es um die Performance geht, ist die erste Variante die langsamste, die zweite Version ist etwas schneller und die dritte Version ändert die Daten in der Regel am schnellsten. Aus diesem Grund schauen wir uns nachfolgend lediglich die zweite und die dritte Variante an.

Datensatz löschen per SQL

Bei der ersten Variante legen Sie eine Pass-Through-Abfrage mit der auszuführenden DELETE-Anweisung an (s. Bild 1).

Bild 1: Die neue PassThrough-Abfrage zum Löschen eines Datensatzes im SQL Server

Dazu sind folgende Schritte nötig:

• Erstellen einer neuen, leeren Abfrage und Schließen des Dialogs Tabelle anzeigen
• Wechseln des Abfragetyps auf Pass-Through
• Einstellen der Eigenschaft ODBC-Verbindung auf die gewünschte Verbindungszeichen­fol­ge (hier ODBC;DRIVER={SQL Server Native Client 11.0};SERVER=(localdb)MSSQLLocalDB;DATABASE=Suedsturm;Trusted_Connection=Yes)
• Einstellen der Eigenschaft Liefert Datensätze auf Nein
• Eintragen der DELETE-Anweisung

Die DELETE-Anweisung soll in unserem Fall wie folgt lauten:

DELETE FROM tblKategorien WHERE KategorieID = 12

Die Abfrage können Sie dann per VBA mit einer einzigen Anweisung ausführen:

CurrentDb.Execute "qryPTDeleteKategorie"

Sie können auch die Variante mit dem QueryDefs-Objekt verwenden:

CurrentDb.QueryDefs("qryPTDeleteKategorie").Execute

Damit haben Sie allerdings noch nicht viel gewonnen: Die Anweisung löscht ja nur genau den Datensatz, dessen ID Sie als Kriterium angegeben haben. Immerhin haben wir aber bereits eine Abfrage erstellt, die den richtigen Typ aufweist, die Ver­bin­dungszeichenfolge enthält und deren Eigenschaft Liefert Datensätze auf Nein eingestellt ist. Diese nutzen wir nun, um gezielt einen bestimmten Datensatz zu löschen. Die folgende Prozedur (wie auch die weiteren Beispiele im Modul mdlRDBMSZugriff_DatenBearbeiten) erwartet den Primärschlüsselwert des zu löschenden Datensatzes als Parameter:

Public Sub KategorieLoeschen_PT(lngKategorieID As Long)

     Dim db As DAO.Database

     Dim qdf As DAO.QueryDef

     Set db = CurrentDb

     Set qdf = db.QueryDefs("qryPTDeleteKategorie")

     qdf.SQL = "DELETE FROM dbo.tblKategorien  WHERE KategorieID = " & lngKategorieID

     qdf.Execute

     Set qdf = Nothing

     Set db = Nothing

End Sub

Mit dieser Prozedur referenzieren wir die soeben erstellte Abfrage qryPTDeleteKategorie und ändern die enthaltene SQL-Anweisung so, dass diese als Kriterium den per Parameter übergebenen Primärschlüsselwert enthält.

Danach führen wir die geänderte Abfrage mit der Execute-Anweisung aus. Der Aufruf dieser Prozedur sieht etwa so aus:

KategorieLoeschen_PT 104

Diese Variante hat noch folgende Nachteile:

• Die an den SQL Server übergebene SQL-Anweisung wird dynamisch zusammengesetzt. Wenn sich die SQL-Anweisung dabei von einer bereits verwendeten unterscheidet, also etwa ein anderer Parameterwert zum Einsatz kommt, muss der Ausführungsplan neu erstellt werden.
• Die Verbindungszeichenfolge ist in der Abfrage gespeichert. Wenn sich diese ändert, muss sie in jeder Abfrage angepasst werden.
• Wir erfahren nicht, ob die Aktion erfolgreich war und wie viele Datensätze gelöscht wurden.

In den folgenden beiden Abschnitten kümmern wir uns um diese Nachteile.

Datensatz löschen per gespeicherter Prozedur

Als Erstes sorgen wir dafür, dass der SQL Server unabhängig vom übergebenen Parameter nur einen Ausführungsplan für die Abfrage erstellt, speichert und bei weiteren Aufrufen wiederverwendet. Dazu erstellen wir eine gespeicherte Prozedur, und zwar mit folgendem SQL-Skript:

CREATE PROCEDURE dbo.spDELETEKategorieNachID (@KategorieID int)

AS

SET NOCOUNT ON;

DELETE FROM tblKategorien 

WHERE KategorieID = @KategorieID;

Dieses Skript können Sie, wenn Sie es von Access aus ausführen möchten, in das Formular frmSQLBefehle eingeben und dann mit der Ausführen-Schaltfläche ausführen (s. Bild 2). Ob die gespeicherte Prozedur erfolgreich angelegt wurde, können Sie mit der folgenden Anweisung, ebenfalls in diesem Formular abgesetzt, prüfen:

Bild 2: Anlegen einer gespeicherten Prozedur per Access-Formular

SELECT * FROM Suedsturm.information_schema.routines 

WHERE routine_type = 'PROCEDURE'

Die gespeicherte Prozedur spDELETEKategorieNachID erwartet den Primärschlüsselwert des zu löschenden Datensatzes als Parameter. Wenn Sie die gespeicherte Prozedur direkt vom Abfragefenster des SQL Servers aus ausführen wollten, würden Sie dies mit folgender Anweisung erledigen:

EXEC dbo.spDELETEKategorieNachID 105

Sie können auch diese Abfrage im Formular frmSQLBefehle absetzen, aber es gibt noch eine andere Variante – zum Beispiel für den Fall, dass Sie diese gespeicherte Prozedur per Code aufrufen wollen.

Also erstellen Sie zunächst eine neue Abfrage, wandeln diese in eine Pass-Through-Abfrage um und legen den SQL-Ausdruck aus Bild 3 fest.

Bild 3: Aufruf einer gespeicherten Abfrage per Passthrough-Abfrage

In dieser Abfrage müssen Sie nun natürlich ebenfalls den Primärschlüsselwert des zu löschenden Datensatzes als Parameter angeben. Dies erledigen Sie ähnlich wie oben:

Public Sub KategorieLoeschen_PT_SP(lngKategorieID As Long)

     Dim db As DAO.Database

     Dim qdf As DAO.QueryDef

     Set db = CurrentDb

     Set qdf = db.QueryDefs("qryPTDeleteKategorie")

     qdf.SQL = "EXEC dbo.spDELETEKategorieNachID "  & lngKategorieID

     qdf.Execute

     Set qdf = Nothing

     Set db = Nothing

End Sub

Der Aufruf sieht beispielsweise wie folgt aus:

KategorieLoeschen_PT_SP 106

Dies ändert zunächst den SQL-Ausdruck der Abfrage ptKategorieLoeschen wie folgt:

EXEC dbo.spDELETEKategorieNachID 106

Dieser Aufruf wird direkt an den SQL Server gesendet, der dann die gespeicherte Prozedur spDELETEKategorie­NachID mit dem angegebenen Parameter ausführt und den entsprechenden Datensatz löscht.

Pass-Through-Abfrage mit dynamischer Verbindungszeichenfolge

Nun soll noch die Verbindungszeichenfolge direkt aus der Tabelle tblVerbindungszeichenfolgen bezogen werden (Erläuterungen zu dieser Tabelle siehe RDBMS-Zugriff per VBA: Verbindungen, www.access-im-unternehmen.de/1054). Dazu übergeben Sie der VBA-Prozedur noch die ID der Verbindungszeichenfolge als weiteren Parameter. Dieser Parameter wird an die in dem oben erwähnten Beitrag erläuterte Funktion Ver­bin­dungs­zeichenfolgeNachID übergeben, die dann die Verbindungszeichenfolge zurückliefert. Das Ergebnis landet direkt in der Eigenschaft Connect des QueryDef-Objekts, was dem Zuweisen der Verbindungszeichenfolge zur Eigenschaft ODBC-Verbindung entspricht. Die Prozedur finden Sie in Listing 1. Auch hier noch ein Beispielaufruf:

Public Sub KategorieLoeschenNachID_PT_SP_Connection(lngKategorieID As Long, _
         lngVerbindungszeichenfolgeID As Long)

     Dim db As DAO.Database

     Dim qdf As DAO.QueryDef

     Set db = CurrentDb

     Set qdf = db.QueryDefs("qryPTDELETEKategorie")

     qdf.Connect = VerbindungszeichenfolgeNachID(lngVerbindungszeichenfolgeID)

     qdf.SQL = "EXEC dbo.spDELETEKategorieNachID " & lngKategorieID

     qdf.Execute

     Set qdf = Nothing

     Set db = Nothing

End Sub

Listing 1: Aufruf einer gespeicherten Prozedur mit dynamischer Verbindungszeichenfolge

KategorieLoeschenNachID_PT_SP_Connection 107, 9

Dies löscht den Datensatz mit dem Wert 107 im Feld KategorieID und verwendet die Verbindungszeichenfolge mit dem Wert 9 im Feld VerbindungszeichenfolgeID der Tabelle tblVerbindungszeichenfolgen.

Sie können die Verbindungszeichenfolge natürlich auch mit der Funktion Standard­ver­bin­dungs­zeichenfolge ermitteln. Dazu ersetzen Sie die Zeile mit der Connect-Eigenschaft wie folgt:

qdf.Connect = Standardverbindungszeichenfolge

Oder Sie übergeben die Standardverbindungszeichenfolge beim Aufruf:

KategorieLoeschenNachID_PT_SP_Connection 108,  StandardverbindungszeichenfolgeID

Löschen mit Bestätigung

Schließlich möchten Sie vielleicht noch wissen, ob der Löschvorgang überhaupt erfolgreich war beziehungsweise wie viele Datensätze von der Aktionsabfrage betroffen waren. T-SQL bietet mit der Funktion @@ROWCOUNT ein Mittel, um die Anzahl der von der zuletzt ausgeführten Abfrage betroffenen Datensätze zu ermitteln. Dies bezieht sich auf die Aktionsabfragen der aktuellen Verbindung. Die folgende gespeicherte Prozedur löscht wie in den obigen Beispielen einen Datensatz mit dem übergebenen Wert für das Feld KategorieID, gibt aber als Ergebnis die Anzahl der betroffenen Datensätze zurück:

CREATE PROCEDURE dbo.spDELETEKategorieNachIDMitErgebnis

@KategorieID INT

AS

SET NOCOUNT ON;

DELETE FROM tblKategorien WHERE KategorieID = @KategorieID

SELECT @@ROWCOUNT AS RecordsAffected;

Wenn Sie diese gespeicherte Prozedur im Abfragefenster im SQL Server Management Studio aufrufen, sieht das Ergebnis wie in Bild 4 aus. Um dieses Ergebnis von Access aus zu nutzen, ist eine kleine Änderung am Entwurf der Pass-Through-Abfrage nötig.

Bild 4: Ergebnis einer gespeicherten Prozedur im SQL Server Management Studio

Wir haben die Abfrage von oben unter dem Namen qrySPDELETEKategorieNachIDMitErgebnis kopiert und die Eigenschaft Liefert Datensätze auf den Wert Ja eingestellt (s. Bild 5). Anderenfalls liefert die Abfrage das Ergebnis der SELECT-Abfrage mit der Anzahl der betroffenen Datensätze nicht zurück!

Bild 5: Entwurf der Passthrough-Abfrage zum Löschen eines Datensatzes mit Rückgabe der betroffenen Datensätze

Führen Sie diese Abfrage direkt aus, liefert sie das Ergebnis aus Bild 6.

Bild 6: Ergebnis der gespeicherten Prozedur innerhalb einer Pass-Through-Abfrage in Access

Dies ist ein Ergebnis, mit dem wir auch unter VBA arbeiten können. Die Prozedur aus Listing 2 verwendet wieder die KategorieID und ermittelt die Verbindungszeichenfolge mit der Funktion Stan­dard­verbindungszeichenfolge. Sie erzeugt wie gewohnt ein QueryDef-Objekt auf Basis einer neuen gespeicherten Access-Abfrage namens spDELETEKategorie­NachIDMitErgebnis und ermittelt die gewünschte Ver­bin­dungs­zei­chenfolge. Dann weist sie wie zuvor den neuen SQL-Ausdruck zu, führt die Abfrage aber diesmal nicht mit Execute aus. Stattdessen erstellt sie ein neues Recordset-Objekt und füllt es über die OpenRecordset-Methode mit dem Ergebnis der gespeicherten Prozedur. Dies erzeugt ein herkömmliches Recordset-Objekt, das nur einen Datensatz mit einem Feld enthält – und dieses wird mit rst!RecordsetAffected ausgelesen und in einem Meldungsfenster ausgegeben.

Public Sub KategorieLoeschenNachID_PT_SP_Connection_MitErgebnis(lngKategorieID As Long)

     Dim db As DAO.Database

     Dim qdf As DAO.QueryDef

     Dim rst As DAO.Recordset

     Dim lngAnzahl As Long

     Set db = CurrentDb

     Set qdf = db.QueryDefs("qryPTSPDELETEKategorieNachIDMitErgebnis")

     qdf.Connect = Standardverbindungszeichenfolge

     qdf.SQL = "EXEC dbo.spDELETEKategorieNachIDMitErgebnis " & lngKategorieID

     Set rst = qdf.OpenRecordset(dbOpenSnapshot)

     lngAnzahl = rst!RecordsAffected

     MsgBox "Es wurden " & lngAnzahl & " Datensätze gelöscht."

     Set rst = Nothing

     Set qdf = Nothing

     Set db = Nothing

End Sub

Listing 2: Aufruf einer gespeicherten Prozedur mit Rückgabewert

Dynamische Aktionsabfrage ohne Rückgabewert

Die bisherigen Ansätze gingen davon aus, dass die Access-Datenbank eine gespeicherte Access-Abfrage mit den wichtigsten Eigenschaften zum Ausführen der gespeicherten Prozedur per Pass-Through-Abfrage enthält. Je mehr solcher Abfragen Sie verwenden, desto unübersichtlicher wird es im Navigationsbereich. Und davon abgesehen ändern wir ohnehin zumindest den SQL-Code jeder Pass-Through-Abfrage, die eine gespeicherte Prozedur mit Parametern ausführt. Dann könnten wir diese auch gleich neu anlegen – der Performance-Unterschied dürfte sich in Grenzen halten. Es gibt jedoch auch die Möglichkeit, ein QueryDef-Objekt komplett temporär zu erzeugen.