Home > Artikel > Ausgabe 5/2011 > Aktionsabfragen per VBA ausführen

Aktionsabfragen per VBA ausführen

  PDF ansehen

  Download PDF und Beispieldatenbank

Aktionsabfragen lassen sich bequem mit der Entwurfsansicht für Abfragen zusammenstellen. Sie können damit Daten an Tabellen anfügen, bestehende Daten ändern oder löschen und sogar gleich die passende Tabelle zum Einfügen von Daten erstellen. Wie aber soll der Benutzer eine solche Abfrage aufrufen, ohne doppelt auf den entsprechenden Eintrag im Datenbankfenster oder im Navigationsbereich zu klicken? Dieser Artikel zeigt, wie Sie dies realisieren.

Beispieldatenbank

Die Beispiele zu diesem Artikel finden Sie in der Datenbank 1105_AktionsabfragenPerVBA.mdb.

Aktionsabfragen

Wenn Sie eine Aktionsabfrage erstellen, gehen Sie wie in Aktionsabfragen – Teil II: Löschabfragen oder Aktionsabfragen – Teil I: Anfügeabfragen beschrieben vor. Weitere Artikel zu Aktualisierungsabfragen oder Tabellenerstellungsabfragen folgen in späteren Ausgaben.

Das Resultat beim Erstellen einer Aktualisierungsabfrage mit der Abfrageentwurfsansicht ist eine gespeicherte Abfrage. Diese führen Sie per Doppelklick auf den entsprechenden Eintrag im Datenbankfenster oder im Navigationsbereich aus.

Ein Beispiel hierfür ist die Löschabfrage aus Bild 1. Sie ist unter dem Namen qryArtikelLoeschen gespeichert und bildet die Grundlage für das folgende Beispiel.

Entwurfsansicht einer Abfrage zum Löschen des Artikels mit dem Wert 1 im Feld ArtikelID.

Bild 1: Entwurfsansicht einer Abfrage zum Löschen des Artikels mit dem Wert 1 im Feld ArtikelID.

Die Abfrage löscht genau einen Datensatz, und zwar den mit dem Wert 1 im Feld ArtikelID. Das gilt natürlich nur, wenn ein solcher Datensatz überhaupt vorhanden ist. Sonst bewirkt der Aufruf dieser Abfrage gar nichts.

Die Execute-Methode

Nun folgt der interessante Teil: Access bietet eine ganze Reihe von VBA-Objekten an, die wiederum praktische Methoden etwa für den Zugriff auf Daten offerieren. In unserem Fall geht es um das Objekt, dass die aktuelle Datenbank repräsentiert.

Es hat den Datentyp Database und wird als Objektvariable deklariert. Das alles ist für Einsteiger vielleicht noch etwas unverständlich – das ist aber kein Problem. Manche Dinge kann man zu Beginn einfach als gegeben hinnehmen. In diesem Fall geht es um die folgenden Codezeilen:

Dim db As Database

Set db = CurrentDb

Die erste Zeile deklariert db als Objektvariable des Typs Database, die zweite füllt diese Variable mit dem Wert der Funktion CurrentDb. CurrentDb wiederum liefert einen Verweis auf die aktuelle Datenbank.

Dank dieser beiden Zeilen können Sie nachfolgend über die Variable db bestimmte Aktionen mit der Datenbank durchführen.

In unserem Fall soll eine Aktionsabfrage durchgeführt werden, genau genommen eine Löschabfrage.

Der dazu benötigte Befehl des mit db referenzierten Objekts heißt Execute. Execute erwartet als Parameter zumindest eine Variable oder Zeichenkette, die folgendes enthält:

  • den Namen der Aktionsabfrage, wie Sie ihn auch im Datenbankfenster oder im Navigationsbereich finden (hier also beispielsweise qryArtikelLoeschen)
  • den SQL-Code der Aktionsabfrage (wie Sie diesen ermitteln, erfahren Sie gleich).

Am einfachsten ist es natürlich, wenn Sie eine DELETE-Abfrage per Abfrageentwurf zusammenstellen und diese direkt per VBA aufrufen möchten. Die nötigen Anweisungen sehen dann so aus:

Private Sub EinfacherAufruf()

     Dim db As DAO.Database

     Set db = CurrentDb

     db.Execute "qryArtikelLoeschen"

     Set db = Nothing

End Sub

Wenn Sie das Beispiel nachvollziehen möchten, legen Sie ein neues Standardmodul an und fügen Sie den obigen Code dort ein (?[basics] VBA-Modul anlegen).

Wenn Sie als Parameter der Execute-Methode einfach nur den Namen einer Aktionsabfrage angeben, wird diese einfach aufgerufen. Allerdings müssen bereits alle Einstellungen in der Abfrage selbst vorgenommen werden – zum Beispiel, ob alle Datensätze der Tabelle gelöscht werden sollen oder nur einer. Im obigen Beispiel wird die Abfrage qryArtikelLoeschen ausgelöst, was den Artikel mit dem Wert 1 im Feld ArtikelID löscht.

Die Abfrage führen Sie beispielsweise aus, indem Sie die Einfügemarke irgendwo innerhalb der Prozedur platzieren und dann die Taste F5 betätigen oder den Menübefehl Ausführen|Sub/Userform ausführen aufrufen.

Für den Anfang prüfen Sie nun per Sichtkontrolle, ob der Datensatz tatsächlich gelöscht wurde – und zwar, indem Sie die Tabelle tblArtikel öffnen und schauen, ob der Datensatz noch vorhanden ist.

Achtung: Keine Rückfrage!

Die Execute-Anweisung führt die angegebene Aktionsabfrage ohne Rückfrage aus. Wenn Sie also etwa einen Artikel erst löschen möchten, wenn der Benutzer dem explizit zugestimmt hat, müssen Sie die Execute-Anweisung noch in eine If...Then-Bedingung einfassen, die das Ergebnis einer MsgBox-Funktion prüft. Dies kann etwas allgemein formuliert etwa so aussehen:

If MsgBox("Datensatz löschen?", vbYesNo) = vbYes Then

     Set db = CurrentDb

     db.Execute "qryArtikelLoeschen"

End If

Aktionsabfrage als SQL-Ausdruck angeben

Manchmal kann es hilfreich sein, die Aktionsabfrage nicht als Abfrage zu speichern, sondern direkt auszuführen. Das bedeutet, dass Sie der Execute-Anweisung nicht den Namen der gespeicherten Abfrage, sondern einen entsprechenden SQL-Ausdruck angeben.

Fehlende SQL-Kenntnisse sind hier kein Problem: Access bietet die Möglichkeit, Abfragen in der SQL-Ansicht anzuzeigen. Und aus dieser können Sie den gewünschten SQL-Ausdruck einfach herauskopieren und in den VBA-Code einfügen.

Im Falle der Aktionsabfrage unseres Beispiels sieht das so aus:

  • Öffnen Sie die Abfrage in der Entwurfsansicht.
  • Klicken Sie mit der rechten Maustaste auf die Titelzeile und wählen Sie den Eintrag SQL-Ansicht aus (siehe Bild 2).
  • SQL-Ansicht einer Abfrage aktivieren

    Bild 2: SQL-Ansicht einer Abfrage aktivieren

Fertig! Der SQL-Ausdruck zur aktuell geöffneten Aktionsabfrage wird nun wie in Bild 3 angezeigt.

SQL-Ansicht einer Abfrage

Bild 3: SQL-Ansicht einer Abfrage

Um den Code etwas übersichtlicher zu gestalten, fügen Sie nun zunächst eine Variable ein, die den SQL-Ausdruck aufnimmt:

Dim strSQL As String

Diesen weisen Sie wie in der folgenden Anweisung zu:

strSQL = "DELETE tblArtikel.ArtikelID FROM tblArtikel WHERE (((tblArtikel.ArtikelID)=1));"

Der besseren Übersicht halber lässt sich der SQL-Ausdruck auch noch ein wenig vereinfachen:

strSQL = "DELETE FROM tblArtikel WHERE ArtikelID=1;"

Schließlich verwendet die Execute-Methode den in strSQL gespeicherten Ausdruck als Parameter:

db.Execute strSQL

Flexible Aktionsabfragen

Mit einem per VBA erstelltem SQL-Ausdruck können Sie die Abfrage gleich ein wenig flexibler machen: Der SQL-Ausdruck lässt sich nämlich leicht ändern. Im folgenden soll nun nicht mehr unbedingt der Datenatz mit dem Wert 1 im Feld ArtikelID gelöscht werden, sondern ein Datensatz, den der Benutzer zuvor über eine InputBox eingibt.

Im folgenden Beispiel wird zusätzlich eine Variable namens lngArtikelID deklariert. Dieser weist eine InputBox-Funktion den vom Benutzer eingegebenen Wert zu. Schließlich wird der benötigte SQL-Ausdruck aus der Zeichenkette DELETE FROM tblArtikel WHERE ArtikelID = und dem Inhalt der Variablen lngArtikelID zusammengesetzt:

Public Sub ArtikelLoeschenSQLMitInputBox()

     Dim db As DAO.Database

     Dim strSQL As String

     Dim lngArtikelID As Long

     lngArtikelID = InputBox("Geben Sie die ID des µ

                       zu löschenden Artikels ein.")

     Set db = CurrentDb

     strSQL = "DELETE FROM tblArtikel WHERE µ

                          ArtikelID=" & lngArtikelID

     db.Execute strSQL

     Set db = Nothing

End Sub

Einsatz von Parameterabfragen

Es gibt noch eine weitere Möglichkeit, Abfragen flexibel zusammenzustellen – in sogenannten Parameterabfragen. Auf diese Technik gehen wir in einer späteren Ausgabe ein.

War die Aktionsabfrage erfolgreich?

Dem Benutzer werden Sie diese Vorgehensweise natürlich nicht zumuten wollen. Die Prüfung, ob die Aktionsabfrage funktioniert hat, nehmen Sie per VBA vor. Im Falle der Löschabfrage qryArtikelLoeschen soll beispielsweise ein Artikel gelöscht werden, also betrifft die Abfrage genau einen Datensatz.

Beim Löschen könnten Sie beispielsweise zweimal die Domänenfunktion DCount aufrufen (siehe Access-Funktionen – Teil I: Domänenfunktionen): einmal vor und einmal nach dem Löschen. Wenn das Ergebnis des zweiten Aufrufs um eins kleiner als das des ersten Aufrufs ist, war das Löschen offensichtlich erfolgreich. Dies könnte (hier auszugsweise) so aussehen:

Dim lngArtikelVorher As Long

Dim lngArtikelNachher As Long

...

lngArtikelVorher = DCount("*", "tblArtikel")

db.Execute "qryArtikelLoeschen"

lngArtikelNachher = DCount("*", "tblArtikel")

If lngArtikelVorher - 1 = lngArtikelNachher Then

     MsgBox "Artikel gelöscht!"

Else

     MsgBox "Artikel nicht gelöscht!"

End If

...

Die beiden Variablen lngArtikelVorher und lng­Ar­tikel­Nachher speichern die Anzahl der Artikel vor und nach dem Aufrufen der Löschabfrage. Wenn lng­­Ar­tikelVorher - 1 dem Wert von lngArtikelNachher entspricht, wurde offensichtlich genau ein Datensatz gelöscht. In diesem Fall wird die Meldung Artikel gelöscht! ausgegeben.

Die RecordsAffected-Eigenschaft

Diese Vorgehensweise hat jedoch einen entscheidenden Nachteil: Es kann sein, dass genau zum Zeitpunkt des Löschens ein anderer Benutzer ebenfalls einen Artikel löscht oder einen hinzufügt. Das Ergebnis würde dann nicht mehr stimmen.

Glücklicherweise bietet das Database-Objekt eine Eigenschaft, mit der Sie ermitteln können, wieviele Datensätze durch die zuletzt ausgeführte Execute-Anweisung betroffen waren. Diese Eigenschaft heißt RecordsAffected. Sie können den Wert dieser Eigenschaft beispielsweise gleich nach dem Ausführen der Execute-Anweisung in einem Meldungsfenster ausgeben:

Public Sub ArtikelLoeschenMitPruefungII()

     Dim db As DAO.Database

     Set db = CurrentDb

     db.Execute "qryArtikelLoeschen"

     MsgBox db.RecordsAffected

     Set db = Nothing

End Sub

Natürlich lässt sich auch noch ein informativer Text anzeigen. Die MsgBox-Anweisung sieht dann so aus:

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

Fehler in Aktionsabfragen bemerken

Die Execute-Anweisung führt die angegebene Aktionsabfrage nicht nur sofort aus, ohne den Benutzer zu fragen, ob dies auch tatsächlich geschehen soll. Sie gibt standardmäßig auch kein Feedback, wenn beim Ausführen der Aktionsabfrage etwas schiefgeht.

Ein Beispiel hierfür ist das Löschen eines Eintrags der Tabelle tblKategorien, wenn die entsprechende Kategorie bereits für einen oder mehrere Artikel aus tblArtikel ausgewählt wurde (siehe Bild 4). Wenn Sie dennoch versuchen, einen solchen Datensatz zu löschen, erscheint eine Meldung wie in Bild 5.

Wenn Artikel und Kategorien wie hier mit referentieller Integrität verknüpft sind, kann keine Kategorie gelöscht werden, wenn diese bereits einem Artikel zugeordnet wurde.

Bild 4: Wenn Artikel und Kategorien wie hier mit referentieller Integrität verknüpft sind, kann keine Kategorie gelöscht werden, wenn diese bereits einem Artikel zugeordnet wurde.

Fehlermeldung beim Löschen eines Datensatzes

Bild 5: Fehlermeldung beim Löschen eines Datensatzes

Die folgende Prozedur soll die Kategorie mit dem Wert 1 im Feld KategorieID löschen:

Public Sub KategorieLoeschenMitFehler()

     Dim db As DAO.Database

     Dim strSQL As String

     Set db = CurrentDb

     strSQL = "DELETE FROM tblKategorien WHERE KategorieID = 1"

     db.Execute strSQL

     Set db = Nothing

End Sub

Die Kategorie kann nicht gelöscht werden. Da die Execute-Anweisung aber standardmäßig keine Fehler meldet, läuft die Prozedur ohne Meldung und ohne Ergebnis durch.

Dies ändern Sie, indem Sie einen zweiten Parameter zur Execute-Anweisung hinzufügen:

db.Execute strSQL, dbFailOnError

Wenn Sie die Prozedur nun aufrufen, erscheint eine entsprechende Fehlermeldung (siehe Bild 6). An dieser Stelle wagen wir einen kleinen Ausblick auf ein Thema, das in einer späteren Ausgabe detailliert erläutert wird: das Behandeln von Fehlern im VBA-Code.

Mit dem Parameter dbFailOnError führt auch die Execute-Anweisung zu einer Fehlermeldung.

Bild 6: Mit dem Parameter dbFailOnError führt auch die Execute-Anweisung zu einer Fehlermeldung.

Ziel dieser Maßnahme ist es, die aufgetauchte Fehlermeldung zu unterbinden und eine benutzerdefinierte Fehlermeldung anzuzeigen – und zwar eine, die auch der Benutzer versteht.

Dazu bauen Sie um die Execute-Anweisung herum einige weitere Anweisungen ein:

On Error Resume Next

db.Execute strSQL, dbFailOnError

If Err.Number = 3200 Then

     MsgBox "Die Kategorie konnte nicht gelöscht werden, weil sie bereits einem oder mehreren Artikeln zugewiesen wurde."

End If

On Error GoTo 0

Die erste Anweisung On Error Resume Next sorgt dafür, dass in den nachfolgenden Zeilen auftauchende Fehler nicht durch eine entsprechende Fehlermeldung gemeldet werden.

Dies wird in diesem Fall erst durch die Anweisung On Error Goto 0 wieder aufgehoben.

Zwischen diesen beiden On Error ...-Anweisungen folgt zunächst die Execute-Anweisung, die ja in bestimmten Fällen einen Fehler auslöst – zum Beispiel, wenn der zu löschende Datensatz wie hier bereits in einer verknüpften Tabelle als Fremdschlüsselwert ausgewählt wurde.

Wie Sie Bild 7 entnehmen können, heißt die Fehlernummer dieses Fehlers 3200. Um eine speziell auf diesen Fehler zugeschnittene Meldung anzeigen zu können, fragen wir mit einer If...Then-Bedingung ab, ob der Fehler die Nummer 3200 hat.

Benutzerdefinierte Fehlermeldung

Bild 7: Benutzerdefinierte Fehlermeldung

Diese Nummer liefert, auch dazu in einen späteren Artikel mehr, der Ausdruck Err.Number. Hat dieser den Wert 3200, soll in einem Meldungsfenster der Text Die Kategorie konnte nicht gelöscht werden, weil sie bereits einem oder mehreren Artikeln zugewiesen wurde angezeigt werden.

Diese Fehlerbehandlung ist allerdings zu rudimentär: Access würde nun nämlich nur auf den Fehler 3200 reagieren, alle anderen Fehler blieben unbehandelt. Daher ändern wir die Fehlerbehandlung der Ordnung halber noch wie folgt:

Select Case Err.Number

     Case 3200

         MsgBox "Die Kategorie konnte nicht gelöscht werden, weil sie bereits einem oder mehreren Artikeln zugewiesen wurde."

     Case 0

     Case Else

         MsgBox "Fehler " & Err.Number & " " & Err.Description

End Select

Dieses Konstrukt gibt bei Fehler 3200 die gewünschte Meldung aus. Hat Err.Number den Wert 0, ist kein Fehler aufgetreten – es geschieht dann nichts. Und in allen anderen Fällen wird eine Meldung mit der Fehlernummer und der Fehlerbeschreibung ausgegeben.

Im Artikel Daten per Kombinationsfeld auswählen und löschen finden Sie Beispiele für den Praxiseinsatz von Aktionsabfragen.