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
Ganz ähnlich arbeitet die Funktion GetDatatype (s. Listing 4): Sie erwartet ebenfalls den Verweis auf das aktuelle Field-Objekt und ermittelt in einem Select Case-Konstrukt den Datentyp für diesen Ausdruck, also beispielsweise String oder Integer.
Listing 4: Datentyp eines Feldes ermitteln
Private Function GetDatatype(fld As DAO.Field) As String
Select Case fld.Type
Case dbBoolean
GetDatatype = "Boolean"
Case dbInteger
GetDatatype = "Integer"
Case dbLong
GetDatatype = "Long"
Case dbCurrency
GetDatatype = "Currency"
Case dbSingle
GetDatatype = "Single"
Case dbDouble
GetDatatype = "Double"
Case dbText, dbMemo
GetDatatype = "String"
Case dbDate
GetDatatype = "Date"
Case Else
Debug.Print fld.Type, fld.Name
End Select
End Function
Deklaration von Database und Recordset
Danach folgt die Deklaration einiger wichtiger Objekte. Auf jeden Fall benötigt die Prozedur ein Database-Objekt, dessen Deklaration die folgende Zeile hinzufügt:
strCode = strCode & " Dim db As
DAO.Database" & vbCrLf
Wenn die Daten per DAO hinzugefügt oder geändert werden sollen, brauchen Sie außerdem ein Recordset-Objekt. Ob dieses deklariert wird oder nicht, legt der Wert der Optionsgruppe ogrZugriffstechnik fest. Der Wert 1 steht für DAO und erfordert das Anlegen der entsprechenden Zeile:
If Me!ogrZugriffstechnik = 1 Then
strCode = strCode & " Dim
rst As DAO.Recordset" & vbCrLf
End If
Deklaration in der Prozedur
Die folgende Zeile ruft die Funktion GetDeclarations auf und fügt so die Deklarationszeilen zur Prozedur hinzu (s. Listing 5):
Listing 9: Aktionsabfrage zusammenbauen
Private Function GetExecute() As String
Select Case Me!ogrZugriffsart
Case 1
GetExecute = " db.Execute(""INSERT INTO " & Me!cboTabelle & "(" & GetFields _
& ") VALUES(" & GetValues & ")"")" & vbCrLf
Case 2
If Len(GetCriteria) > 0 Then
GetExecute = " db.Execute(""UPDATE " & Me!cboTabelle & " SET " & GetUpdates _
& " WHERE " & GetCriteria & ")" & vbCrLf
Else
GetExecute = " db.Execute(""UPDATE " & Me!cboTabelle & " SET " & GetUpdates & vbCrLf
End If
End Select
End Function
strCode = strCode & GetDeclarations
GetDeclarations liefert allerdings nur ein Ergebnis zurück, wenn die Optionsgruppe ogrVariablen den Wert 2 enthält, die Variablen also innerhalb der Prozedur deklariert werden sollen.
Dies wird in einer If…Then-Bedingung geprüft. Innerhalb dieser Bedingung durchläuft die Funktion zwei For…Next-Schleifen. Die erste durchläuft alle markierten Einträge des Listenfeldes lstFelder, das zweite kümmert sich um die markierten Einträge von lstKriterien.
Innerhalb dieser Schleifen befinden sich jeweils zwei Anweisungen. Die erste füllt eine Field-Variable mit einem Verweis auf das Feld der Zieltabelle, dessen Name dem im Listenfeld ausgewählten Eintrag entspricht. Dieser Objektverweis wird im Aufruf der Funktion GetDeclaration in der zweiten Zeile als Parameter übergeben.
GetDeclaration liefert einen Ausdruck nach dem Schema Dim [Praefix]Feldname[Suffix] As [Datentyp] zurück. Dabei kommen die bereits weiter oben vorgestellten Funktionen GetPraefix und GetDatatype zum Einsatz.
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