Zur Hauptseite ... Zum Onlinearchiv ... Zum Abonnement ... Zum Newsletter ... Zu den Tools ... Zum Impressum ... Zum Login ...

Gedrucktes Heft

Diesen Beitrag finden Sie in Ausgabe 1/2017.

Unser Angebot für Sie!

Lesen Sie diesen Beitrag und 500 andere sofort im Onlinearchiv, und erhalten Sie alle zwei Monate brandheißes Access-Know-how auf 72 gedruckten Seiten! Plus attraktive Präsente, zum Beispiel das bald erscheinende Buch 'Access 2010 - Das Grundlagenbuch für Entwickler'!

Diesen Beitrag twittern

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).

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

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:

Anlegen einer gespeicherten Prozedur per Access-Formular

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.

Aufruf einer gespeicherten Abfrage per Passthrough-Abfrage

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.

Ergebnis einer gespeicherten Prozedur im SQL Server Management Studio

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!

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

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.

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

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:

Fehler beim Versuch, ein Recordset ohne die Option db­See­Changes zu öffnen

Bild 7: Fehler beim Versuch, ein Recordset ohne die Option db­See­Changes zu öffnen

Set rst = db.OpenRecordset("SELECT * FROM tblKatgorien", dbOpenDynaset, dbSeeChanges)

Die Prozedur läuft nun durch, die Debug.Print-Anweisung liefert jedoch keinen Wert für das Feld KategorieID, also das durch den SQL Server mit einem Autowert zu füllende Pri­mär­schlüsselfeld:

Debug.Print "Neue KategorieID: " & rst!KategorieID
Neue KategorieID:

Der Grund liegt darin, dass der SQL Server den Autowert für das Primärschlüsselfeld erst vergibt, wenn der Datensatz gespeichert wird. Hilft es also, wenn wir den Wert des Primärschlüsselfeldes erst nach dem Aufruf von Update abfragen? Nein – AddNew und Update verschieben den Da­ten­satzzeiger nicht auf den neuen Datensatz, sondern dieser verbleibt auf dem Datensatz, auf den der Zeiger vor dem Anlegen bereits zeigte (in diesem Fall auf den ersten Datensatz des Recordsets):

rst.AddNew
rst!Kategoriename = "Beispielkategorie1"
rst!Beschreibung = "Beispielbeschreibung"
rst.Update
Debug.Print "Neue KategorieID: " & rst!KategorieID

Hier kommt die Eigenschaft LastModified des DAO-Recordsets zum Einsatz. Stellen wir die Ei­gen­schaft Bookmark nach dem Einfügen des Datensatzes auf den Wert der Eigenschaft Last­Mo­di­fied ein, wird der Datensatzzeiger auf den neuen Datensatz verschoben und wir können den Primärschlüsselwert auslesen:

rst.Update
rst.Bookmark = rst.LastModified
Debug.Print "Neue KategorieID: " & rst!KategorieID

Fehler ermitteln unter DAO

Wenn Sie das obige Beispiel zweimal hintereinander ausführen, ohne den Code zu ändern, löst dies den Fehler aus Bild 8 aus. Die Fehlermeldung ODBC-Aufruf fehlgeschlagen weist lediglich darauf hin, dass beim Zugriff auf den SQL Server ein Fehler aufgetreten ist. Genauere Informationen erhalten Sie über die Errors-Auflistung der DAO-Bibliothek. Die Count-Eigenschaft liefert die Anzahl der Einträge – im Direktbereich etwa so (nach dem Quittieren der obigen Fehlermeldung mit einem Klick auf die Schaltfläche Debuggen):

Fehler beim Versuch, einen bereits vorhandenen Wert in ein eindeutiges Feld zu schreiben

Bild 8: Fehler beim Versuch, einen bereits vorhandenen Wert in ein eindeutiges Feld zu schreiben

  DAO.Errors.Count
  3 

Der letzte Fehler der Auflistung mit dem Index 2 ist der, den auch das Err-Objekt und die Fehlermeldung liefern:

  DAO.Errors(2).Number, DAO.Errors(2).Description
  3146         ODBC-Aufruf fehlgeschlagen.

Der zweite Fehler basiert auf folgender Nummer und Meldung:

  DAO.Errors(1).Number, DAO.Errors(1).Description
  3621         [Microsoft][SQL Server Native Client 11.0][SQL Server]Die Anweisung wurde beendet.

Auch diese Meldung liefert keine entscheidenden Informationen. Bleibt noch die Meldung mit dem Index 0:

  DAO.Errors(0).Number, DAO.Errors(0).Description
  2601         [Microsoft][SQL Server Native Client 11.0][SQL Server]Eine Zeile mit doppeltem _
Schlüssel kann in das dbo.tblKategorien-Objekt mit dem eindeutigen IX_tblKategorien-Index _
nicht eingefügt werden. Der doppelte Schlüsselwert ist (Beispielkategorie1).

Wir haben also schlicht versucht, einen Wert in das Feld Kategoriename einzutragen, der bereits vorhanden war. Und da für dieses Feld ein eindeutiger Index festgelegt wurde, löst der SQL Server einen entsprechenden Fehler aus.

Variante II: Execute/INSERT INTO

Unter Access verwendet man bei Datensatzänderungen, die durch INSERT INTO- und SELECT INTO-Abfragen durchgeführt wurden, eine spezielle Abfrage zum Ermitteln des Primärschlüsselwertes des zuletzt hinzugefügten Datensatzes. Ein Beispiel sieht wie folgt aus:

Public Sub NeuerDatensatzExecute()
     Dim db As DAO.Database
     Dim lngID As Long
     Set db = CurrentDb
     db.Execute "INSERT INTO tblKategorien(Kategoriename,  Beschreibung) VALUES('Beispielkategorie', " _
         & "'Beispielbeschreibung')", dbFailOnError
     lngID = db.OpenRecordset("SELECT @@IDENTITY").Fields(0)
     Debug.Print "Neuer Datensatz: " & lngID
End Sub

Die Codezeilen legen zunächst einen neuen Datensatz in der Tabelle tblKategorien an. Im Gegensatz zur entsprechenden Vorgehensweise mit den DAO-Methoden AddNew und Up­date erhalten Sie hier nicht automatisch die ID des neu angelegten Datensatzes. Diese ist aber häufig erforderlich, da gegebenenfalls weitere Datensätze angelegt werden sollen, die per Fremd­schlüsselwert auf diesen Datensatz verweisen, oder der neu angelegte Datensatz soll gleich nach dem Aktualisieren der Datenherkunft im Formular angezeigt werden. Access liefert die ID mit der Funktion @@IDENTITY, die Sie in Form einer SELECT-Abfrage abfragen.

Neue ID per SQL Server abfragen

Der SQL Server bietet glücklicherweise genau die gleiche Funktion an, um den zuletzt hinzugefügten Autowert zu ermitteln. Allein die Übergabe an Access stellt eine kleine Hürde dar, die wir allerdings leicht nehmen – zunächst mit der folgenden gespeicherten Prozedur als Beispiel:

CREATE PROCEDURE dbo.spINSERTINTOKategorie
@Kategoriename NVARCHAR(255), @Beschreibung NVARCHAR(255)
AS
SET NOCOUNT ON;
INSERT INTO tblKategorien(Kategoriename, Beschreibung) VALUES(@Kategoriename, @Beschreibung);

Wenn wir nun die gespeicherte Prozedur aufrufen und anschließend die bereits unter Access verwendete Abfrage zur Abfrage des neuen Identitätswertes, sieht das wie folgt aus:

EXEC dbo.spINSERTINTOKategorie 'Neue Kategorie', 'Neue Beschreibung'
SELECT @@IDENTITY AS KategorieID;

Das Abfragefenster zeigt nun den Wert des Feldes KategorieID des neuen Datensatzes an (s. Bild 9).

Anlegen eines neuen Datensatzes plus Ausgabe der neuen ID

Bild 9: Anlegen eines neuen Datensatzes plus Ausgabe der neuen ID

Neben @@IDENTITY bietet T-SQL noch zwei Al­ter­na­ti­ven. Es gibt insgesamt drei Funktionen, die ein ähnliches Ergebnis lie­fern:

  • @@IDENTITY: Liefert den Wert des zuletzt angelegten Autowertes. Meist ist dies der Wert, den wir suchen – aber es gibt Ausnahmen! Wenn Sie einen Trigger einsetzen, der durch das Anlegen des neuen Datensatzes ausgelöst wird und der wiederum einen neuen Datensatz in einer Tabelle mit einem Autowert-Feld einträgt, liefert @@IDENTITY den durch den Trigger erzeugten Autowert zurück. Die Funktion ist auf die aktuelle Session begrenzt und liefert nicht etwa durch andere Benutzer angelegte Autowerte zurück.
  • SCOPE_IDENTITY(): Diese Funktion ist ebenfalls auf die aktuelle Session beschränkt, aber berücksichtigt nur die Autowerte, die explizit erzeugt wurden – also durch INSERT INTO oder SELECT INTO-Anweisungen. Durch Trigger erzeugte Datensätze werden nicht berücksichtigt.
  • IDENT_CURRENT(): Der Vollständigkeit halber stellen wir auch noch diese Funktion vor. Sie liefert den zuletzt zu einer bestimmten Tabelle hinzugefügten Autowert. Sie erwartet die Angabe des Tabellennamens als Parameter, zum Beispiel IDENT_CURRENT('tblKategorien').

Verwenden wir also statt @@IDENTITY die Funktion SCOPE_IDENTITY() – die Klammern dürfen übrigens nicht weggelassen werden:

SELECT SCOPE_IDENTITY() AS KategorieID

Wie Bild 10 zeigt, funktioniert die Funktion nicht wie @@IDENTITY. In der Tat beschränkt sich der Gültigkeitsbereich von SCOPE_IDENTITY auf eine gespeicherte Prozedur, eine benutzerdefinierte Funktion oder einen Batch. Kein Problem: Packen wir die Rückgabe des Autowertes des neu angelegten Datensatzes also einfach mit in die gespeicherte Prozedur:

SCOPE_IDENTITY liefert nicht auf Anhieb den gewünschten Wert.

Bild 10: SCOPE_IDENTITY liefert nicht auf Anhieb den gewünschten Wert.

CREATE PROCEDURE dbo.spINSERTINTOKategorienMitID
@Kategoriename NVARCHAR(255), @Beschreibung NVARCHAR(255)
AS
SET NOCOUNT ON;
INSERT INTO tblKategorien(Kategoriename, Beschreibung) VALUES(@Kategoriename, @Beschreibung);
SELECT SCOPE_IDENTITY() AS KategorieID;

Dies liefert das gewünschte Ergebnis – der Aufruf der gespeicherten Prozedur spINSERTINTOKategorienMitID gibt gleich den neuen Autowert zurück (s. Bild 11).

Als Teil der gespeicherten Prozedur arbeitet SCOPE_IDENTITY hingegen wie gewünscht.

Bild 11: Als Teil der gespeicherten Prozedur arbeitet SCOPE_IDENTITY hingegen wie gewünscht.

Autowert per gespeicherter Prozedur von Access aus

Nun wissen wir schon einmal, wie wir den neuen Autowert – und somit meist auch den Primärschlüsselwert – im SQL Server ermitteln. Der Rest ist eine Kombination aus dem Erstellen eines QueryDef-Objekts und dessen Ausführung mit der OpenRecordset-Methode (s. Listing 4). Die Prozedur erstellt ein neues temporäres QueryDef-Objekt (also mit einer leeren Zeichenkette als Name). Für dieses sind einige Eigenschaften einzustellen:

Public Function NeuenDatensatzMitID_INSERT_InSP()
     Dim db As dao.Database
     Dim qdf As dao.QueryDef
     Dim rst As dao.Recordset
     Set db = CurrentDb
     Set qdf = db.CreateQueryDef("")
     With qdf
         .Connect = Standardverbindungszeichenfolge
         .ReturnsRecords = True
         .SQL = "EXEC spINSERTINTOKategorienMitID  'Neue Kategorie 123', 'Beschreibung'"
         Set rst = .OpenRecordset
         Debug.Print rst.Fields("KategorieID")
     End With
     Set db = Nothing
End Function

Listing 4: Prozedur zum Erstellen eines temporären QueryDef-Objekts

  • Connect: Wird mit der Funktion Standardver­bin­dungs­zei­chen­folge­ gefüllt.
  • ReturnsRecord: Obwohl die Abfrage eigentlich eine Aktion ausführen soll, möchten wir den Wert des neuen Primärschlüssels erhalten. Also stellen wir ReturnsRecords auf True ein.
  • SQL: Die Anweisung führt die gewünschte gespeicherte Prozedur mit der EXEC-Anweisung aus und gibt den Namen der anzulegenden Kategorie als Parameter an.

Anschließend führt die Prozedur die Abfrage aus, indem sie die OpenRecordset-Methode aufruft. Das Ergebnis in Form eines Recordsets wird mit der Objektvariablen rst referenziert. Die folgende Anweisung greift auf das einzige zurückgegebene Feld namens KategorieID zu und liest somit den Autowert des neuen Datensatzes aus.

Was aber geschieht, wenn wir die Pass-Through-Abfrage einfach nur ausführen möchten, ohne den neuen Autowert zu beziehen? Fliegt uns diese dann um die Ohren? Nein – es sind nur ein paar kleinere Änderungen nötig. Die folgende Variante stellt ReturnRecords auf False ein, außerdem wird kein Recordset erstellt, sondern die VBA-Prozedur einfach nur mit Execute ausgeführt:

Public Function NeuenDatensatzMitID_INSERT_InSP_ OhneRueckgabewert()
     Dim db As dao.Database
     Dim qdf As dao.QueryDef
     Dim rst As dao.Recordset
     Set db = CurrentDb
     Set qdf = db.CreateQueryDef("")
     With qdf
         .Connect = Standardverbindungszeichenfolge
         .ReturnsRecords = False
         .SQL = "EXEC dbo.spINSERTINTOKategorienMitID  'Neue Kategorie 124', 'Beschreibung'"
         .Execute
     End With
     Set db = Nothing
End Function

Autowert-Abfrage per Code an gespeicherte Prozedur anhängen

Nicht alle gespeicherten Prozeduren, die Aktionsabfragen wie beispielsweise eine INSERT INTO- oder SELECT INTO-Abfrage ausführen, enthalten gleich eine entsprechende SELECT-Anweisung, welche den zuletzt hinzugefügten Autowert ermittelt. Es kann beispielsweise sein, dass Sie ein Access-Frontend gegen ein SQL Server-Back­end programmieren müssen, in dem Sie keine Änderungen durchführen können oder dürfen. Wie hängen Sie dennoch eine Anweisung wie SELECT SCOPE_IDENTITY() ... so an die gespeicherte Prozedur an, dass diese im gleichen Gültig­keits­bereich ausgeführt wird? Die Antwort ist: Es gelingt nicht. Die gespeicherte Prozedur ist ein abgeschlossener Gültigkeitsbereich, also Scope, dessen Änderungen nach der Ausführung nicht über Funktionen wie SCOPE_IDENTITY() abgefragt werden können.

Sollten Sie Daten an eine Tabelle hinzufügen wollen und müssen den Autowert ermitteln, können Sie nur direkt die INSERT INTO-Anweisung und die SELECT SCOPE_IDENTITY()...-Abfrage gleichzeitig über eine Pass-Through-Abfrage absetzen.

Allgemeine Prozeduren für das Ausführen von Aktionsabfragen

Zum Ausführen von Aktionsabfragen haben wir zwei verschiedene Routinen vorgesehen – eine mit und eine ohne Rückgabewert.

Aktionsabfrage ohne Ergebnis

Die einfachere ist die ohne Rückgabewert. Sie rufen diese Prozedur beispielsweise wie folgt auf (diese Prozedur finden Sie im Modul mdlToolsSQLServer, die Beispiele im Modul mdlRDBMSZugriff_DatenBearbeiten):

SPAktionsabfrageOhneErgebnis  "dbo.spINSERTINTOKategorienMitID",  Standardverbindungszeichenfolge, _
"Neue Kategorie 234",  "Beschreibung"

Dies ruft die gespeicherte Prozedur dbo.spINSERTINTOKategorienMitID auf und übergibt die folgenden Parameter:

  • strStoredProcedure: Name der auszuführenden gespeicherten Prozedur, hier dbo.spINSERT­INTOKategorienMitID
  • strVerbindungszeichenfolge: zu verwendende Verbindungszeichenfolge, hier mit der Funk­tion Standardverbindungszeichenfolge ermittelt
  • varParameter: Parameter-Array, das beliebig viele Parameter entgegennehmen kann – hier nur der Name der neuen Kategorie und der Beschreibung

Die Prozedur erstellt ein neues QueryDef-Objekt ohne Name, da dieses nur temporär innerhalb dieser Prozedur genutzt werden soll. Dann setzt sie mit der Hilfsfunktion Parameterliste die eventuell in varParameter enthaltenen Werte zusammen – siehe weiter unten.

Nun stattet die Prozedur die Pass-Through-Abfrage mit den notwendigen Informationen aus:

  • Connect erhält die Verbindungszeichenfolge,
  • ReturnsRecords wird auf False eingestellt, da keine Daten zurückgeliefert werden sollen,
  • SQL wird mit einem Ausdruck gefüllt, der sich aus der EXEC-Anweisung, dem Namen der gespeicherten Prozedur und der Parameterliste zusammensetzt und
  • die Execute-Methode führt die Abfrage aus.

Die VBA-Prozedur sieht schließlich wie in Listing 5 aus.

Public Sub SPAktionsabfrageOhneErgebnis(strStoredProcedure As String, _
         strVerbindungszeichenfolge As String, _
         ParamArray varParameter() As Variant)
     Dim db As dao.Database
     Dim qdf As dao.QueryDef
     Dim strParameter As String
     Set db = CurrentDb
     Set qdf = db.CreateQueryDef("")
     strParameter = Parameterliste(varParameter)
     With qdf
         .Connect = strVerbindungszeichenfolge
         .ReturnsRecords = False
         .SQL = "EXEC " & strStoredProcedure & " " & strParameter
         .Execute
     End With
     Set db = Nothing
End Sub

Listing 5: Prozedur zum Ausführen beliebiger gespeicherter Prozeduren mit Parametern, aber ohne Rückgabewert

Aktionsabfrage mit Ergebnis

Diese Routine zur Durchführung einer Aktionsabfrage funktioniert prinzipiell genauso wie die zuvor beschriebene Variante. Allerdings erwartet sie einen einzelnen Rückgabewert.

Das Be­son­dere ist, dass der Rückgabewert entweder in der gespeicherten Prozedur in Form einer entsprechenden SELECT-Abfrage implementiert werden kann (bolRueckgabeInSPImplementiert erhält den Wert True) oder dass die Prozedur einfach die Anzahl der von der Aktionsabfrage betroffenen Datensätze ermittelt oder eine mit dem Parameter strRueckgabeausdruck festgelegte SELECT-Anweisung an die EXEC-Methode mit der Angabe der gespeicherten Prozedur anhängt.

Achtung: Die gespeicherte Prozedur muss die Anweisung SET NOCOUNT OFF enthalten, damit @@ROWCOUNT die Anzahl der betroffenen Datensätze erfassen kann.

Der Rest entspricht dem Aufruf der zuvor beschriebenen Prozedur SPAktions­ab­frage­Ohne­Er­geb­nis (s. Listing 6).

Public Function SPAktionsabfrageMitErgebnis(strStoredProcedure As String, _
         strVerbindungszeichenfolge As String, _
         bolRueckgabeInSPImplementiert As Boolean, strRueckgabeausdruck As String, _
         ParamArray varParameter() As Variant) As Variant
     Dim db As dao.Database
     Dim qdf As dao.QueryDef
     Dim rst As dao.Recordset
     Dim strParameter As String
     Set db = CurrentDb
     Set qdf = db.CreateQueryDef("")
     strParameter = Parameterliste(varParameter)
     With qdf
         .Connect = strVerbindungszeichenfolge
         .ReturnsRecords = True
         .SQL = "EXEC " & strStoredProcedure & " " & strParameter
         If Not bolRueckgabeInSPImplementiert Then
             If Len(strRueckgabeausdruck) = 0 Then
                 .SQL = .SQL & vbCrLf & "SELECT @@ROWCOUNT AS RecordsAffected;"
             Else
                 .SQL = .SQL & vbCrLf & strRueckgabeausdruck
             End If
         End If
         Set rst = .OpenRecordset
     End With
     SPAktionsabfrageMitErgebnis = Nz(rst.Fields(0))
     Set db = Nothing
End Function

Listing 6: Prozedur zum Ausführen beliebiger gespeicherter Prozeduren mit Parametern mit Rückgabewert

Ein Beispielaufruf sieht wie folgt aus. Hier hat bolRueckgabeInSPImplementiert den Wert True, also wird keine SELECT-Anweisung zur Ermittlung eines Ergebnisses angehängt:

Debug.Print SPAktionsabfrageMitErgebnis("dbo.spINSERTINTOKategorienMitID", 
Standardverbindungszeichenfolge, True, "", "Neue Kategorie 345", "Beschreibung")

Das Ergebnis entspricht dann dem von der Funktion SCOPE_IDENTITY in der gespeicherten Pro­ze­dur ermittelten Autowert des neuen Datensatzes. Angenommen, es handelt sich um eine reine Aktionsabfrage, dann können Sie mit folgendem Aufruf eine SELECT-Anweisung mit dem Parameter strRueckgabeausdruck übergeben, die dann an die EXEC-Anweisung mit der gespeicherten Prozedur angehängt wird. Hier soll die Kategorie mit dem Primärschlüsselwert 1 gelöscht werden:

Debug.Print SPAktionsabfrageMitErgebnis( "dbo.spDELETEKategorieNachID",  
Standardverbindungszeichenfolge, False,  "SELECT @@ROWCOUNT AS Geloescht", 1)

Zusammenfassung und Ausblick

Dieser Beitrag erläutert die Grundlagen für das Durchführen von Datensatzbearbeitungen an den Daten einer SQL Server-Datenbank von VBA-Prozeduren aus.

Zu Beginn haben wir grundlegende Herausforderungen und Lösungen beschrieben und die verwendeten VBA-Routinen immer weiter verfeinert, bis wir am Ende die beiden Prozeduren SPAktionsabfrageOhneErgebnis und SPAktionsabfrageMitErgebnis herausgearbeitet haben.

Beide haben Parameter, mit denen Sie die auszuführenden gespeicherten Prozeduren, die Verbindungszeichenfolge, Rückgabewerte und Parameter auswerten können.

Kompletten Artikel lesen?

Einfach für den Newsletter anmelden, dann lesen Sie schon in einer Minute den kompletten Artikel und erhalten die Beispieldatenbanken.

E-Mail:

Download

Download

Die .zip-Datei enthält folgende Dateien:

Suedsturm.sql

RDBMSPerVBA_DatenBearbeiten.accdb

Beispieldateien downloaden

© 2003-2015 André Minhorst Alle Rechte vorbehalten.