Parameterabfragen unter der Lupe

Abfragen lassen sich auf unterschiedlichste Weise mit den Vergleichswerten für ihre Kriterien bestücken – beispielsweise, indem man einfach den gewünschten SQL-Ausdruck per VBA zusammensetzt. Es gelingt jedoch unter Einsatz von Abfrageparametern: Sie fügen in eckige Klammern eingefasste Bezeichnungen in den Abfrageentwurf ein und füllen diese dann entweder per eingebauter Inputbox oder per VBA. Dieser Beitrag stellt alle wichtigen Techniken rund um den Einsatz von Parameterabfragen vor.

Warum Parameterabfragen

In vielen Fällen benötigen Sie keine Parameterabfragen, weil die Abfrage entweder bereits die notwendigen Kriterien enthält oder diese nachträglich per Filter appliziert werden. Dies kann beispielsweise der Fall sein, wenn Sie einem Formular eine komplette Tabelle als Datenherkunft zuweisen, dann aber etwa mit der OpenForm-Methode des DoCmd-Objekts einen entsprechenden Filterausdruck übergeben. Dieser wird dann beim öffnen des Formulars angewendet.

In manchen Fällen kommen Sie damit aber nicht weiter: Wenn Sie etwa eine Aktualisierungsabfrage erstellen, die ein oder mehrere Kriterien enthält, können Sie die Vergleichswerte nur auf zwei Arten anwenden: Entweder, indem Sie den SQL-Ausdruck der Abfrage in eine VBA-Variable laden und dieser die entsprechenden Kriterien hinzufügen oder eben mit Abfrageparametern.

Bestimmte Anwendungsfälle erfordern es auch, dass man mehrere aufeinander aufbauende Abfragen nutzt. Das kann etwa sein, wenn Sie zunächst die Daten einer Abfrage mit OUTER JOIN-Verknüpfung ermitteln und das Ergebnis dann in einer weiteren Abfrage mit den Daten aus weiteren Tabellen verknüpfen. In diesem Fall kommen Sie kaum um den Einsatz von Abfrageparametern herum. Sie könnten zwar noch den Abfragetext der Ausgangsabfrage per VBA anpassen und erst dann die andere Abfrage darauf zugreifen lassen, aber das wäre doch etwas umständlich.

Darüber hinaus haben Parameterabfragen den Vorteil, dass Access für diese nach der ersten Ausführung einen Ausführungsplan erstellt. Das bedeutet, dass ein Plan aufgestellt, mit dem das Ergebnis optimal ermittelt wird. Wenn Sie die gleiche Abfrage anschließend nochmals aufrufen, kann diese auf den bereits ermittelten Ausführungsplan zurückgreifen. Wenn Sie eine Abfrage bei jedem Aufruf erneut per VBA zusammenstellen, muss auch der Ausführungsplan mit jedem Aufruf erneut erstellt werden.

Parameterabfrage erstellen

Eine Parameterabfrage unterscheidet sich nicht wesentlich von einer herkömmlichen Abfrage. Der Unterschied ist, dass beispielsweise ein Kriteriumsausdruck nicht direkt beim Erstellen mit dem Vergleichswert für diese Abfrage ausgestattet wird, sondern stattdessen einen in eckigen Klammern eingefassten Parameter aufnimmt.

Dies sieht beispielsweise so wie in Bild 1 aus. Wenn Sie die Abfrage nun ausführen, zeigt diese alle Datensätze der Tabelle tblArtikel an, deren Feld KategorieID den Wert 1 enthält. Dies wollen wir nun flexibler gestalten: Der Benutzer soll diesen Wert beim Aufruf der Abfrage selbst eingeben können. Dazu ersetzen Sie den Wert 1 einfach durch den Ausdruck [VergleichskategorieID].

Bild 1: Abfrage mit festem Vergleichswert

Wenn Sie die Abfrage dann ausführen, zeigt Access einen Dialog zur Eingabe des Vergleichswertes an (s. Bild 2). Dieser erhält als Titel den Text Parameterwert eingeben und als Meldungstext den in eckigen Klammern angegebenen Ausdruck. Hier ist zu beachten, dass der Text in eckigen Klammern, also der Name des Parameters, nicht den Namen eines der Felder der Tabellen der Datenherkunft enthalten darf. In diesem Fall wird der Parameter mit dem jeweiligen Wert der Datenherkunft gefüllt.

Bild 2: Abfrage mit Parameter als Vergleichswert

Bei der Benennung der Parameter gibt es sonst kaum Einschränkungen – Sie dürfen allerdings keinen Punkt und kein Ausrufezeichen verwenden. Dies führt direkt zu einer entsprechenden Meldung (s. Bild 3).

Bild 3: Abfrageparameter mit ungültigem Zeichen

Wenn Sie den Parameter tatsächlich über den dafür vorgesehenen Dialog vom Benutzer abfragen möchten, können Sie allerdings auch komplette Sätze unterbringen – nur ohne Punkt. Aber das ist kein Problem – Sie können einen solchen Satz ja auch mit einem Doppelpunkt abschließen:

[Geben Sie die KategorieID ein:]

Zeichenketten und Parameter

Wenn Sie eine Zeichenkette mit einem Parameter vergleichen wollen, gehen Sie genauso vor wie mit einem Zahlenwert wie im obigen Beispiel. Allerdings möchten Sie den Parameter ja vielleicht als Teil des Vergleichsausdrucks einsetzen.

Auch das ist kein Problem: Sie müssen den Abfrageparameter lediglich per Und-Zeichen mit den übrigen Elementen verbinden. Wenn der Benutzer etwa Kundendatensätze anhand der Postleitzahl eingrenzen soll, können Sie ihm folgendes Kriterium anbieten:

Wie [Geben Sie die PLZ ein:] & "*"

Dieses fragt wie in Bild 4 eine Zeichenkette ab und hängt an diese noch das Sternchen (*) als Platzhalter an. Die Abfrage liefert dann alle Datensätze der Tabelle tblKunden, deren Feld PLZ mit dem als Parameter übergebenen Wert übereinstimmt.

Bild 4: Abfrageparameter als Teil des Vergleichswertes

Datentypen für Parameter festlegen

Aktuell kann der Benutzer für die Parameter alle denkbaren Werte eingeben. So landet dann auch schon mal eine Zeichenkette in einem Parameter, der eigentlich ein Datumsfeld betrifft. Dies führt natürlich zu einer entsprechenden Fehlermeldung, die – wie in vielen Fällen – für Otto Normalverbraucher wenig Aussagekraft besitzt. Wenn Sie sicherstellen wollen, dass der Benutzer einen Wert als Parameter übergibt, der einem bestimmten Datentyp entspricht, müssen Sie den Parameter in einem separaten Dialog deklarieren. Diesen Dialog rufen Sie auf, indem Sie das Kontextmenü des Abfrageentwurfs anzeigen und dort den Eintrag Parameter auswählen (s. Bild 5).

Bild 5: Anzeigen des Dialogs zum Deklarieren von Parametern

In diesem Fall wollen wir dafür sorgen, dass der Benutzer für die beiden Parameter Startdatum und Enddatum nur Datumswerte eintragen darf. Der Dialog, den Sie mit dem Kontextmenüeintrag Parameter anzeigen, enthält leider noch nicht automatisch die bereits im Kriterium angegebenen Abfrageparameter. Sie müssen diese also manuell dort eintragen und dann den Datentyp festlegen. Dabei stellen wir fest, dass die Datentypen nicht exakt mit denen im Tabellenentwurf übereinstimmen. Die Zahlendatentypen etwa werden hier direkt mit der entsprechenden Feldgröße angegeben, also Byte, Long et cetera (s. Bild 6). Wir tragen hier die beiden Parameter Startdatum und Enddatum ein und legen als Felddatentyp jeweils Datum/Uhrzeit fest.

Bild 6: Der Dialog zum Anzeigen von Parametern

Wenn der Benutzer dann einen falschen Wert einträgt, erscheint die Meldung Sie haben einen Wert eingegeben, der für dieses Feld nicht gültig ist – also die gleiche Meldung, die auch bei der Eingabe falscher Daten in Tabellenfelder erscheint.

Davon abgesehen arbeiten Abfrageparameter, die auch als solche deklariert wurden, genau wie nicht deklarierte Abfrageparameter.

Nun wird es Zeit, einen Blick auf den SQL-Code für eine Abfrage mit deklarierten Parametern zu werfen. Die Parameter werden hier in einer eigenen Zeile vor Beginn der eigentlichen SQL-Anweisung mit dem Schlüsselwort PARAMETERS unter Angabe des entsprechenden Datentyps deklariert:

PARAMETERS Startdatum DateTime, Enddatum DateTime;
SELECT BestellungID, KundeID, PersonalID, Bestelldatum
FROM tblBestellungen
WHERE Bestelldatum Between [Startdatum] And [Enddatum];

Sollten die Namen der im Kriterium untergebrachten Parameter nicht mit denen im Parameter-Dialog übereinstimmen, fragt Access alle verschiedenen Versionen ab.

Wenn Sie also einen Parameter namens Datum eingeben: im Abfrageentwurf unterbringen und im Parameter-Dialog einen Parameter namens Datumeingabe: definieren, erscheinen Abfragedialoge für beide Varianten. Allerdings wirkt sich nur derjenige, der auch im Kriterium eingesetzt wird, auf das Abfrageergebnis aus.

Parameter-abfragen per VBA

Für einen Schnellschuss kann man den Benutzer mal mit den von Access erzeugten Dialogen zum Abfragen von Parametern konfrontieren, aber in der Regel sollte man die Werte per Formular abfragen. Dort kann man diese gegebenenfalls vorbelegen, eine eigene Fehlerbehandlung für den Fall der Eingabe ungültiger Werte unterbringen et cetera. Also schauen wir uns an, wie man unter VBA mit Parameterabfragen umgeht.

Wenn Sie die Abfrage etwa als Recordset öffnen und durchlaufen möchten und annehmen, dass Access wie beim manuellen öffnen den Dialog zum Eingeben der Parameter anzeigt, probieren Sie einmal die Prozedur aus Listing 1 aus.

Public Sub ParameterabfrageMitFehler()
     Dim db As DAO.Database
     Dim rst As DAO.Recordset
     Set db = CurrentDb
     Set rst = db.OpenRecordset("qryBestellungenNachDatum")
     Do While Not rst.EOF
         rst.MoveNext
     Loop
End Sub

Listing 1: Dieser Zugriff auf die Parameterabfrage löst einen Fehler aus.

Das Ergebnis ist die Fehlermeldung aus Bild 7. Access zeigt also keine Eingabedialoge an, wenn Sie eine Parameterabfrage als Recordset öffnen. Wie also bringen Sie die Parameterwerte in der Abfrage unter

Bild 7: Fehlermeldung beim Versuch, eine Abfrage mit Parametern per OpenRecordset zu öffnen

VBA und QueryDef

Dazu benötigen Sie zwei weitere DAO-Objekte, nämlich das QueryDef-Objekt, mit dem Sie auf Abfragen zugreifen und ihre Eigenschaften verändern können, sowie das Parameter-Objekt, das den Zugriff auf die Parameter einer Abfrage erlaubt. Für das Beispiel der Abfrage qryBestellungenNachDatum benötigen wir eigentlich sogar zwei Parameter-Objekte, die wir als prmStart und prmEnde deklarieren. Theoretisch würde zwar eine Variable reichen (prm), aber wir wollen für eine bessere übersicht für jeden Parameter eine eigene Variable einsetzen. Der Einsatz von Parametern in VBA sieht wie in Listing 2 aus.

Public Sub Parameterabfrage()
     Dim db As DAO.Database
     Dim qdf As DAO.QueryDef
     Dim prmStart As DAO.Parameter
     Dim prmEnde As DAO.Parameter
     Dim rst As DAO.Recordset
     Set db = CurrentDb
     Set qdf = db.QueryDefs("qryBestellungenNachDatum")
     Set prmStart = qdf.Parameters("Startdatum")
     prmStart.Value = "1.1.2012"
     Set prmEnde = qdf.Parameters("Enddatum")
     prmEnde.Value = "31.1.2012"
     Set rst = qdf.OpenRecordset
     Do While Not rst.EOF
         Debug.Print rst!BestellungID
         rst.MoveNext
     Loop
End Sub

Listing 2: Parameterabfrage mit per VBA zugewiesenen Parametern

Neben den beiden Parameter-Variablen deklarieren wir dort eine QueryDef-Variable namens qdf sowie ein Database-Objekt und ein Recordset-Objekt. Das Database-Objekt db füllen wir mit einem Verweis auf die aktuelle Datenbank. Dann referenzieren wir die Abfrage qryBestellungenNachDatum mit dem QueryDef-Objekt qdf, das wir über den Namen der Abfrage aus der QueryDefs-Auflistung des Database-Objekts beziehen.

Und natürlich müssen wir noch die beiden Parameter-Variablen füllen. Dies erledigen wir über die Parameters-Auflistung, deren Elemente wir beispielsweise direkt über den Namen ansprechen können – also Startdatum und Enddatum.

Das Parameter-Objekt bietet eine Eigenschaft namens Value, mit der wir den Parameterwert übergeben können. Schließlich erstellen wir ein Recordset-Objekt auf Basis des mit den entsprechenden Parametern versehenen QueryDef-Objekts qdf und durchlaufen zum Test dessen Datensätze.

Parameter aufnehmen

Im nächsten Beispiel bieten wir dem Benutzer eine Möglichkeit, um die beiden Parameter einzugeben und gleich die gesuchten Datensätze anzuzeigen. Dazu erstellen Sie ein Hauptformular namens frmBestellungen, das die beiden Textfelder txtStartdatum und txtEnddatum enthält, sowie eine Schaltfläche namens cmdAnzeigen (s. Bild 8). Das Unterformular enthält zunächst die Daten der Tabelle tblBestellungen. Beim Laden belegt das Formular die beiden Textfelder vor:

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