Code für den Datenzugriff brauchen Sie immer wieder. Mal möchten Sie eine SQL-Aktionsabfrage auf Basis einer Tabelle erzeugen, in die Sie eine Reihe zuvor gefüllter Variablen integrieren, mal greifen Sie vielleicht per DAO oder ADO auf die Felder einer solchen Tabelle zu. Wir stellen Ihnen die notwendigen Techniken vor, damit Sie dies nicht mehr von Hand erledigen müssen.
DAO- und SQL-Ausdrücke erzeugen
Wer Code für den Datenzugriff erstellen muss, der mehr als ein paar Felder umfasst, sieht sich einer Fleißaufgabe gegenüber, die oft Flüchtigkeitsfehler hervorbringt. Variablendeklaration, Werte zuweisen oder per Parameter entgegennehmen, Zuweisung der Variablen oder Werte zu den Feldern erfordern einiges an Schreibarbeit. Komplizierter wird es noch, wenn Sie SQL-Anweisungen wie UPDATE oder INSERT INTO erzeugen und mit den Feldern kompletter Tabellen füllen müssen.
Dem Autor dieses Beitrags kribbelt es schon lange in den Fingern, ein Tool zu programmieren, mit dem sich solche Aufgaben per Mausklick erledigen lassen. Dieser Beitrag beschreibt, was dabei herausgekommen ist.
Hauptelement der Lösung ist das Formular aus Bild 1. Es erlaubt zunächst das Auswählen der Zieltabelle. Dann legen Sie fest, welche Felder dieser Tabelle vom Einfügen beziehungsweise Ôndern der Daten betroffen sind und welche Felder als Kriterien beim Auswählen eines zu ändernden Datensatzes verwendet werden.
Bild 1: Das Formular frmDatenzugriffscode
Schließlich legen Sie diverse weitere Optionen fest, die wir in den folgenden Abschnitten zusammen mit der Programmierung dieses Formulars vorstellen.
Das Resultat ist eine fertige Prozedur, die genau auf die ausgewählten Parameter zugeschnitten ist. Sie können diese Prozedur kopieren und in die Module Ihrer eigenen Datenbank einfügen.
Um die Lösung im aktuellen Zustand einzusetzen, kopieren Sie einfach das Formular frmDatenzugriffscode in die Zielanwendung und öffnen es – schon können Sie Datenzugriffscode erzeugen.
Gegebenenfalls benötigen Sie noch die Funktion IsoDatum aus dem Modul mdlTools – fügen Sie diese einfach zu einem bestehenden Modul mit Hilfsroutinen hinzu oder importieren Sie das Modul komplett in Ihre Anwendung.
Formular zum Festlegen der Parameter
Die Funktionen zum Erstellen der gewünschten Code-Schnipsel sollen über ein Formular gesteuert werden. Dieses Formular heißt frmDatenzugriffscode und verfügt für die Auswahl der betroffenen Tabelle und der zu berücksichtigenden Felder ein Kombinationsfeld namens cboTabelle, ein Listenfeld namens lstFelder und einige weitere Steuerelemente (s. Bild 1).
Für das Listenfeld stellen Sie die Eigenschaft Herkunftstyp auf Feldliste ein. Es zeigt dann alle Felder der Tabelle an, die Sie für die Eigenschaft Datensatzherkunft angeben.
Diese wählen Sie mit dem Kombinationsfeld cboTabelle aus. Das Kombinationsfeld bezieht seine Werte aus der Systemtabelle MSysObjects:
SELECT Name FROM MSysObjects WHERE Type = 1 AND Name NOT LIKE ''MSYS*'';
Stellen Sie für das Listenfeld außerdem die Eigenschaft Mehrfachauswahl auf Einfach ein. Der Benutzer kann so eines oder mehrere Felder durch simples Anklicken der gewünschten Felder auswählen.
Da sicher gelegentlich alle Felder in den Datenzugriffscode eingebunden werden sollen, fügen Sie außerdem eine Schaltfläche namens cmdAlleMarkieren hinzu.
Damit diese alle Einträge des Listenfeldes lstFelder auswählt, hinterlegen Sie für die Ereigniseigenschaft Beim Klicken die folgende Prozedur:
Private Sub cmdAlleAuswaehlen_Click() Dim i As Integer For i = 0 To Me!lstFelder.ListCount - 1 Me!lstFelder.Selected(i) = True Next i End Sub
Neben diesem Listenfeld befindet sich ein weiteres Listenfeld namens lstKriterien, mit dem Sie festlegen können, welche Kriterien beim Ôndern bestehender Datensätze berücksichtigt werden sollen.
Beide Listenfelder werden beim Auswählen einer Tabelle aktualisiert:
Private Sub cboTabelle_AfterUpdate() If Not IsNull(Me!cboTabelle) Then Me!lstFelder.RowSource = Me!cboTabelle Me!lstKriterien.RowSource = Me!cboTabelle Else Me!lstFelder.RowSource = "" Me!lstKriterien.RowSource = "" End If End Sub
Die Optionsgruppe ogrKriterien erlaubt das Festlegen eines Verknüpfungsoperators für mehrere Kriterien. Sie können diese entweder mit AND oder mit OR verknüpfen, aber nicht gemischt – dies müssen Sie gegebenenfalls manuell durchführen.
Die Optionsgruppe ogrVariablen fragt ab, ob die zu verwendenden Variablen als Parameter der zu erstellenden Routine übergeben werden sollen oder ob diese innerhalb der Routine deklariert werden sollen. Mit der Optionsgruppe ogrZugriffsart legen Sie fest, ob ein bestehender Datensatz bearbeitet oder ein neuer Datensatz angelegt werden soll.
Schließlich bestimmt die Optionsgruppe ogrZugriffstechnik, ob beim Aktualisieren beziehungsweise Hinzufügen des Datensatzes DAO verwendet werden soll oder eine per Execute ausgeführte Aktionsabfrage.
In einem weiteren Rahmen finden Sie noch drei Optionen:
- chkZwischenablage: Ist diese Option aktiviert, schreibt das Tool den erzeugten Code gleich in die Zwischenablage. Von dort können Sie diesen gleich in das Zielmodul einfügen.
- chkIsoDatum: Wenn Sie diese Option aktivieren, fügt das Tool zu Datumsvariablen, die in Aktionsabfragen verwendet werden, die Funktion IsoDatum hinzu. Diese wandelt ein Datum in ein SQL-taugliches Datumsformat um.
- chkKommaErsetzen: Bei Verwendung von Zahlenwerten in Aktionsabfragen kann es zu Problemen kommen, wenn als Trennzeichen das Komma eingesetzt wird. Wenn die Option chkKommaErsetzen aktiviert ist, ersetzt das Tool Kommata in Dezimalzahlen durch den Punkt.
Zusammenstellen der Prozedur auf Basis der gewählten Parameter
In den folgenden Abschnitten erfahren Sie, wie das Tool den gewünschten Code zusammenstellt.
Den Startschuss geben Sie mit einem Klick auf die Schaltfläche cmdCodeErstellen. Dies löst die folgende Prozedur aus:
Private Sub cmdCodeErstellen_Click() If IsNull(Me!cboTabelle) Then MsgBox "Bitte wählen Sie eine Tabelle aus." Exit Sub End If If Me!lstFelder.ItemsSelected.Count = 0 Then MsgBox "Bitte wählen Sie die betroffenen Felder aus." Exit Sub End If Me!txtZugriffscode = CreateCode End Sub
Die Prozedur stellt sicher, dass der Benutzer eine Tabelle ausgewählt und mindestens ein Feld aus dem Listenfeld lstFelder markiert hat.
Ist dies gewährleistet, wird das Textfeld txtZugriffscode durch die Funktion CreateCode mit der gewünschten Prozedur gefüllt.
Code erstellen
Der Prozess findet hauptsächlich in der Funktion CreateCode statt, die eine Variable namens strCode nach und nach mit den Zeilen der zu erstellenden Routine füllt:
Private Function CreateCode() As String Dim strCode As String ... End Function
Wir starten mit der Kopfzeile der Prozedur. Bereits diese kann nicht einfach erstellt werden, denn gegebenenfalls sollen ja die Variablen, auf deren Basis der Datenzugriff erfolgt, als Parameter übergeben werden. Die Zeile zum Erstellen der ersten Zeile ruft daher gleich zwei weitere Funktionen auf:
strCode = strCode & "Public Sub " & GetCodename & "(" & GetParameters & ")" & vbCrLf
Die erste Funktion namens GetCodename setzt den Namen der Prozedur zusammen (s. Listing 1). Der Name besteht aus einem Präfix, das entweder Add_ oder Update_ lautet – je nachdem, ob der Datensatz hinzugefügt oder geändert werden soll. Das Präfix wird durch eine Choose-Funktion auf Basis des Wertes der Optionsgruppe ogrZugriffsart ermittelt. Dann folgt der Name der Tabelle und schließlich ein Suffix, das die Art des Datenzugriffs repräsentiert – entweder _DAO oder _SQL (je nach Wert der Optionsgruppe ogrZugriffstechnik). Für das Hinzufügen eines Datensatzes per DAO zur Tabelle tblArtikel entsteht so ein Routinenname wie Add_tblArtikel_DAO.
Listing 1: Prozedurname erstellen
Private Function GetCodename() As String Dim strCodename As String strCodename = Choose(Me!ogrZugriffsart, "Add_", "Update_") strCodename = strCodename & Me!cboTabelle strCodename = strCodename & Choose(Me!ogrZugriffstechnik, "_DAO", "_SQL") GetCodename = strCodename End Function
Variablen als Parameter übergeben
Wenn der Benutzer den Wert 1 für die Optionsgruppe ogrVariablen ausgewählt hat, sollen alle Variablen in der Parameterliste im Prozedurkopf erscheinen – sowohl die für die neuen Daten als auch für die eventuell zu verwendenden Kriterien. Diese Liste erzeugt die Funktion GetParameters (s. Listing 2). Sie prüft zunächst den Wert der Optionsgruppe ogrVariablen. Danach durchläuft sie alle markierten Elemente der beiden Listenfelder lstFelder und lstKriterien. Für jeden Eintrag fügt sie der Variablen str einen Ausdruck nach dem Schema [Präfix]Feldname As [Datentyp] hinzu. Das Präfix wird dabei über eine weitere Funktion namens GetPraefix ermittelt (s. Listing 3). Diese Funktion erwartet einen Verweis auf das dem Eintrag entsprechende Field-Objekt der Tabellendefinition. Abhängig vom Wert der Type-Eigenschaft dieses Field-Objekts liefert sie ein passendes Präfix wie etwa str oder lng zurück.
Listing 2: Parameterliste zusammenstellen
Private Function GetParameters() As String Dim db As DAO.Database Dim tdf As DAO.TableDef Dim fld As DAO.Field Dim i As Integer Dim str As String If Me!ogrVariablen = 1 Then Set db = CurrentDb Set tdf = db.TableDefs(Me!cboTabelle) For i = 0 To Me!lstFelder.ItemsSelected.Count - 1 Set fld = tdf.Fields(Me!lstFelder.ItemsSelected(i)) str = str & GetPraefix(fld) & fld.Name & " As " & GetDatatype(fld) & ", " Next i For i = 0 To Me!lstKriterien.ItemsSelected.Count - 1 Set fld = tdf.Fields(Me!lstKriterien.ItemsSelected(i)) str = str & GetPraefix(fld) & fld.Name & "_Crit As " & GetDatatype(fld) & ", " Next i GetParameters = Left(str, Len(Trim(str)) - 1) End If End Function
Listing 3: Präfix für ein Feld ermitteln
Private Function GetPraefix(fld As DAO.Field) As String Select Case fld.Type Case dbBoolean GetPraefix = "bol" Case dbInteger GetPraefix = "int" Case dbLong GetPraefix = "lng" Case dbCurrency GetPraefix = "cur" Case dbSingle GetPraefix = "sng" Case dbDouble GetPraefix = "dbl" Case dbText, dbMemo GetPraefix = "str" Case dbDate GetPraefix = "dat" Case Else Debug.Print fld.Type, fld.Name End Select End Function
Listing 5: Deklarationszeilen zusammenstellen
Private Function GetDeclarations() Dim db As DAO.Database Dim tdf As DAO.TableDef Dim fld As DAO.Field Dim i As Integer Dim str As String If Me!ogrVariablen = 2 Then Set db = CurrentDb Set tdf = db.TableDefs(Me!cboTabelle) For i = 0 To Me!lstFelder.ItemsSelected.Count - 1 Set fld = tdf.Fields(Me!lstFelder.ItemsSelected(i)) str = str & GetDeclaration(fld) Next i For i = 0 To Me!lstKriterien.ItemsSelected.Count - 1 Set fld = tdf.Fields(Me!lstKriterien.ItemsSelected(i)) str = str & GetDeclaration(fld, "_Crit") Next i GetDeclarations = str End If End Function Private Function GetDeclaration(fld As DAO.Field, Optional strSuffix As String) As String GetDeclaration = " Dim " & GetPraefix(fld) & fld.Name & strSuffix & " As " _ & GetDatatype(fld) & vbCrLf End Function