View
214
Download
1
Category
Preview:
Citation preview
Regionales Rechenzentrum für Niedersachsen
VBA (Visual Basic for Application)
Verbindung zur Datenbank mit Hilfe von DAO
OpenOffice | 13.08.08 | Folie 2Access 2007 – VBA | 06.03.08 | Seite 2
Regionales Rechenzentrum für Niedersachsen
ADO (ActiveX Data Object)
... ermöglicht ein Zugriff auf Datenquellen unterschiedlichster Art und Herkunft.
... ist eine Datenbankschnittstelle, die eine Verbindung zu O(bject) L(inking) E(Embedding) Datenbanken herstellt.
... nicht für Access 97 geeignet. ... ist in Access 2000 Standard. Access 2007 nutzt die Version ADO 2.8.
OpenOffice | 13.08.08 | Folie 3Access 2007 – VBA | 06.03.08 | Seite 3
Regionales Rechenzentrum für Niedersachsen
DAO (Data Access Object)
... wird für die Programmierung und Manipulation von Access-Datenbanken und deren Objekte genutzt.
... wurde speziell als Datenbankschnittstelle für Access-Datenbanken und ODBC (Open Database Connectivity) entwickelt.
... ist ab Access 2003 Standard für die Verbindung von Datenbanken und VBA.
... wird bis Access 2003 als DAO 3.xx implementiert. Access 2007 nutzt die Neuentwicklung Microssoft Office 12.0 Access database engine object Library (acedato.dll). Die neue Schnittstelle enthält die älteren Versionen sowie neue Elemente wie Anlagen etc.
http://msdn.microsoft.com/en-us/library/bb244889.aspx
OpenOffice | 13.08.08 | Folie 4Access 2007 – VBA | 06.03.08 | Seite 4
Regionales Rechenzentrum für Niedersachsen
DAO einbinden
Klicken Sie auf das Menü Extras – Verweise im VBA-Editor. In einer ACCDB (Access 2007) ist Microsoft Office 12.0 Access database
engine Object Library standardmäßig eingebunden. Für eine MDB kann Microsoft DAO 3.6 Object Library eingebunden werden.
OpenOffice | 13.08.08 | Folie 5Access 2007 – VBA | 06.03.08 | Seite 5
Regionales Rechenzentrum für Niedersachsen
Objektmodell
DBEngine(Access-Application)
Workspaces(Benutzersitzung)
Workspace
Databases(Alle geöffneten Datenbanken)
OpenOffice | 13.08.08 | Folie 6Access 2007 – VBA | 06.03.08 | Seite 6
Regionales Rechenzentrum für Niedersachsen
Objektmodell
Databases(Alle geöffneten Datenbanken)
Database(Geöffnete Datenbank)
Recordsets(Alle geöffneten Datensätze)
Relations(Beziehungen in Access)
TableDefs(Alle Tabellendefinitionen)
Recordset(Datensatz)
Fields(Alle Datenfelder)
OpenOffice | 13.08.08 | Folie 7Access 2007 – VBA | 06.03.08 | Seite 7
Regionales Rechenzentrum für Niedersachsen
Arbeitsablauf
Die Arbeitsumgebung wird festgelegt. Die Datenbank wird geöffnet. Tabellen / Abfragen öffnen. Datensätze neu erstellen oder bearbeiten. Tabelle / Abfrage schließen. Datenbank schließen.
OpenOffice | 13.08.08 | Folie 8Access 2007 – VBA | 06.03.08 | Seite 8
Regionales Rechenzentrum für Niedersachsen
Standard-Arbeitsbereich
Beide Anweisungen geben ein Verweis auf die Standard-Arbeitsumgebung zurück.
DBEngine ... wird automatisch von der Jet-Engine erstellt. ... repräsentiert die Daten in einer Access-Datenbank. ... ist eine Eigenschaft der Access-Anwendung
Workspace ... repräsentiert eine Arbeitssitzung. Der Arbeitsbereich besteht aus dem Benutzernamen, dem Passwort sowie alle
dazugehörigen Datenbanken. Es können mehrere Arbeitssitzungen geöffnet werden.
Dim wsAccess As DAO.Workspace
Set wsAccess = DBEngine(0)Set wsAccess = DBEngine.Workspaces(0)
OpenOffice | 13.08.08 | Folie 9Access 2007 – VBA | 06.03.08 | Seite 9
Regionales Rechenzentrum für Niedersachsen
Neuen Arbeitsbereich
Dim wsAccess As DAO.WorkspaceDim strUser As StringDim strPasswort As String
strUser = "Admin"strPasswort = ""Set wsAccess = DBEngine.CreateWorkspace(
"database", strUser, strPasswort, dbUseJet)
Workspaces.Append wsAccess
Hier wird ein neuer Arbeitsbereich für einen bestimmten Benutzer
angelegt. Das Benutzerkonto muss vorhanden sein, andernfalls wird eine
Fehlermeldung ausgegeben.
Der neu erstellte Arbeitsbereich wird nicht automatisch der Auflistung hinzugefügt. Append fügt einen
Arbeitsbereich der Auflistung hinzu.
OpenOffice | 13.08.08 | Folie 10Access 2007 – VBA | 06.03.08 | Seite 10
Regionales Rechenzentrum für Niedersachsen
Zugriff auf Datenbanken
Database ... ist ein virtuelles Abbild der Datenbank. Zu einer Datenbank gehören Tabellen und Abfragen. Formulare, Berichte und
Module sind abhängig vom Datenbank-Managmentsystem und gehören nicht zur Datenbank.
DAO kann mehr als eine Datenbank gleichzeitig geöffnet haben. Alle Anweisungen greifen auf die aktuell geöffnete Datenbank in der
Standard-Arbeitsumgebung zu. Mit Hilfe dieser Anweisungen kann zum Beispiel von Excel auf eine
Access-Datenbank zugegriffen werden.
Dim dbs As DAO.Database Set dbs = DBEngine.Workspaces(0).Databases(0)Set dbs = DBEngine(0)(0)Set dbs = DBEngine.Databases(0)
OpenOffice | 13.08.08 | Folie 11Access 2007 – VBA | 06.03.08 | Seite 11
Regionales Rechenzentrum für Niedersachsen
Zugriff auf die aktuelle Datenbank
Am meisten wird die Methode CurrentDb() genutzt. Die Methode ... gibt einen Verweis auf die aktuell geöffnete Datenbank zurück. ... erzeugt eine Instanz der aktuellen Datenbank. ... greift immer auf die aktuelle Datenstruktur zurück. ... kann nur in einer Access-Anwendung genutzt werden.
Der Verweis auf die aktuelle Datenbank muss in einer Objektvariablen gespeichert werden. Der Verweis verliert nach dem Aufruf seine Gültigkeit. Der Verweis ist nur zum Zeitpunkt des Aufrufs existent.
Dim dbs As DAO.Database
Set dbs = CurrentDb()Set dbs = DBEngine(0)(0)
OpenOffice | 13.08.08 | Folie 12Access 2007 – VBA | 06.03.08 | Seite 12
Regionales Rechenzentrum für Niedersachsen
Externe Datenbank öffnen
Dim ws As WorkspaceDim dbs As DatabaseDim pfad As String
Set ws = DBEngine.Workspaces(0)
pfad = Application.CurrentProject.Path & "\work.mdb"
If Dir$(pfad ) <> "" Then
Set dbs = ws.OpenDatabase( pfad, _
False, _
False, _
"MS Access; pwd=passwort")
End If
Welche Datenbank wird geöffnet?
Öffnung in einer Mehrbenutzer-Umgebung
Zum Schreiben und Lesen öffnen.
Verbindungsinformationen.
OpenOffice | 13.08.08 | Folie 13Access 2007 – VBA | 06.03.08 | Seite 13
Regionales Rechenzentrum für Niedersachsen
Informationen zur Datenbank
Dim dbs As DAO.Database
Set dbs = CurrentDb()
Debug.Print db.Name ' Name der Datenbank
Debug.Print db.Version ' Genutzte Access-Version
' Pfad des aktuellen ProjektsDebug.Print Application.CurrentProject.Path
OpenOffice | 13.08.08 | Folie 14Access 2007 – VBA | 06.03.08 | Seite 14
Regionales Rechenzentrum für Niedersachsen
Properties (Eigenschaften) nutzen
Dim dbs As DAO.DatabaseDim obj As DAO.Property On Error GoTo FehlerSet dbs = CurrentDb() Debug.Print dbs.Properties!AppTitle Set dbs = Nothing
Exit SubMit Hilfe der Collection Properties können verschiedene Eigenschaften der Datenbank abgefragt und gesetzt
werden. Zum Beispiel AppTitle liefert den Titel der Anwendung,
StartupShowDBWindow blendet den Navigationsbereich aus,
StartupForm legt eine Startform fest.
OpenOffice | 13.08.08 | Folie 15Access 2007 – VBA | 06.03.08 | Seite 15
Regionales Rechenzentrum für Niedersachsen
Properties (Eigenschaften) erzeugen
Const conPropNotFoundError = 3270... Fehler: If Err.Number = conPropNotFoundError Then Set obj = dbs.CreateProperty("AppTitle", _
dbText, "DAO-Example") dbs.Properties.Append obj Else MsgBox "Fehler: " & Err.Description End If Resume Next
OpenOffice | 13.08.08 | Folie 16Access 2007 – VBA | 06.03.08 | Seite 16
Regionales Rechenzentrum für Niedersachsen
Erläuterung
CreateProperty ... erzeugt eine Eigenschaft einer Datenbank. Falls die Eigenschaft vorhanden
ist, wird ein Fehler geliefert. ... bekommt den Namen der Eigenschaft sowie deren Datentyp übergeben. Für
Zeichenfolgen wird der Typ dbText, für boolsche Werte dbBoolean und für Ganzzahlen dbInteger übergeben.
... bekommt als dritten Parameter einen Wert entsprechend des Datentyps übergeben.
. Append fügt der Auflistung Properties die neu erzeugte Eigenschaft hinzu.
OpenOffice | 13.08.08 | Folie 17Access 2007 – VBA | 06.03.08 | Seite 17
Regionales Rechenzentrum für Niedersachsen
Datenbank-Objekt zerstören
Die Verbindung zu der, in der Objektvariablen gespeicherten Datenbank wird geschlossen.
Um eine Nutzung eines ungültigen Verweise zu verhindern, wird der Verweis mit Hilfe von Nothing zerstört.
Dim dbs As DAO.Database
Set dbs = CurrentDb()
dbs.Close
Set dbs = Nothing
OpenOffice | 13.08.08 | Folie 18Access 2007 – VBA | 06.03.08 | Seite 18
Regionales Rechenzentrum für Niedersachsen
Recordset
... bildet eine Tabelle, eine Abfrage oder eine SQL-Anweisung ab. ... hat immer eine bestimmte Anzahl von Datensätzen. ... ermöglicht ein Zugriff auf die Datenfelder eines Datensatzes.
OpenOffice | 13.08.08 | Folie 19Access 2007 – VBA | 06.03.08 | Seite 19
Regionales Rechenzentrum für Niedersachsen
Öffnen einer Tabelle oder Abfrage
Voraussetzung: Eine Datenbank ist geöffnet. Es ist eine Variable vom Typ Database initialisiert.
varDatabase.OpenRecordset(Quelle [, Typ, Optionen, Sperren] ) ... erstellt ein virtuelles Abbild einer Tabelle oder Abfrage. Als Abfrage kann
eine SQL-Anweisung oder eine in der Datenbank vorhandene Abfrage genutzt werden.
... ermöglicht das Arbeiten mit Daten aus Tabellen oder Abfragen.
OpenOffice | 13.08.08 | Folie 20Access 2007 – VBA | 06.03.08 | Seite 20
Regionales Rechenzentrum für Niedersachsen
Beispiel
Dim dbs As DAO.DatabaseDim rsTbl As DAO.RecordsetDim rsQry As DAO.Recordset
Set dbs = CurrentDb()
Set rsTbl = db.OpenRecordset("tblWare", dbOpenTable)rsTbl.Close
Set rsQry = db.OpenRecordset("qryWareInfo", dbOpenDynaset)rsQry.Close
dbs.CloseSet dbs = Nothing
Es wird ein Verweis auf die Tabelle tblWare erzeugt.
Der Methode wird der Name der Tabelle sowie der Typ, auf den
verwiesen werden soll, übergeben. Standardmäßig wird auf eine Tabelle in der angegebenen
Datenbank verwiesen.
Es wird ein virtuelles Abbild einer Abfrage
erzeugt.
OpenOffice | 13.08.08 | Folie 21Access 2007 – VBA | 06.03.08 | Seite 21
Regionales Rechenzentrum für Niedersachsen
Typen des Recordsets
dbOpenTable ... greift auf eine Tabelle in der geöffneten Datenbank zu. Die Daten in der Tabelle können bearbeitet, aber nicht sortiert werden. ... wird für eine schnelle Suche in Tabellen genutzt.
dbOpenDynaset ... wird für Abfragen, verknüpfte Tabellen und SQL-Anweisungen genutzt. ... lädt einen eindeutigen Schlüssel für jeden Datensatz in den Speicher. Die
Daten werden bei Bedarf nachgeladen. Datensätze können hinzugefügt und gelöscht werden. Datensätze, die nicht auf einer 1:n-Beziehung beruhen, können verändert
werden. Die Daten können beliebig gefiltert und sortiert werden.
dbOpenSnapshot ... erstellt eine Kopie der Daten zu einem bestimmten Zeitpunkt. Die Daten können nicht bearbeitet werden.
OpenOffice | 13.08.08 | Folie 22Access 2007 – VBA | 06.03.08 | Seite 22
Regionales Rechenzentrum für Niedersachsen
Optionen zum Öffnen der Datensätze
Dim dbs As DAO.DatabaseDim rsTbl As DAO.RecordsetDim rsQry As DAO.Recordset
Set dbs = CurrentDb()
Set rsTbl = db.OpenRecordset("tblWare", dbOpenTable, dbReadOnly)
rsTbl.Close
Set rsQry = db.OpenRecordset("qryWareInfo", dbOpenDynaset,dbConsistent OR dbSeeChanges)
rsQry.Close
dbs.CloseSet dbs = Nothing
OpenOffice | 13.08.08 | Folie 23Access 2007 – VBA | 06.03.08 | Seite 23
Regionales Rechenzentrum für Niedersachsen
Mögliche Optionen
dbAppendOnly Daten können einem Dynaset hinzugefügt, aber nicht angezeigt werden.
dbReadOnly Änderungen sind an den Datensätzen nicht möglich.
Mehrbenutzer-Umgebung:dbDenyWrite Andere Nutzer können keine Datensätze
hinzufügen oder ändern.dbDenyRead Andere Nutzer können keine Datensätze in der
Tabelle lesen.dbConsistent In einem Dynaset oder Snapshot sind nur
konsistente Änderungen möglich.dbSeeChanges Auslösung eines Laufzeitfehlers, wenn ein
Benutzer Daten ändert, die ein anderer bearbeitet.
Die verschiedenen Optionen können durch eine OR-Verknüpfung miteinander verknüpft werden.
OpenOffice | 13.08.08 | Folie 24Access 2007 – VBA | 06.03.08 | Seite 24
Regionales Rechenzentrum für Niedersachsen
Datensätze sperren
Dim dbs As DAO.DatabaseDim rsTbl As DAO.RecordsetDim rsQry As DAO.Recordset
Set dbs = CurrentDb()
Set rsTbl = db.OpenRecordset("tblWare", dbOpenTable, , dbReadOnly)
rsTbl.Close
dbs.CloseSet dbs = Nothing
OpenOffice | 13.08.08 | Folie 25Access 2007 – VBA | 06.03.08 | Seite 25
Regionales Rechenzentrum für Niedersachsen
Sperren
dbReadOnly Änderungen an den Datensätzen sind nicht möglich. Die Datensätze können nur gelesen werden.
dbPessimistic Der Datensatz wird während der Bearbeitung für alle anderen Nutzer gesperrt. Ein lesender Zugriff ist aber möglich.
dbOptimistic Der Datensatz wird während der Aktualisierung gesperrt. Während der Bearbeitung ist der Datensatz nicht für andere Nutzer gesperrt.
... beeinflusst das Verhalten des Recordsets in einer Mehrbenutzerumgebung.
OpenOffice | 13.08.08 | Folie 26Access 2007 – VBA | 06.03.08 | Seite 26
Regionales Rechenzentrum für Niedersachsen
Datensätze basieren auf einen SQL-String
Dim dbs As DAO.DatabaseDim rs As DAO.RecordsetDim strSQL As String
strSQL = "SELECT titel FROM qryBuchInfo " strSQL = strSQL & "WHERE nachname LIKE 'Williams'"
Set dbs = CurrentDb()
Set rs = db.OpenRecordset(strSQL, dbOpenSnapshot)rs.Close
dbs.CloseSet dbs = Nothing
OpenOffice | 13.08.08 | Folie 27Access 2007 – VBA | 06.03.08 | Seite 27
Regionales Rechenzentrum für Niedersachsen
Ausflug zu SQL (Structured Query Language)
Strukturierte Abfragesprache für relationale Datenbanken. Datendefinition, -manipulation, -abfrage. ... definiert Kriterien, um nach Daten zu suchen. ... kann eine Menge von Datensätze automatisch aktualisieren oder
löschen. ... besteht aus sehr wenigen Kommandos, sehr vielen Schlüsselwörter
und einfachen Funktionen. In SQL sind keine Schleifen, bedingte Anweisungen oder die Nutzung von
Variablen implementiert. ... enden in der SQL-Ansicht der Abfrage mit einem Semikolon.
OpenOffice | 13.08.08 | Folie 28Access 2007 – VBA | 06.03.08 | Seite 28
Regionales Rechenzentrum für Niedersachsen
Werte in der Tabelle ansehen
Durch das Sternchen werden alle Datensätze aus einer Tabelle angezeigt. SELECT (Wähle aus, Selektierte) alle FROM (Wo?, von) tabelle.
SELECT * FROM tabellenname
OpenOffice | 13.08.08 | Folie 29Access 2007 – VBA | 06.03.08 | Seite 29
Regionales Rechenzentrum für Niedersachsen
Bestimmte Spalten auswählen
Das Sternchen kann durch Spaltennamen in der Tabelle ersetzt werden. Es werden nur die Daten in den angegebenen Spalten angezeigt. Die Spaltennamen
... werden durch ein Kommata getrennt. ... müssen in der angegebenen Tabelle vorkommen. Andernfalls wird ein
Fehler angezeigt. Die Reihenfolge der Spaltennamen spielt keine Rolle.
SELECT datenfeld1, datenfeld2, ... FROM tabellenname
OpenOffice | 13.08.08 | Folie 30Access 2007 – VBA | 06.03.08 | Seite 30
Regionales Rechenzentrum für Niedersachsen
Datensätze filtern
Es werden alle Datensätze angezeigt, die der Bedingung entsprechen. Die Bedingung oder das Kriterium für die Auswahl beginnt mit WHERE. Eine Bedingung kann sich folgendermaßen aufbauen:
feld Vergleichsoperator Wert Mehrere Bedingungen können miteinander verknüpft werden.
SELECT * FROM tabellenname WHERE bedingung;
OpenOffice | 13.08.08 | Folie 31Access 2007 – VBA | 06.03.08 | Seite 31
Regionales Rechenzentrum für Niedersachsen
Bedingungen
... sind Ausdrücke, die einen boolschen Wert zurückliefern. Ein boolscher Wert ist true(wahr) oder false(falsch, 0) ... vergleichen mit Hilfe von bestimmten Operatoren zwei Werte. ... sind zum Beispiel:
Wenn die Bestellmenge eine gewisse Höchstmenge überschreitet... Wenn der Kontostand dem Dispo entspricht... Wenn die Strecke A doppelt so lang ist wie Strecke B... Wenn die Warenmenge eine Mindestmenge unterschreitet...
OpenOffice | 13.08.08 | Folie 32Access 2007 – VBA | 06.03.08 | Seite 32
Regionales Rechenzentrum für Niedersachsen
Vergleichsoperatoren
Operator Beispiel = ist gleich< ist kleiner als<= ist kleiner gleich als> ist größer als>= ist größer gleich als<> ist ungleich
Rechenart(7 = 3 ) => False(7 < 3 ) => False(7 <= 3 ) => False(7 > 3 ) => True(7 >= 3 ) => True(7<> 3 ) =>True
SELECT artikel, preis, menge FROM lager WHERE artikel = 'Banane';
SELECT artikel, preis, menge FROM lager WHERE preis >= 2.53;
SELECT artikel, preis, menge FROM lager WHERE menge < 10;
OpenOffice | 13.08.08 | Folie 33Access 2007 – VBA | 06.03.08 | Seite 33
Regionales Rechenzentrum für Niedersachsen
Logische Operatoren
... oder relationale Operatoren. ... verknüpfen zwei oder mehr Bedingungen miteinander. Folgende Möglichkeiten sind vorhanden:
AND (Und, Konjunktion) ist nur wahr, wenn alle Bedingungen wahr sind. OR (Oder, Disjunktion) ist wahr, sobald eine der Bedingungen wahr ist. NOT (Negation) invertiert den boolschen Wert der Bedingung.
OpenOffice | 13.08.08 | Folie 34Access 2007 – VBA | 06.03.08 | Seite 34
Regionales Rechenzentrum für Niedersachsen
Logische Operatoren nutzen
SELECT artikel, preis, menge FROM lager WHERE artikel = 'Banane' AND preis >= 1.23;
SELECT artikel, preis, menge FROM lager WHERE (preis = 1.99) OR (preis = 2.89);
SELECT artikel, preis, menge FROM lager WHERE (menge > 5) AND (menge < 10);
Bedingunga b Not a a AND b a OR b
false false true false falsefalse true true false truetrue false false false truetrue true false true true
OpenOffice | 13.08.08 | Folie 35Access 2007 – VBA | 06.03.08 | Seite 35
Regionales Rechenzentrum für Niedersachsen
Wertebereiche und Listen nutzen
SELECT * FROM tabellenname WHERE datenfeld BETWEEN untergrenze AND obergrenze;
SELECT * FROM tabellenname WHERE NOT datenfeld BETWEEN untergr AND obergr;
SELECT * FROM tabellenname WHERE datenfeld IN (element1, element2, ...);
SELECT * FROM tabellenname WHERE datenfeld NOT IN (element1, element2, ...);
OpenOffice | 13.08.08 | Folie 36Access 2007 – VBA | 06.03.08 | Seite 36
Regionales Rechenzentrum für Niedersachsen
Undefinierte Werte
SELECT * FROM tabellenname WHERE datenfeld IS NULL;SELECT * FROM tabellenname WHERE NOT(datenfeld IS NULL);
SELECT * FROM tabellenname WHERE datenfeld = '';SELECT * FROM tabellenname WHERE NOT(datenfeld = '');
OpenOffice | 13.08.08 | Folie 37Access 2007 – VBA | 06.03.08 | Seite 37
Regionales Rechenzentrum für Niedersachsen
LIKE
... entspricht dem Gleichheitszeichen. ... kann nur für Textwerte genutzt werden. ... wird für Mustervergleiche genutzt. Im Suchmuster können folgende Platzhalter an beliebiger Position
vorkommen: Das Fragezeichen ersetzt ein Zeichen. Das Sternchen ersetzt eine beliebige Anzahl von Zeichen. Das Hash-Zeichen ersetzt eine beliebige Ziffer.
OpenOffice | 13.08.08 | Folie 38Access 2007 – VBA | 06.03.08 | Seite 38
Regionales Rechenzentrum für Niedersachsen
Beispiele
SELECT nachname, eintritt FROM mitarbeiterWHERE nachname LIKE 'M*';
SELECT nachname, eintritt FROM mitarbeiterWHERE nachname LIKE 'Me?er';
SELECT artikel, preis, menge FROM lieferbar WHERE artikel LIKE 'Banane?';
SELECT artikelnr, artikel FROM lieferbar WHERE artikelnr LIKE '456-*-78-?';
SELECT standort, plz, groesse menge FROM lager WHERE plz LIKE '38###';
OpenOffice | 13.08.08 | Folie 39Access 2007 – VBA | 06.03.08 | Seite 39
Regionales Rechenzentrum für Niedersachsen
Daten sortieren
Hier werden die Daten sortiert. ... ORDER BY datenfeld ASC.
ASC entspricht einer aufsteigenden Sortierung (A bis Z und 0 bis 9). ... ist die Standardsortierung.
... ORDER BY datenfeld DESC. DESC entspricht einer absteigenden Sortierung (Z bis A und 9 bis 0).
Es kann mit Hilfe von ORDER BY feld1, feld2 mehrere Felder sortiert werden. Die Daten werden zuerst nach feld1 sortiert und anschließend nach feld2.
SELECT * FROM tabellenname ORDER BY datenfeldSELECT artikel, preis, menge FROM lager
WHERE preis >= 1.23ORDER BY artikel;
OpenOffice | 13.08.08 | Folie 40Access 2007 – VBA | 06.03.08 | Seite 40
Regionales Rechenzentrum für Niedersachsen
Inner - Join - Verknüpfung
... oder Exklusionsverknüpfung, Equival-Join. ... verknüpft über ein oder mehrere gemeinsame Felder. ... sucht passend zu einem Fremdschlüssel in einer Detailtabelle einen
Primärschlüssel in einer Mastertabelle. Es wird eine 1 : n – Beziehung nachgebildet. Es werden nur die Datensätze angezeigt, deren verknüpfte Werte in
beiden Tabellen vorkommen. ... wird am häufigsten verwendet. Das Ergebnis ist ein Dynaset. Das Ergebnis dieser SQL-Anweisung kann
bearbeitet werden.
OpenOffice | 13.08.08 | Folie 41Access 2007 – VBA | 06.03.08 | Seite 41
Regionales Rechenzentrum für Niedersachsen
Beispiel
SELECT tabLand.land, tabKontinent.kontinent FROM tabKontinentINNER JOIN tabLand ON tabKontinent.idKontinent = tabLand.Kontinent
Welche Tabelle wird mit welcher Tabelle verknüpft? Auf welche Art werden die Tabellen verknüpft?
Welche Spalte aus der Mastertabelle wird mit welcher Spalte aus der Detailtabelle verknüpft? Die Werte beider Spalten muss übereinstimmen.
OpenOffice | 13.08.08 | Folie 42Access 2007 – VBA | 06.03.08 | Seite 42
Regionales Rechenzentrum für Niedersachsen
Grafische Darstellung
SüdafrikaAfrika
Antarktis
Arktis
IndienAsienChinaAsienSpanienEuropa
FrankreichEuropa
AfrikaAustralienAmerikaAsienEuropa
Inner Join
SüdafrikaAfrikaIndienAsienChinaAsienSpanienEuropa
FrankreichEuropa
tabLand
tabKontinent
OpenOffice | 13.08.08 | Folie 43Access 2007 – VBA | 06.03.08 | Seite 43
Regionales Rechenzentrum für Niedersachsen
Left – Join - Verknüpfung
Es werden alle Datensätze aus der linken Tabelle angezeigt. Es werden nur die Datensätze aus der rechten Tabelle angezeigt, die eine
Beziehung zu der linken Tabelle besitzen.
SELECT tabLand.land, tabKontinent.kontinent FROM tabKontinent LEFT JOIN tabLand ON tabKontinent.idKontinent = tabLand.Kontinent
OpenOffice | 13.08.08 | Folie 44Access 2007 – VBA | 06.03.08 | Seite 44
Regionales Rechenzentrum für Niedersachsen
Grafische Darstellung
SüdafrikaAfrika
Amerika
Australien
IndienAsienChinaAsienSpanienEuropa
FrankreichEuropaSüdafrikaAfrika
Antarktis
Arktis
IndienAsienChinaAsienSpanienEuropa
FrankreichEuropa
AfrikaAustralienAmerikaAsienEuropa
Left Join
tabKontinent
tabLand
OpenOffice | 13.08.08 | Folie 45Access 2007 – VBA | 06.03.08 | Seite 45
Regionales Rechenzentrum für Niedersachsen
Right – Join - Verknüpfung
Es werden alle Datensätze aus der rechten Tabelle angezeigt. Es werden nur die Datensätze aus der linken Tabelle angezeigt, die eine
Beziehung zu der rechten Tabelle besitzen.
SELECT tabLand.land, tabKontinent..kontinent FROM tabKontinent RIGHT JOIN tabLand ON tabKontinent.idKontinent = tabLand.Kontinent
OpenOffice | 13.08.08 | Folie 46Access 2007 – VBA | 06.03.08 | Seite 46
Regionales Rechenzentrum für Niedersachsen
Right- Join - Verknüpfung
SüdafrikaAfrika
Antarktis
Arktis
IndienAsienChinaAsienSpanienEuropa
FrankreichEuropaSüdafrikaAfrika
Antarktis
Arktis
IndienAsienChinaAsienSpanienEuropa
FrankreichEuropa
AfrikaAustralienAmerikaAsienEuropa
Right Join
tabKontinent
tabLand
OpenOffice | 13.08.08 | Folie 47Access 2007 – VBA | 06.03.08 | Seite 47
Regionales Rechenzentrum für Niedersachsen
Sind Datensätze vorhanden?
Set dbs = CurrentDb()
Set rs = db.OpenRecordset(strSQL, dbOpenSnapshot)
If (rs.RecordCount = 0) ThenMsgBox("Es sind keine Datensätze vorhanden")
ElseMsgBox("Es sind x Datensätze vorhanden")
End If
rs.Close
OpenOffice | 13.08.08 | Folie 48Access 2007 – VBA | 06.03.08 | Seite 48
Regionales Rechenzentrum für Niedersachsen
RecordCount
... liefert die Anzahl der gelesenen Datensätze. Quelle: Tabelle (dbTable):
Die Gesamtzahl aller Datensätze werden angezeigt. Wie viele Datensätze besitzt das Recordset?
Quelle: dbDynaset oder dbSnapshot oder Abfragen: Die Gesamtzahl der Datensätze ist immer eins. Es wird ein falsches Ergebnis geliefert.
OpenOffice | 13.08.08 | Folie 49Access 2007 – VBA | 06.03.08 | Seite 49
Regionales Rechenzentrum für Niedersachsen
Besser...
Set dbs = CurrentDb()
Set rs = db.OpenRecordset(strSQL, dbOpenSnapshot)
' Wenn der Anfang und das Ende der Datensätze gleich istIf (rs.BOF AND rs.EOF) Then
MsgBox("Es sind keine Datensätze vorhanden")Else
rs.Requery ' Datensätze aktualisieren' nur Dynaset, Snapshot
rs.MoveLast ' Gehe zum letzten DatensatzMsgBox "Es sind " & rs.Recordcount & _
" Datensätze vorhanden"End If
rs.Close
OpenOffice | 13.08.08 | Folie 50Access 2007 – VBA | 06.03.08 | Seite 50
Regionales Rechenzentrum für Niedersachsen
BOF und EOF
BOF (Begin of)
Datensatz 01
Datensatz 02
Datensatz 03
Datensatz 04
Datensatz 05
EOF (End of)
OpenOffice | 13.08.08 | Folie 51Access 2007 – VBA | 06.03.08 | Seite 51
Regionales Rechenzentrum für Niedersachsen
In Datensätzen navigieren
rs.MoveNext
rs.MovePreviousrs.MoveFirst
rs.MoveLast
BOF (Begin of)
Datensatz 01
Datensatz 02
Aktueller Datensatz
Datensatz 04
Datensatz 05
EOF (End of)
OpenOffice | 13.08.08 | Folie 52Access 2007 – VBA | 06.03.08 | Seite 52
Regionales Rechenzentrum für Niedersachsen
In Datensätzen navigieren
rs.Move(1)
rs.Move(-1)
rs.AbsolutePosition = 3rs.Move(rs.AbsolutePosition - 2)
BOF (Begin of)
Datensatz 01
Datensatz 02
Aktueller Datensatz
Datensatz 04
Datensatz 05
EOF (End of)
OpenOffice | 13.08.08 | Folie 53Access 2007 – VBA | 06.03.08 | Seite 53
Regionales Rechenzentrum für Niedersachsen
AbsolutePosition
... kann nur beim Typ Dynaset oder Snapshot genutzt werden. ... positioniert den Datensatz-Zeiger neu oder gibt die aktuelle Position
relativ zu 0 zurück. Der erste Datensatz hat den Index 0. ... kann keinen Wert größer als (.RecordCount – 1) annehmen.
OpenOffice | 13.08.08 | Folie 54Access 2007 – VBA | 06.03.08 | Seite 54
Regionales Rechenzentrum für Niedersachsen
Datensätze von vorn nach hinten durchlaufen
Set dbs = CurrentDb()
Set rs = db.OpenRecordset(strSQL, dbOpenSnapshot)
If (rs.BOF AND rs.EOF) ThenMsgBox("Es sind keine Datensätze vorhanden")
Elsers.Requery
Do While Not rs.EOFrs.MoveNext
LoopEnd If
rs.Close
OpenOffice | 13.08.08 | Folie 55Access 2007 – VBA | 06.03.08 | Seite 55
Regionales Rechenzentrum für Niedersachsen
Datensätze von hinten nach vorn durchlaufen
Set dbs = CurrentDb()
Set rs = db.OpenRecordset(strSQL, dbOpenSnapshot)
If (rs.BOF AND rs.EOF) ThenMsgBox("Es sind keine Datensätze vorhanden")
Elsers.Requeryrs.MoveLast
Do While Not rs.BOFrs.MovePrevious
LoopEnd If
rs.Close
OpenOffice | 13.08.08 | Folie 56Access 2007 – VBA | 06.03.08 | Seite 56
Regionales Rechenzentrum für Niedersachsen
Daten eines Datensatzes
...Set rs = db.OpenRecordset("SELECT * FROM tblMitarbeiter")
Debug.Print rs!nachname
Debug.Print rs![nachname]
Debug.Print rs("nachname")
recordset!Feldname oder recordset![Feld Name] Das Ausrufezeichen trennt Objektvariablen von benutzerdefinierten
Bezeichnungen. Wenn die benutzerdefinierte Bezeichnung Leerzeichen oder Sonderzeichen
wie Umlaute etc. enthält, muss der Name mit Hilfe von eckigen Klammern zusammengefasst werden.
recordset("Feldname") Hier wird der Feldname als Index einer Auflistung genutzt.
OpenOffice | 13.08.08 | Folie 57Access 2007 – VBA | 06.03.08 | Seite 57
Regionales Rechenzentrum für Niedersachsen
Daten eines Datensatzes
...Set rs = db.OpenRecordset("SELECT * FROM tblMitarbeiter")
Debug.Print rs.Fields!nachname
Debug.Print rs.Fields("nachname")
Debug.Print rs.Fields(1)
Die Auflistung Fields enthält alle Spalten eines Recordsets. Die Anzahl der Spalten ist abhängig von der angegebenen Quelle
(Tabelle, Abfrage oder SQL_Anweisung). Es sollte immer der Name als Index für die Elemente der Auflistung
genutzt werden.
OpenOffice | 13.08.08 | Folie 58Access 2007 – VBA | 06.03.08 | Seite 58
Regionales Rechenzentrum für Niedersachsen
Neue Datensätze hinzufügen
Set rs = db.OpenRecordset("tblKunde")
With rs
.AddNew
.Fields(" firma") = "Molkerei Kuh Gut"
.Fields("mail") = "sekretariat@molkereiKuhGut.de"
.Update
.Move 0, rs.LastModifiedEnd rs
OpenOffice | 13.08.08 | Folie 59Access 2007 – VBA | 06.03.08 | Seite 59
Regionales Rechenzentrum für Niedersachsen
AddNew
Nach dieser Anweisung können neue Datensätze eingegeben werden. Für jedes Feld wird ein Wert eingegeben. Der Datensatz wird nicht direkt in der Tabelle gespeichert, sondern in
einem Zwischenspeicher. Mit Hilfe der Eigenschaft .Updatable kann überprüft werden, ob der
Datensatz verändert werden kann oder nicht.
OpenOffice | 13.08.08 | Folie 60Access 2007 – VBA | 06.03.08 | Seite 60
Regionales Rechenzentrum für Niedersachsen
Update
... übernimmt Änderungen in die Datenbank. ... speichert die Daten aus dem Puffer im Recordset. ... führt eine Typüberprüfung der Daten durch. ... überprüft die Gültigkeit der Daten.
OpenOffice | 13.08.08 | Folie 61Access 2007 – VBA | 06.03.08 | Seite 61
Regionales Rechenzentrum für Niedersachsen
Daten ändern
With rsIf rs.Updatable Then
.MoveFirst
Do While .EOF = FalseIf (.Fields("ort") Like "Braunschweig") Then
.Edit .Fields("plz") = "38100" .Update
End If
.MoveNext Loop
End IfEnd With
OpenOffice | 13.08.08 | Folie 62Access 2007 – VBA | 06.03.08 | Seite 62
Regionales Rechenzentrum für Niedersachsen
Daten löschen
Set rs = db.OpenRecordset("SELECT plz, ort FROM tblKunde")
With rs.MoveFirst
Do While .EOF = False
If (.Fields("ort") Like "Braunschweig") Then
.Delete End If
.MoveNextLoop
End With
Nach der Löschung wird der Datensatzzeiger nicht
automatisch neu gesetzt. Er zeigt auf den gelöschten Datensatz im Speicher.
OpenOffice | 13.08.08 | Folie 63Access 2007 – VBA | 06.03.08 | Seite 63
Regionales Rechenzentrum für Niedersachsen
Bestimmte Anzahl von Zeilen...
Dim daten As VariantDim anzahlRs As Integer
...
Set rs = db.OpenRecordset("SELECT * FROM tblMitarbeiter")
With rs.Requery.MoveLastanzahlRs = .RecordCount
.MoveFirstdaten = .GetRows(anzahl)
End With
OpenOffice | 13.08.08 | Folie 64Access 2007 – VBA | 06.03.08 | Seite 64
Regionales Rechenzentrum für Niedersachsen
Erläuterung
Die Methode GetRows ... füllt ein Array vom Datentyp Variant mit einer bestimmten Anzahl von
Datensätzen. ... verschiebt den Datensatzzeiger um die angegebene Anzahl von
Datensätzen automatisch. Wenn weniger Datensätze als angegeben vorhanden sind, wird ein
Laufzeitfehler angezeigt.
OpenOffice | 13.08.08 | Folie 65Access 2007 – VBA | 06.03.08 | Seite 65
Regionales Rechenzentrum für Niedersachsen
Lesezeichen nutzen
Dim varLesezeichen As Variant
...
rs.Requery rs.MoveLast rs.MoveFirst rs.AbsolutePosition = rs.RecordCount \ 2 varLesezeichen = rs.Bookmark Debug.Print "Aktuell: "& rs.AbsolutePosition rs.MoveLast Debug.Print "Aktuell: " & rs.AbsolutePosition rs.Bookmark = varLesezeichen Debug.Print "Aktuell: " & rs.AbsolutePosition
OpenOffice | 13.08.08 | Folie 66Access 2007 – VBA | 06.03.08 | Seite 66
Regionales Rechenzentrum für Niedersachsen
Hinweise
rs.Bookmark ist eine Lesezeichen auf den aktuellen Datensatz. lesezeichen = rs.Bookmark speichert die Position des aktuellen
Datensatzes. rs.Bookmark = lesezeichen setzt den Datensatzzeiger auf den
Datensatz, deren Position in der Variable gespeichert ist. rs.Bookmark = rs.LastModified positioniert den Datensatzzeiger
auf den zuletzt hinzugefügten oder geänderten Datensatz. rs.Bookmarkable fragt ab, ob Lesezeichen gesetzt werden können.
OpenOffice | 13.08.08 | Folie 67Access 2007 – VBA | 06.03.08 | Seite 67
Regionales Rechenzentrum für Niedersachsen
Datensatz eines Formulars nutzen
' Klon eines Datensatzes in einem Formular erzeugenSet rs = Me.RecordsetClone
' Einen bestimmten Datensatz suchenrs.FindFirst "[nachname] = '" & strName & "'"
' Falls der Datensatz gefunden wurdeIf Not(rs.NoMatch) Then
' wird das Lesezeichen des Formulars gesetztMe.Bookmark = rs.Bookmark
End If
OpenOffice | 13.08.08 | Folie 68Access 2007 – VBA | 06.03.08 | Seite 68
Regionales Rechenzentrum für Niedersachsen
Hinweise zu RecordsetClone
Es wird eine Kopie des Recordsets eines bestimmten Formulars erzeugt. Kopie und Original arbeiten mit dem gleichen Objekt. Wenn das Original zerstört wird, kann nicht mit der Kopie gearbeitet
werden.
OpenOffice | 13.08.08 | Folie 69Access 2007 – VBA | 06.03.08 | Seite 69
Regionales Rechenzentrum für Niedersachsen
Index einer Tabelle durchsuchen
Dim dbs As DAO.DatabaseDim rsTbl As DAO.RecordsetDim rsQry As DAO.Recordset
Set dbs = CurrentDb()
Set rsTbl = db.OpenRecordset("tblWare", dbOpenTable)
' Ein Index muss gesetzt werdenrsTbl.Index = "IDArtikel"
' Es wird nach einem bestimmten Index gesucht...rsTbl.Seek "=", 125
rsTbl.Close
OpenOffice | 13.08.08 | Folie 70Access 2007 – VBA | 06.03.08 | Seite 70
Regionales Rechenzentrum für Niedersachsen
Hinweise
In einer Tabelle kann nur mit der Methode .Seek gesucht werden. Voraussetzung: In der Tabelle ist ein Index definiert. Der Primärschlüssel
wird zum Beispiel immer indexiert. Folgende Parameter wird der Methode übergeben:
Ein Vergleichsoperator als String. Ein Suchmuster.
Falls in mehreren Indexfeldern gleichzeitig gesucht werden soll, wird für jedes Feld ein Suchmuster, getrennt durch Kommata angegeben.
OpenOffice | 13.08.08 | Folie 71Access 2007 – VBA | 06.03.08 | Seite 71
Regionales Rechenzentrum für Niedersachsen
Datensätze durchsuchen
Dim dbs As DAO.DatabaseDim rs As DAO.RecordsetDim strSQL As String
strSQL = "SELECT titel FROM qryBuchInfo" Set dbs = CurrentDb()
Set rs = db.OpenRecordset(strSQL, dbOpenDynaset)
rs.FindFirst "[nachname] LIKE 'W*'"
If rs.NoMatch ThenMsgBox "Keine Daten vorhanden"
End If
rs.Close
OpenOffice | 13.08.08 | Folie 72Access 2007 – VBA | 06.03.08 | Seite 72
Regionales Rechenzentrum für Niedersachsen
Möglichkeiten
.FindFirst sucht den ersten übereinstimmenden Datensatz. .FindLast sucht den ersten übereinstimmenden Datensatz. .FindPrevious sucht den vorherigen übereinstimmenden Datensatz ab
dem aktuellen Datensatz. .FindNext sucht den nächsten übereinstimmenden Datensatz ab dem
aktuellen Datensatz.
Recommended