Mitarbeiterverwaltung mit der MSDE (Teil 2)

Lies diesen Artikel und viele weitere mit einem kostenlosen, einwöchigen Testzugang.

Autor: André Minhorst und Michael Kessel, Duisburg

Eine Datenbank ist prädestiniert für die Pflege der Informationen über Ihre Mitarbeiter. Sie dient der Speicherung der Mitarbeiterdaten sowie der Daten über deren Beschäftigungsverhältnisse und weiterer Informationen – wie z. B. der Projekte, an denen sie beteiligt sind. In der Ausgabe 7/2001 haben Sie im ersten Teil des vorliegenden Beitrags erfahren, wie Sie mit Access Tabellen und deren Beziehungen auf der Basis der MSDE aufbauen können, und einige Beispiele für gespeicherte Prozeduren und Trigger kennen gelernt. Nun geht es an das Frontend – und damit an die Programmierung der Formulare und Berichte.

Hinweis

Der vorliegende Beitrag setzt das Vorhandensein von Access 2000 sowie die Installation der MSDE bzw. des SQL Servers voraus. Wie das funktioniert, erfahren Sie im Beitrag Die Microsoft Data Engine der Ausgabe 1/2000 von Access im Unternehmen. Sie finden den Beitrag im PDF-Format auf der Heft-CD.

Die Mitarbeiterverwaltung benötigt fünf Formulare, von denen zwei allerdings Unterformulare eines weiteren Formulars sind.

Das erste Formular namens frmMitarbeiterAuswahl (siehe Bild 1) dient zur Auswahl des anzuzeigenden Mitarbeiters aus einem Listenfeld. Dabei gibt es noch einige Optionen, mit denen die Auswahl der angezeigten Mitarbeiter eingeschränkt werden kann. Von diesem Formular aus kann man Detailinformationen zu dem ausgewählten Mitarbeiter aufrufen.

Die Detailinformationen werden in dem Formular frmMitarbeiterDetail angezeigt (siehe Bild 8). Das Formular ist gleichzeitig Hauptformular für zwei Unterformulare.

Das Unterformular sfmMitarbeiterBeschaeftigungen zeigt das aktuelle und ggf. vorherige Beschäftigungsverhältnis des jeweiligen Mitarbeiters an. Das Unterformular namens sfmMitarbeiterProjekte zeigt die aktuellen Projekte eines Mitarbeiters.

Hinweis

Regelmäßigen Lesern von Access im Unternehmen und des Praxishandbuchs Access sind die meisten der nachfolgend beschriebenen Techniken zur Erstellung von Formularen, Unterformularen und deren Steuerelementen bekannt. Daher wird verstärkt auf solche Techniken eingegangen, bei denen es Unterschiede zwischen herkömmlichen Access-Datenbanken und Access-Projekten gibt.

Das Formular zur Anzeige der Mitarbeiterdetails soll wie in Bild 1 aussehen. Es beinhaltet vier Schaltflächen, ein Kontrollkästchen, zwei Textfelder und ein Listenfeld.

Legen Sie am besten zunächst ein neues Formular mit den entsprechenden Steuerelementen an und bezeichnen Sie die Steuerelemente wie in Tab. 1 angegeben. Speichern Sie es anschließend unter dem Namen frmMitarbeiterAuswahl.

Bild 1: Das fertige Formular zur Verwaltung der Mitarbeiter

Steuerelementname

Steuerelement bezeichnung

cmdNeuerMitarbeiter

Neuer Mitarbeiter

cmdMitarbeiterLoeschen

Mitarbeiter löschen

cmdBeschaeftigungLoeschen

Beschäftigung löschen

cmdListeDrucken

Aktuelle Liste drucken

ctlAlle

txtEintrittVon

Eintritt von:

txtEintrittBis

Eintritt bis:

lstMitarbeiter

Tab. 1: Steuerelemente des Formulars frmMitarbeiterAuswahl

Die Steuerelemente zur Filterung der Datensatzherkunft des Listenfeldes

Um den Rahmen des vorliegenden Beitrags nicht zu sprengen, soll der Inhalt des Listenfeldes nur nach zwei Kriterien gefiltert werden. Das erste Argument liefert das Kontrollkästchen ctlAlle. Es soll festlegen, ob alle Beschäftigungsverhältnisse aller Mitarbeiter oder nur aktuelle Beschäftigungsverhältnisse angezeigt werden sollen.

Außerdem soll der Benutzer den Zeitraum des Starts des Beschäftigungsverhältnisses eingeben können. Dazu dienen die beiden Textfelder txtEintrittVon und txtEintrittBis.

Die Schaltflächen zum Auslösen weiterer Aktionen

Das Formular enthält vier Schaltflächen. Die Schaltflächen dienen zum Anlegen eines neuen Mitarbeiters, zum Löschen eines Mitarbeiters oder eines Beschäftigungsverhältnisses sowie zum Drucken einer Liste mit den aktuell ausgewählten Mitarbeitern bzw. Beschäftigungsverhältnissen.

Die Beschreibung der Funktionalität der Schaltflächen folgt im Anschluss an die Beschreibung des Listenfeldes.

Das Listenfeld lstMitarbeiter

Kernstück des Formulars ist das Listenfeld. Es dient primär zur Anzeige der Mitarbeiter. Außerdem kann der Benutzer per Doppelklick auf einen Eintrag des Listenfeldes die Detailinformationen eines Mitarbeiters in einem weiteren Formular anzeigen.

Während es bei Access-Datenbanken leicht ist, den Inhalt eines Listenfeldes entsprechend bestimmter Auswahlkriterien zu filtern, ist dies bei Access-Projekten in Zusammenarbeit mit der MSDE oder dem SQL Server etwas komplizierter.

Sie können zwar einfach eine entsprechende SQL-Anweisung zusammensetzen und als Datensatzherkunft des Listenfeldes angeben. Dies geht allerdings zu Lasten der Performance, da die Abfrage jedes Mal neu kompiliert und an den SQL Server geschickt werden muss.

Um die Performance bei der Abfrage der gewünschten Daten zu erhöhen, sollten Sie die Abfrage möglichst auf dem SQL Server ausführen. Für einfache Abfragen verwenden Sie einfach eine Sicht. Diese hilft aber nicht mehr weiter, wenn die Abfrage die Verwendung von Parametern erfordert. Daher verwenden Sie eine gespeicherte Prozedur.

Bild 2: Die gespeicherte Prozedur procLstMitarbeiter

Bild 3: Ergebnis einer gespeicherten Prozedur

Datensatzherkunft des Listenfeldes

An das Listenfeld sollen drei Parameter übergeben werden: Der Wert des Kontrollkästchens ctlAlle sowie die Inhalte der beiden Textfelder txtEintrittVon und txtEintrittBis.

Eine gespeicherte Prozedur (siehe Bild 2) soll die übergebenen Parameter auswerten und das Ergebnis als Datensatzherkunft des Listenfeldes zurückgeben.

Die gespeicherte Prozedur verwendet drei Parameter. Der Parameter @Alle hat den Datentyp Integer. Der Hintergrund ist die unterschiedliche Anwendung von Ja/Nein-Werten in Access-Datenbanken und im SQL Server. Während es in Access-Datenbanken die Werte 0 (Falsch), -1 (Wahr) und NULL gibt, verwendet der Datentyp BIT des SQL Servers lediglich die Werte 1 (Wahr) und 0 (Falsch).

Die gespeicherte Prozedur besteht aus einer IF-Abfrage, die den Inhalt der Variablen @Alle überprüft. Falls vom Formular der Wert 0 übergeben wird, sollen nur diejenigen Beschäftigungsverhältnisse zurückgegeben werden, deren Austrittsdatum den Wert NULL hat.

Die beiden Parameter @EintrittVon und @EintrittBis dienen der Einschränkung der Datensätze nach dem Eintrittsdatum.

Sie enthalten jeweils einen Standardwert. Die Standardwerte kommen zum Einsatz, falls die entsprechenden Textfelder keinen Inhalt haben und damit keine Parameter übergeben werden.

Testen der Datensatzherkunft

Nachdem Sie die gespeicherte Prozedur eingegeben haben, können Sie direkt mit einem Test beginnen (vorausgesetzt, Sie haben bereits einige Daten für die beiden Tabellen tblMitarbeiter und tblBeschaeftigungen eingegeben): Starten Sie einfach einmal die Prozedur per Doppelklick auf ihren Namen im Datenbankfenster.

Daraufhin erscheinen nacheinander drei Eingabefenster, die zur Abfrage der drei Parameter dienen. Nach der Eingabe der Parameter erscheint das Ergebnis der Abfrage (siehe Bild 3).

Sub ListeAktualisieren()
    Dim Parameter As String
    Parameter = "@Alle = " & Me.ctlAlle
    Parameter = Parameter & IIf(IsNull(Me.txtEintrittVon), "", ", @EintrittVon = ''" _        & Me.txtEintrittVon & "''")
    Parameter = Parameter & IIf(IsNull(Me.txtEintrittBis), "", ", @EintrittBis = ''" _        & Me.txtEintrittBis & "''")
    Me.lstMitarbeiter.RowSource = "EXEC procLstMitarbeiter " & Parameter
    Me.lstMitarbeiter.Requery
End Sub

Quellcode 1

Funktionalität der Steuerelemente

In den nächsten Abschnitten erfahren Sie, wie Sie das Listenfeld mit den Ergebnissen der gespeicherten Prozedur füllen und wie Sie der gespeicherten Prozedur die erforderlichen Parameter übergeben.

Die Prozedur ListeAktualisieren

Die Prozedur aus Quellcode 1 dient der Aktualisierung des Listenfeldes. Sie setzt einen String zusammen, der den Namen der gespeicherten Prozedur procLstMitarbeiter sowie die entsprechenden Parameter enthält.

Die Parameter enthalten die Werte der Steuerelemente ctlAlle, txtEintrittVon und txtEintrittBis. Die Parameter @EintrittVon und @EintrittBis fallen weg, wenn die entsprechenden Textfelder leer sind. In dem Fall verwendet die gespeicherte Prozedur die im Deklarationsteil angegebenen Standardwerte (siehe Bild 2).

Die Prozedur legen Sie im Modul des Formulars frmMitarbeiterAuswahl an. Um das Modul zu öffnen, aktivieren Sie die Entwurfsansicht des Formulars und verwenden den Menübefehl Ansicht/Code. Daraufhin öffnet sich das entsprechende Modul.

Anschließend müssen Sie dafür sorgen, dass diese Prozedur auch ausgelöst wird. Das soll beim öffnen des Formulars sowie nach der Aktualisierung der drei Steuerelemente ctlAlle, txtEintrittVon und txtEintrittBis passieren.

Dazu legen Sie jeweils eine neue Prozedur für das Ereignis Beim öffnen des Formulars sowie für das Ereignis Nach Aktualisierung der drei anderen Steuerelemente an und geben als einzigen Befehl den folgenden Ausdruck an:

ListeAktualisieren

Weitere Informationen zum Anlegen einer Ereignisprozedur finden Sie im Beitrag Anlegen von Ereignisprozeduren im vorliegenden Heft.

Geben Sie außerdem für die Eigenschaft Standardwert des Kontrollkästchens den Wert 0 an.

Anschließend können Sie direkt die neuen Funktionen ausprobieren, indem Sie das Formular speichern und anschließend in der Formularansicht öffnen. Das Ergebnis sieht nun wie in Bild 4 aus. Leider entspricht dies aber nicht dem gewünschten Ergebnis.

Sie können jedoch mit drei kleinen änderungen am Listenfeld nachhelfen:

  • Geben Sie für die Eigenschaft Spaltenanzahl den Wert 8 ein.
  • Stellen Sie in der Eigenschaft Spaltenbreiten die gewünschte Breite der Spalten ein, z. B. auf den Wert 1cm; 2cm; 2cm; 2cm; 2cm; 2cm; 2cm; 0cm.
  • Setzen Sie die Eigenschaft Spaltenüberschriften auf den Wert Ja.
  • Ein erneuter Wechsel in die Formularansicht lässt das Ergebnis direkt freundlicher aussehen (siehe Bild 5).

    Funktionalität der Schaltflächen

    Bild 4: Das Listenfeld zeigt nicht die erwarteten Daten an.

    Bild 5: Das Listenfeld mit funktionierender Anzeige

    Nun widmen Sie sich der Funktionalität der Schaltflächen. Dazu legen Sie für jede Schaltfläche eine eigene Ereignisprozedur an, die in den folgenden Abschnitten beschrieben wird.

    Private Sub cmdNeuerMitarbeiter_Click()
        DoCmd.OpenForm "frmMitarbeiterDetail", , , , acFormAdd, acDialog
        Me.lstMitarbeiter.Requery
    End Sub

    Quellcode 2

    Neuer Mitarbeiter

    Die Schaltfläche cmdNeuerMitarbeiter soll das Formular frmMitarbeiterDetail öffnen, das weiter unten beschrieben wird. In Quellcode 2 finden Sie die dazu notwendige Ereignisprozedur.

    Sie verwenden in der Prozedur die Konstante acFormAdd für den Parameter DataMode, damit das aufgerufene Formular nur zur Eingabe verwendet werden kann.

    Der Wert acDialog für den Parameter WindowMode bewirkt, dass die Ausführung aller anderen Aktivitäten unterbrochen wird, bis der Anwender das aufgerufene Formular wieder schließt.

    Um die Schaltfläche zu testen, können Sie auf die Schnelle ein leeres Formular namens frmMitarbeiterDetail erstellen.

    Durch Betätigen der Schaltfläche können Sie das neue Formular öffnen. Nach dem Schließen des Formulars kehren Sie automatisch wieder zum aufrufenden Formular zurück.

    Mitarbeiter löschen

    Die Schaltfläche cmdMitarbeiterLoeschen soll das Löschen des aktuell im Listenfeld markierten Mitarbeiters bewirken. Dazu legen Sie für die Ereigniseigenschaft Beim Klicken die Prozedur aus Quellcode 3 an.

    Private Sub cmdMitarbeiterLoeschen_Click()
        If MsgBox("Mitarbeiter und verknüpfte Beschäftigungen löschen", _
            vbExclamation + vbOKCancel, "Mitarbeiter löschen") = vbOK Then
            Dim con As ADODB.Connection
            Set con = CurrentProject.Connection
            con.Execute "procMitarbeiterLoeschen " & Me.lstMitarbeiter
            Set con = Nothing
            Me.lstMitarbeiter.Requery
        Else
            Exit Sub
        End If
    End Sub

    Quellcode 3

    Die Prozedur fragt zunächst ab, ob der ausgewählte Datensatz auch tatsächlich gelöscht werden soll. Falls ja, führt die Abfrage eine gespeicherte Prozedur zum Löschen des Datensatzes durch. Der Text der gespeicherten Prozedur zum Löschen des gewünschten Datensatzes ist in Quellcode 4 enthalten.

    Alter Procedure procMitarbeiterLoeschen (@Mitarbeiternummer INT)
    As
        set nocount on
        DELETE FROM dbo.tblMitarbeiter
        WHERE MitarbeiterID =         @Mitarbeiternummer
        return

    Quellcode 4

    Die VBA-Prozedur übergibt dabei die Datensatznummer des entsprechenden Mitarbeiters an die gespeicherte Prozedur.

    Möglicherweise fragen Sie sich, was mit den Einträgen der Tabelle tblBeschaeftigungen passiert, die mit dem gelöschten Mitarbeiter verknüpft sind. Diese können ja eigentlich ebenfalls entfernt werden, da keine passenden Mitarbeiterdaten mehr vorhanden sind.

    Im ersten Teil des vorliegenden Beitrags in Ausgabe 7/2001 haben Sie bereits einen Trigger erstellt, der dieses Problem löst. Dieser Trigger löscht alle Datensätze der Tabelle tblBeschaeftigungen, die zum gelöschten Mitarbeiter gehören.

    Beschäftigung löschen

    Möglicherweise möchten Sie einmal nicht die kompletten Mitarbeiterdaten, sondern nur die Daten zu einem Beschäftigungsverhältnis löschen. In dem Fall verwenden Sie die Schaltfläche mit der Beschriftung Beschäftigung löschen.

    Die Vorgehensweise ist prinzipiell mit der beim Löschen eines Mitarbeiters vergleichbar. Allerdings kann es vorkommen, dass Sie auf diese Weise alle Beschäftigungsverhältnisse eines Mitarbeiters löschen und dieser dann nicht mehr im Listenfeld angezeigt wird.

    Daher soll die Anwendung – falls nur ein Beschäftigungsverhältnis für den Mitarbeiter vorhanden ist – den Benutzer fragen, ob der Mitarbeiter direkt mitgelöscht werden soll.

    Der Nebeneffekt dieser Mehrarbeit ist, dass Sie direkt lernen, eine gespeicherte Prozedur mit Rückgabeparameter zu erstellen.

    Der Ablauf beim Löschen der Beschäftigung sieht folgendermaßen aus: Zunächst wählt der Benutzer das zu löschende Beschäftigungsverhältnis aus und betätigt die entsprechende Schaltfläche.

    Die dadurch ausgelöste Prozedur überprüft, ob es noch weitere Beschäftigungsverhältnisse zu dem entsprechenden Mitarbeiter gibt. Falls ja, fragt sie den Benutzer, ob direkt der komplette Mitarbeiterdatensatz gelöscht werden soll.

    Gespeicherte Prozedur mit Rückgabeparameter

    Alter Procedure procBeschaeftigungenZaehlen (@BeschaeftigungID INT, 
    @AnzahlBeschaeftigungen INT OUTPUT)
    As
    DECLARE @MitarbeiterID INT
    SELECT @MitarbeiterID = MitarbeiterID 
    FROM tblBeschaeftigungen 
    WHERE BeschaeftigungID = @BeschaeftigungID
    SELECT * 
    FROM tblBeschaeftigungen 
    WHERE MitarbeiterID = @MitarbeiterID
    SELECT @AnzahlBeschaeftigungen = @@ROWCOUNT
    return 

    Quellcode 5

    Public Function AnzahlBeschaeftigungen _    (BeschaeftigungID As Integer)
        Dim cnn As ADODB.Connection
        Dim cmd As ADODB.Command
        Dim prm As ADODB.Parameter
        Set cnn = CurrentProject.Connection
        Set cmd = New ADODB.Command
        cmd.CommandText = "procBeschaeftigungenZaehlen"
        cmd.CommandType = adCmdStoredProc
        cmd.ActiveConnection = cnn
        Set prm = cmd.CreateParameter("@BeschaeftigungID", _        adInteger, adParamInput)
        prm.Value = BeschaeftigungID
        cmd.Parameters.Append prm
        Set prm = cmd.CreateParameter _        ("@AnzahlBeschaeftigungen", adInteger, _        adParamOutput)
        cmd.Parameters.Append prm
        cmd.Execute
        AnzahlBeschaeftigungen = _        cmd.Parameters("@AnzahlBeschaeftigungen")
    End Function

    Quellcode 6

    Sie benötigen eine gespeicherte Prozedur zur Ermittlung der Anzahl der Beschäftigungen des Mitarbeiters (s. Quellcode 5). Die Prozedur enthält die zwei Parameter @Beschaeftigung und @AnzahlBeschaeftigungen. Der zweite Parameter wird durch das angehängte Schlüsselwort OUTPUT als Rückgabeparameter deklariert.

    Nach der Deklaration des Hilfsparameters @MitarbeiterID wird diesem in einer Abfrage die MitarbeiterID des Mitarbeiters zugewiesen, zu dem das zu löschende Beschäftigungsverhältnis gehört.

    In einer zweiten Abfrage werden alle Datensätze der Tabelle tblBeschaeftigungen ermittelt, die zu diesem Mitarbeiter gehören. Hinter dem Ausdruck @@ROWCOUNT verbirgt sich eine der vielen eingebauten SQL-Funktionen. Diese ermittelt die Anzahl der Datensätze des aktuellen Abfrageergebnisses und weist sie dem Rückgabeparameter @AnzahlBeschaeftigungen zu.

    Aufruf einer gespeicherten Prozedur mitRückgabeparametern

    Die Funktion zum Aufruf der gespeicherten Prozedur und zum Auslesen des Rückgabeparameters finden Sie in Quellcode 6.

    Ende des frei verfügbaren Teil. Wenn Du mehr lesen möchtest, hole Dir ...

    Testzugang

    eine Woche kostenlosen Zugriff auf diesen und mehr als 1.000 weitere Artikel

    diesen und alle anderen Artikel mit dem Jahresabo

    Schreibe einen Kommentar