Prof. Dr.-Ing. Christine Wahmkow Fachhochschule Stralsund Fachbereich Maschinenbau Lehrgebiet Informatik Eine Einführung in ACCESS 1. Tabellen einrichten und verknüpfen Zu einer Datenbank gehören Objekte (Tabellen, Formulare, Berichte usw.). Vom Betriebssystem Windows wird nur der Name der Datenbank verwaltet. Alle Objekte (Tabellen, Formulare, Abfragen, Makros usw.) werden intern von ACCESS verwaltet. Eine Datenbank kann also als Projekt verstanden werden. Die Datenbank mit der Dateierweiterung MDB muß gleich zu Beginn der Arbeit definiert und gespeichert werden. Aus dem Datenbankfenster kann man eine Kategorie von Objekten auswählen: Kategorien Hier kann die Tabelle angelegt werden, d.h., die Struktur wird beschrieben: Primärschlüs sel ____________________________________ Arbeitsblätter ACCESS Seite 1 Zur Definition des Primärschlüssels markiert man das entsprechende Attribut (Feld) und klickt auf das Symbol „Primärschlüssel setzen“ Sollen mehrere Elemente den Primärschlüssel bilden, so werden diese Elemente vor der Definition des Schlüssels im Block markiert. In der Datenblattansicht können Werte eingegeben werden: Wechsel zwischen Datenblatt- und Entwurfs-ansicht Beziehungen zwischen Tabellen werden wie folgt definiert: Menü EXTRAS Beziehungen... oder durch dieses Symbol: Mastertabelle Verknüpfen der Feldelemente durch Klicken in der Mastertebelle und Ziehen in die Detailtabelle Bearbeiten einer Beziehung durch Doppelklick auf die Verbindungslinie; es öffnet sich das abgebildete Fenster ____________________________________ Arbeitsblätter ACCESS Seite 2 Was heißt - referenielle Integrität: In der Detailtabelle dürfen nur Datensätze stehen, deren verknüpftes Feldelement auch in der Mastertabelle vorkommt. Beispiel: Im Lager dürfen nur Artikel liegen, deren Artikelnummer in der Tabelle T_Artikel auch vorkommt. - Aktualisierungsweitergabe: Bekommt in der Mastertabelle das verknüpfende Feldelement einen neuen Wert, wird auch in der Detailtabelle automatisch mit korrigiert. Beispiel: Die Artikelnummern werden in der Tabelle T_Artikel geändert; automatisch ändern sich die Artikelnummern auch in den Detailtabellen - Löschweitergabe Wird in der Mastertabelle ein Datensatz mit einem verknüpften Feldelement gelöscht, wird in der Detailtabelle der Datensatz mit dem entsprechenden Feldelement auch gelöscht. Beispiel: In der Tabelle T_Artikel wird der Datensatz mit der Artikelnummer 10 gelöscht; in allen Detailtabellen wird dieser Datensatz ebenfalls gelöscht. Bei der Definition der Beziehungen sind diese drei Eigenschaften stets kritisch zu beurteilen !!! ____________________________________ Arbeitsblätter ACCESS Seite 3 2. Formulare erstellen Es gibt 2 Arten von Formularen: - als Benutzeroberfläche zum Navigieren in der Anwendung - zur Datenein- und -ausgabe Formulare als Benutzeroberfläche steuern den Fluß einer Anwendung. Der Benutzer hat es während seiner Tätigkeit zum Erfassen, Ändern, Hinzufügen, Recherchieren in einer Datenbank ausschließlich mit Formularen zu tun. Wir als Entwickler müssen natürlich diese bedienfreundlichen Benutzeroberflächen erst einmal zur Verfügung stellen. Zuerst soll ein Hauptformular für die Anwendung erstellt werden. Das enthält außer paar hübschen ansprechenden bekannten Windows-Elementen wie Grafiken, Linien, farbigen Hintergrund usw. vor allem Steuerelemente in Form von Befehlsschaltflächen. Datenein- und –ausgabe ist nicht möglich, deshalb können die Navigationsleisten und der Datensatzmarkierer ausgeschaltet werden. ( siehe Eigenschaften des Formulars) Das Klicken auf die Befehlsschaltflächen öffnet jeweils ein Formular zur Datenein- oder – ausgabe. Diese Formulare müssen natürlich erst vorhanden sein. Deshalb bietet sich bei der Entwicklung einer Datenbank wieder die Bottom-up-Methode an. Das heißt von unten nach oben. Die unteren Formulare können erst mal Dummies sein, d.h. leere Formulare. Hauptformular Button1 Formular 1 Button2 Formular 2 Für unsere Lagerverwaltung könnte das Hauptformular beispielhaft so aussehen: ____________________________________ Arbeitsblätter ACCESS Seite 4 Die wesentliche Eigenschaft ergibt sich für eine Befehlsschaltfläche aus der Vorgabe „Beim Klicken“. Hier im Beispiel wird das Makro „Schaltfläche Datenbankfenster“ aufgerufen. Die Eigenschaften der Befehlsschaltfläche sind in der unteren Abbildung zu sehen. Der Nutzer will bei seiner Arbeit so wenig wie möglich mit dem eigentlichen Datenbanksystem ACCESS zu tun haben. Am besten ist es, wenn beim Start der Anwendung sofort das Hauptformular geöffnet wird. Das lässt sich in ACCESS sehr einfach realisieren: Menü EXTRAS Start.... In folgendem Dialogfenster sind ein paar wenige Eintragungen notwendig: ____________________________________ Arbeitsblätter ACCESS Seite 5 1. Erstellen Sie selbständig das Hauptformular, so daß die Schaltflächen „Datenbankfenster“ und „Anwendung verlassen“ tatsächlich funktionieren. 2. Definieren Sie die Startoptionen, wie oben beschrieben. 3. Erstellen Sie selbst ein Formular zum Eingeben von Artikeln (PC-Gehäuse). Verwenden Sie ein Kombinationsschaltfeld für die Eingabe der Gehäusegrößen. Legen Sie dafür vorher eine einfache Tabelle mit möglichen Gehäusegrößen an. 4. Definieren Sie für das Formular und dessen Elemente Eigenschaften. Das Eigenschaftenfenster für das Formular öffnen Sie nach untenstehender Abbildung. Klicken Sie mit der rechten Maustaste auf ein Formularelement, um dessen Eigenschaftenfenster zu öffnen. Definieren Sie das Erzwingen der Eingabe bei den Feldern der Tabelle. Eigenschaften von Formularen definieren: Rechter Mausklick auf das kleine schwarze Quadrat öffnet das Eigenschaftenfens ter 3. Abfragen erstellen und anwenden Abfragen ermöglichen die verschiedensten Sichten auf eine Datenbank. Das heißt, daß die Daten einer Datenbank verschieden kombiniert und dem Nutzer zur Verfügung gestellt werden können. Mittels Abfrage stellt man also eine Frage an eine oder mehrere Tabellen. Die Antwort darauf ist ein Dynaset, was dynamische Menge heißt. Dynamisch deshalb, weil sich die Antworten immer entsprechend dem Inhalt der Tabellen ändern. Überlegen Sie, welche Fragen und Antworten aus der Datenbank Lager.mdb interessant sein könnten. Zum Beispiel: Welche Lagerplätze befinden sich in der untersten Zeile des Hochregals ? Zur Definition einer Abfrage muß natürlich die Datenherkunft bekannt sein, also in welchen Tabellen befinden sich die Daten. Da ein Dynaset die gleichen Eigenschaften wie eine Tabelle hat, kann auch eine Abfrage Grundlage einer neuen Abfrage sein. Aber das wollen wir erst einmal außen vor lassen. ____________________________________ Arbeitsblätter ACCESS Seite 6 In die Abfrage werden alle interessierenden Datenfelder aufgenommen, die anderen bleiben unberücksichtigt. Das bringt ja den Vorteil, daß jetzt Daten gruppiert werden können, die in einem anderen Zusammenhang eigentlich gar nicht zueinander passen. Zum Beispiel: Die Datei Eingang enthält folgende Datensätze: Datensatz Art.nr. 1 10 2 20 3 10 Anzahl 100 200 200 Eingangsdatum Lagerfach 20.02. 2 20.02. 4 22.02. 4 Bei den folgenden Abfragen sind die aufgeführten Felder interessant und liefern die entsprechenden Ergebnisse: Abfrage aufzunehmende Feldelemente Art.nr., Anzahl Wieviel Teile der Art.nr.10 liegen im Lager ? Wieviel Teile wurden am 20.02. Anzahl, Eingangsdatum geliefert ? Wieviel Teile liegen im Lagerfach 4 ? Anzahl, Lagerfach Welche Teile wurden am 20.2. geliefert Art.nr., Eingangsdatum ? Ergebnis 300 300 400 10,20 Eine Abfrage ist in ACCESS wie folgt zu definieren: Beruht eine Abfrage auf mehreren Tabellen, muß es ein Feldelement als verknüpfendes Element geben. Existiert zwischen beiden Tabellen schon eine Beziehung, die schon in der Datenbankkonzeption festgelegt wurde, braucht man sich um die Verknüpfung nicht zu kümmern. Im anderen Fall kann man bei der Definition einer Abfrage die Verknüpfung definieren und deren Eigenschaften festlegen. ____________________________________ Arbeitsblätter ACCESS Seite 7 Definieren Sie für das Beispiel einige Abfragen ! (Siehe Tabelle ) Sie können die Ergebnisse über die Datenblattansicht kontrollieren. Um die Abfrage in die Anwendung zu integrieren, erstellt man ein Formular auf der Basis der Abfrage. Probieren Sie das aus ! ____________________________________ Arbeitsblätter ACCESS Seite 8 4. SQL - Structured Query Language Jede Abfrage wird von Access immer in die Sprache SQL übersetzt. Im Abfragefenster kann man in die SQL-Ansicht wechseln, hier ändern, hinzufügen oder über die Zwischenablage für VBA nutzen. SQL-Befehle - Der Select- Befehl: SELECT Spaltenliste FROM Tabellenliste [WHERE Bedingung] [ORDER BY Sortierfolge] Angaben in eckigen Klammern sind optional. Anwendungen: SELECT * FROM tabelle alle Feldelemente der Tabelle werden ausgewählt SELECT spalte1 FROM abfrage [AS alias] Tabellen oder Abfragen können Aliasnamen haben SELECT spalte1,spalte2 FROM tabelle WHERE ausdruck ausdruck ist ein logischer Vergleich SELECT * FROM t_filme WHERE filmtitel=“Titanic“ sucht alle Einträge zu „Jackess“ SELECT anzahl FROM t_Eingang WHERE datum BETWEEN #1/1/yy# AND #31/1/yy# SELECT name FROM t_kunde WHERE name LIKE „M*“ alle Namen, die mit M beginnen SELECT name FROM t_kunde WHERE name LIKE „M??er“ alle Maier, Meier, Mayer und Meyer SELECT * FROM t_kunde ORDER BY name sortierte Ausgabe Verknüpfungen mit INNER JOIN, LEFT JOIN und RIGHT JOIN SELECT spalten FROM tab1 INNER JOIN tab2 ON tab1.spalte1=tab2.spalte2 Ausgewählt werden alle gleichen Elemente, die in Tab1.spalte1 und Tab2.spalte2 vorkommen SELECT spalten FROM tab1 LEFT JOIN tab2 ON tab1.spalte1=tab2.spalte2 Ausgewählt werden alle Elemente, aus Tab1.spalte1 und die gleichen aus Tab2.spalte2 SELECT spalten FROM tab1 RIGHT JOIN tab2 ON tab1.spalte1=tab2.spalte2 Ausgewählt werden alle Elemente, aus Tab2.spalte2 und die gleichen aus Tab1.spalte1 Beispiel Tab1.spalte1 1 2 3 Tab2.spalte2 Legende: 3 4 5 INNER JOIN LEFT JOIN RIGHT JOIN Die Zusätze ALL, DISTINCT und DISTINCTROW SELECT ALL spalten FROM tab WHERE Bedingung alle Ergebnissdatensätze werden ermittelt SELECT DISTINCT spalte from …… alle doppelten Datensätze werden unterdrückt SELECT DISTINCTROW ….. Accessspezifisch in verknüpften Abfragen, nach Möglichkeit vermeiden !! ____________________________________ Arbeitsblätter ACCESS Seite 9 Neue Tabelle anlegen: CREATE TABLE tabelle (Feld1 Typ, Feld2 Typ,…) z. B. CREATE TABLE t_filme (filmnummer INTEGER, film TEXT) Aktualisieren einer Tabelle oder Abfrage UPDATE Tab SET spalte1=Ausdruck, spalte2=Ausdruck ,…. WHERE bedingung z.B. UPDATE T_Name SET entlassen=“Ja“ WHERE abteilung=“EDV“ ( lieber nicht ;-) Anfügen von Datensätzen INSERT INTO Zieltabelle (spalten) VALUES (werte) z.B. INSERT INTO t_kunden (Name,Ort,PLZ) VALUES („Meier“,Stralsund,18435) ____________________________________ Arbeitsblätter ACCESS Seite 10 5. VBA- Visual Basic for Applications einige wichtige Syntaxbeschreibungen VBA basiert auf der englischen Sprache. Deshalb wird in reellen Zahlen der Dezimalpunkt verwendet. Im Unterschied dazu wird in Makros deutsch „gesprochen“ und bei reellen Zahlen ein Komma als Dezimalstelle verwendet. 5.1 Variablenvereinbarungen - wird durch Option Explicit erzwungen; Menü Extras/Optionen, Registerkarte Editor; Option Variablendeklaration erforderlilch - DIM variablenname AS variablentyp z.B. DIM a as integer, b as Variant, x,y as double ’nur y wird als double deklariert, x als Variant man unterscheidet lokale und globale Variablen lokal: alle Variablen, die innerhalb einer Prozedur oder Funktion definiert sind,d.h. Variablen können in jeder Proz. oder Function beliebig neu definiert werden. global: 2 Arten: 1) Gelten innerhalb eines Moduls; hier erfolgt die Deklaration ganz oben im Modul außerhalb der Prozeduren 2) gelten für alle Module; hier erfolgt die Deklaration ganz oben in einem beliebigen Modul mit dem Schlüsselwort PUBLIC z.B. public a as Variant public const pi=3.14 ’Beachte den Punkt als Dezimalzeichen. 5.2 Deklaration der Datenbanken und Recordsets Deren Deklaration ist abhängig von den verwendeten Objektbibliotheken DAO oder ADO DAO Dim db as DataBase Dim rs as Recordset ADO Dim db as ADODB.Connection Dim rs as New ADODB.Recordset Set db=Currentdb Set rs=db.OpenRecordset(„Tabelle, Abfrage oder Set db=CurrentProject.Connection rs.Open((„Tabelle, Abfrage oder SQL“),db SQL“) 5.3 wichtige Grundstrukturen - Alternativen (Bedingungen) 1.Form: in einer Zeile If Bedingung Then Anweisung [Else Anweisung) 2. Form: IF-THEN-Block if Bedingung then Anweisungsblock ____________________________________ Arbeitsblätter ACCESS Seite 11 End If Oder if Bedingung then Anweisungsblock Else Anweisungsblock End if - Schleifen For…Next-Schleife: For Zähler=Start to Ende Anweisungen Next Zähler For i=1 to 10 s=s+1 next i While-Schleife: While Bedingung Anweisungen Wend ’Führe aus, solange die Bedingung wahr ist X=1 while x<11 debug.print x x=x+1 Wend Do While- Schleife Do While Bedingung Anweisungen Loop Do Until-Schleife Do Until Bedingung Anweisungen Loop ’Führe aus, bis Bedingung wahr X=1 Do until x<11 Debug.print x x=x+1 loop EXIT – zum vorzeitigen Verlassen der Schleifen oder Prozeduren Exit For Exit Do Zum Verlassen von Schleifen Exit Function Exit Sub Zum Verlassen von Prozeduren und Funktionen ____________________________________ Arbeitsblätter ACCESS Seite 12 Diese Schleife wird nicht ausgeführt. 5.4 Objekte und Bezüge auf Objekte Die Access-Objectbibliothek Application Forms Reports Controls Screen DoCmd Controls Screen – aktives Objekt DoCmd – ermöglicht die Ausführung von Makroaktionen in VBA-Programmen (englisch) Forms, Records- Auflistungen, Behälter, wo sich alle Formulare bzw. Berichte befinden Zugriff: ’ Trennzeichen ist ! Forms![Kunden] Benennung des Behälters Unterobjekt . ! Trennzeichen „ “ Oder „ “ . - eigene benutzerdefinierte Elemente werden durch vorstehendes „!“ benannt. - Vordefinierte Eigenschaften oder Methoden werden durch Punkt „ “ eingeleitet. Methoden und Eigenschaften von Objekten sind vordefiniert. Des halb erfolgt hier die Punktnotation. Objekt.Methode Objekt.Eigenschaft z.B. Forms!Kunden.SetFocus Forms!Kunden!Name.Visible Forms!Kunden!Name.Visible=0 Daten suchen Gesucht werden immer nur ganze Datensätze, d.h. der Datensatzzeiger kann innerhalb der Datei gesetzt werden. Ein Datensatz wird gelesen, dann ausgewertet. Zur Datensatznavigation stehen viele Methoden zur Verfügung: ____________________________________ Arbeitsblätter ACCESS Seite 13 rs - Recordset Find-Methoden: rs.Findfirst rs.findlast rs.findnext rs.findprevious Kriterien z.B. rs.Findfirst („[Name]=’Meier’ “) Diese Methoden sind meist in eine Schleife eingebunden, wenn eine Datei von Anfang bis Ende auf ein bestimmtes Kriterium durchsucht werden soll. Dateien mit Schleife durchsuchen: Rs.Nomatch ’ ist ein DS mit Find-Kriterium gefunden, ist NoMatch=true Anwendung in Schleife: rsFindFirst kriterium Do until rs.NoMatch ……. Rs.FindNext Kriterium Loop Move-Methoden: rs.MoveFirst rs.MoveNext rs.MoveLast Wird verwendet, um eine Datei satzweise von Anfang bis Ende zu durchsuchen Rs.Movefirst Do until rs.EOF …… rs.MoveNext loop ’EOF… End of File einige andere wichtige Methoden: rs.recordcount ’gibt die Anzahl der Datensätze zurück rs.close ’schließt die Datei rs.filter=“[Name]=’Meier’“ ’setzt Filter auf Datensatzelemente zugreifen: rs![Element] Name=rs![Name] debug.print rs![Artikelnummer] if rs![Name]=Name then…. ____________________________________ Arbeitsblätter ACCESS Seite 14 If rs![Name]=Forms![Kunden]![Name] then…. ____________________________________ Arbeitsblätter ACCESS Seite 15