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.

Was benötigen wir also im Vergleich zur vorherigen Variante Eigentlich müssen wir nur den Namen der zu verwendenden gespeicherten Prozedur zusätzlich übergeben, die Ver-bin-dungs-zeichenfolge und die Parameter werden ja bereits verarbeitet. Außerdem referenzieren wir nicht über die QueryDefs-Auflistung eine bestehende Abfrage, sondern erstellen mit CreateQueryDef eine neue – und zwar mit einer leeren Zeichenkette als Name. Die Prozedur sieht nun wie in Listing 3 aus. Sie erstellt mit der CreateQueryDef-Methode eine temporäre Abfrage, was wir dadurch erreichen, dass wir eine leere Zeichenfolge als Parameter übergeben.

Public Sub TemporaerePTSPMitParameterAusfuehren(strStoredProcedure As String, _
         lngVerbindungszeichenfolgeID As Long, _
         ParamArray varParameter() As Variant)
     Dim db As DAO.Database
     Dim qdf As DAO.QueryDef
     Dim strParameter As String
     Dim var As Variant
     Set db = CurrentDb
     Set qdf = db.CreateQueryDef("")
     For Each var In varParameter
         strParameter = strParameter & ", " & var
     Next var
     If Len(strParameter) > 0 Then
         strParameter = Mid(strParameter, 3)
     End If
     With qdf
         .Connect = VerbindungszeichenfolgeNachID(lngVerbindungszeichenfolgeID)
         .ReturnsRecords = False
         .SQL = "EXEC " & strStoredProcedure & " " & strParameter
         .Execute
     End With
     Set db = Nothing
End Sub

Listing 3: Prozedur, welche die angegebene gespeicherte Prozedur mit gegebener Verbindungszeichenfolge und Parametern ausführt

Für die Parameterliste verwenden wir im Kopf der Prozedur einen Parameter namens varParameter des Typs ParamArray, dem man beliebig viele durch Kommata getrennte Parameterwerte übergeben kann. Die damit übergebenen Werte setzt die Prozedur in einer For Each-Schleife über alle Elemente von varParameter zusammen und stellt jeweils ein Komma voran. Das erste Komma wird danach gegebenenfalls abgeschnitten. Die folgenden Zeilen stellen die Ver-bin-dungszeichenfolge ein, legen für ReturnRecords den Wert False fest und fügen das Schlüssel-wort EXEC, den Namen der gespeicherten Prozedur und die Parameterliste zusammen. Die Execute-Methode führt die Abfrage schließlich durch. Ein Beispielaufruf sieht etwa so aus:

TemporaerePTSPMitParameterAusfuehren  "spDELETEKategorieNachIDMitErgebnis",9,112

Um auch hier die Standardverbindungszeichenfolge zu verwenden, nutzen Sie folgenden Aufruf:

TemporaerePTSPMitParameterAusfuehren  "spDELETEKategorieNachIDMitErgebnis", _
 StandardverbindungszeichenfolgeID, 112

Autowert des zuletzt hinzugefügten Datensatzes ermitteln

Für verschiedene Zwecke ist es interessant, den Autowert des zuletzt hinzugefügten Datensatzes zu ermitteln. Dies gilt nur für die VBA-Varianten: also das Hinzufügen mit der DAO-Methode AddNew oder mit der per Execute aufgerufenen INSERT INTO– oder SELECT INTO-SQL-Anweisung. Die folgenden Abschnitte zeigen die Variante für lokale Tabellen sowie für das Hinzufügen von Datensätzen zu Tabellen einer SQL Server-Datenbank.

Variante I: AddNew/Update

Die erste Variante ist die oft verwendete DAO-Methode mit den beiden Anweisungen AddNew und Update, wobei zwischen diesen beiden Anweisungen die neuen Feldwerte angegeben werden. Dies ist die Access-Variante, bei welcher der Wert des Primärschlüsselfelds für den neuen Datensatz bereits nach dem Aufruf der AddNew-Methode belegt ist und ausgelesen werden kann – hier am Beispiel der lokalen Tabelle tblKategorien_Lokal:

Public Sub NeuerDatensatzMitID_Lokal()
     Dim db As DAO.Database
     Dim rst As DAO.Recordset
     Set db = CurrentDb
     Set rst = db.OpenRecordset( "SELECT * FROM tblKategorien_Lokal",  dbOpenDynaset)
     rst.AddNew
     Debug.Print "Neue KategorieID: " & rst!KategorieID
     rst!Kategoriename = "Beispielkategorie"
     rst!Beschreibung = "Beispielbeschreibung"
     rst.Update
     rst.Close
     Set rst = Nothing
     Set db = Nothing
End Sub

Führen wir diese Prozedur in einer Beispieldatenbank aus, die eine per ODBC verknüpfte SQL Ser-ver-Ta-belle namens tblKategorien enthält, liefert dies den Fehler aus Bild 7. Dies besagt, dass Sie auf eine SQL Server-Tabelle mit einem Primärschlüsselwert (IDENTITY) nur zu-grei-fen können, wenn Sie den Parameter dbSeeChanges verwenden. Diesen Fehler beheben wir in der folgenden Variante. Dort fügen wir in der Zeile mit der Open-Re-cordset-Methode zunächst den Wert dbSeeChanges für den dritten Parameter hinzu:

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