Language: Deutsch English















Last Update: 2017 - 07 - 09





MS Access

Inhalt

  • Transaktionen in Microsoft Access und VBA verwenden
  • Alle Spalten einer Datenbank
  • Autowert-Feld per VBA-Code erstellen
  • Alle Frontends einer Multiuser-DB schließen
  • Informationen über Datenbankobjekte auslesen
  • Prüfen ob ein Tabellenfeld existiert
  • ADO vs. DAO in Access
  • 7 Gründe für Quellcodeverwaltung bei der Microsoft Access Entwicklung
  • Darstellung von einer 1:N-Beziehung mit einer Auswahlabfrage
  • Werte aus mehreren Datensätzen einer Spalte als kommagetrennten Text ausgeben
  • Binärdaten sortieren und indizieren
  • Natürliche Zahlensortierung mit VBA
  • Daten extern bearbeiten lassen
  • In einem berechnetem Tabellenfeld das Quartal aus einem Datum ermitteln
  • Datenbanken auf OneDrive, DropBox oder Offline-Ordnern
  • Berechnete Felder in Abfragen
  • Access AccDE (MDE / ADE) Datei mit Code erzeugen (VBA/VB-Script)
  • Access SQL - Select Top X Datensätze – Ohne Bindungen
  • Access/VBA - Tutorial zur Ereignisbehandlung (Teil 1) - Erste Schritte für Anfänger
  • Feststellen ob ein Access Formular als Dialog geöffnet ist



  • Transaktionen in Microsoft Access und VBA verwenden

    Man kann Transaktionen verwenden um mehrere Aktionsabfragen in einer Arbeitseinheit zusammenzusfassen. Hier habe ich beschrieben, wie man Transaktionen in Microsoft Access mit VBA nutzen kann.



    Zurück zur Übersicht

    Alle Spalten einer Datenbank

    Die Funktion allColumns liest die Namen aller Spalten aller Tabellen (inklusive der Systemtabellem) der aktuellen Datenbank aus und schreibt sie in das Direktfenster.


    Sub allColumns()
    
        Dim x As Integer
        Dim y As Integer
        Dim db As DAO.Database
    
        Set db = CurrentDb
    
        For x = 0 To db.TableDefs.Count - 1
            Debug.Print db.TableDefs(x).name
            For y = 0 To CurrentDb.TableDefs(x).Fields.Count - 1
                Debug.Print CurrentDb.TableDefs(x).Fields(y).name
            Next
        Next
    
        db.Close
        Set db = Nothing
    
    End Sub
    


    Zurück zur Übersicht

    Autowert-Feld per VBA-Code erstellen

    Gelegentlich ist es erforderlich eine Tabelle per VBA zu erstellen. Eine Möglichkeit, neben einen DDL-SQL-Statement, ist die Verwendung der DAO-Objektbibliothek. Über deren TableDef Objekt geht das eigentlich sehr einfach.

    Das einzige Problem dabei ist eigentlich die Erstellung eines Autowert-Feldes. Es gib keine spezifische Datentyp Konstante im DataTypeEnum für einen Autowert.

    Numerischer Autowert (Long Integer)

    Wenn man ein normales Long Integer Autowert Feld erstellen möchte, erstellt man ein Feld vom Typ dbLong und setzt anschließen das dbAutoIncrField Attribut für dieses Feld.

    Das folgende Beispiel zeigt, wie man mit DAO ein solches Autowert-Feld an eine bestehende Tabelle anfügen kann.

    Sub createAutoIncrField() Dim td As DAO.TableDef Dim db As DAO.Database Dim fld As DAO.Field Set db = CurrentDb Set td = db.TableDefs("tblDeineTabelle") Set fld = td.CreateField("AutoWertFeld", dbLong) fld.Attributes = fld.Attributes Or dbAutoIncrField td.Fields.Append fld End Sub

    GUID Autowert (Replikations-ID)

    Über den Tabellenentwurf kann man manuell aber nicht nur ein Feld vom Type Long Integer als Autowert erstellen, sondern auch eines mit dem Typ Replikations-ID.

    Eine Replikations-ID ist eine GUID (Globally Unique Identifier), eine 128 Bit Zahl, die üblicherweise in hexadezimal Notation dargestellt wird. Beispiel: 6706EF5C-C09E-48E4-A710-DA100C5B1674.

    Üblicherweise werden GUIDs zufällig erzeugt, wobei der verwendete Algorithmus dafür sorgen sollte, dass es außerordentlich unwahrscheinlich ist, dass jemals zwei identische GUIDs generiert werden.

    Mit GUIDs tut sich Access ein wenig schwer, daher sollte man sich gut überlegen, ob man ein solches Feld wirklich verwenden möchte.

    Ein GUID-Feld lässt sich per VBA-Code und DAO nicht mit dem exakt gleichen Resultat erzeugen, wie über die Oberfläche. Allerdings kommt man sehr nah heran.

    Es muss über ein TableDef-Objekt ein neues Feld vom Typ dbGUID erzeugt werden. Diesen neuen Feld weißt man dann die Funktion GenGUID als Standardwert zu. GenGUID ist (vermutlich) eine Pseudo-Funktion, die nur intern der Jet-/ACE-Datenbankengine bekannt ist, und auch nur für genau diesen Zweck verwendet werden kann. Ein direkter Aufruf von GenGUID ist nicht möglich.

    Hier ist der Code um eine solches Feld zu erzeugen:

    Public Sub CreateGUIDAutoNumberColumn() Dim td As DAO.TableDef Dim db As DAO.Database Dim fi As DAO.Field Set db = CurrentDb Set td = db.TableDefs("tblDeineTabelle") Set fi = td.CreateField("DeineGUIDSpalte", dbGUID) fi.DefaultValue = "GenGUID()" td.Fields.Append fi Set td = Nothing Set db = Nothing End Sub

    Das Ergebnis ist nicht exakt identisch, mit dem der händischen Tabellenerstellung. Anstelle von (New) in neuen Datensätzen wird #Name? angezeigt und in der Entwurfsansicht ist das Feld nicht explizit als Autowert deklariert, sondern als Zahl.

    Access Datenblatt, Vergleich GUID Spalten Access Tabellenentwurf, Vergleich GUID Spalten

    In der Funktionalität konnte ich jedoch keinerlei Unterschiede erkennen.



    Zurück zur Übersicht

    Alle Frontends einer Multiuser-DB schließen

    Zur Wartung oder zur Datensicherung ist es erforderlich alle geöffneten Frontends einer Datenbank zu schließen. Oft machen einem die User zusätzliche Arbeit indem sie nach Feierabend die Datenbank geöffnet lassen.

    Dafür habe ich folgende Lösung entworfen. In der Datenbank (Backend) muss eine zusätzliche Tablle (tblShutdown) angelegt werden, die nur das Ja/Nein-Feld shutdown enthält. Diese Tabelle kann man dann in ein Administrationsfrontend einbinden und von dort über das Setzen des Ja/Nein Wertes in dem einzigen Datensatz das globale Schließen der Frontends einleiten.

    Im Frontend muss ein Timer laufen z.B. in einem unsichtbaren Formular, der in gewissen Abständen die Sub closeDB ausführt.

    Probleme kann es geben, wenn in einem der Frontends der aktuelle Datensatz eine unvollständige Eingabe ist, die nicht gespeichert werden kann. Diese Situation sollte man unbedingt vor Einsatz der Funktion bedenken.

    Sub closeDB()
    
        Dim rs As DAO.Recordset
        Set rs = CurrentDb.OpenRecordset _
                       ("SELECT shutdown FROM tblShutdown")
    
        If rs!shutdown = True Then
           rs.Close
           Application.CloseCurrentDatabase
        End If
    
        rs.Close
    
    End Sub
    


    Zurück zur Übersicht

    Informationen über Datenbankobjekte auslesen

    Wenn man Informationen, wie Erstellungsdatum, Datum der letzten Änderung oder die Herkunft verknüpfter Tabellen, über Objekte in einer Datenbank benötigt, kann man die Systemtabelle MSysObjects auslesen.

    Dazu muss man den User, der diese Operation durchführen soll, die Leseberechtigung auf die Tabelle Msysobject erteilen. Selbst der Access-Benutzer 'Administrator' hat per Default keine ausreichenden Rechte um diese Tabelle zu lesen.

    Diese Methode um Informationen über die Tabellen in einer Access-Datenbank zu erhalten, ist besonders dann Nützlich, wenn man Access nur als Backend für eine Applikation verwendet, die ausschließlich über SQL auf die Access-DB zugreifen soll.

    Das folgende SQL-Beispiel ermittelt die Namen und die Zeitpunkte der letzten Änderung aller direkt in der Datenbank gespeicherten Tabellen.

    
        SELECT Name, DateUpdate
          FROM MSysObjects
         WHERE Type = 1 and Flags = 0;
    
    


    Zurück zur Übersicht

    Prüfen ob ein Tabellenfeld existiert

    Gelegentlich möchte man feststellen, ob eine bestimmte Spalte in einer Tabelle existiert. Leider gibt es in der DAO Bibliothek keine fertige Funktion, um das zu überprüfen.

    Die folgende Funktion versucht einfach, das gesuchte Feld einer DAO.Field-Variablen zuzuweisen. Wenn das nicht funktioniert, weil das Feld nicht existiert, wird ein Fehler ausgelöst und die Funktion verlassen. Ansonsten wird nur noch der Rückgabewert der Funktion auf True gesetzt bevor die Funktion verlassen wird. - Quick'n'Dirty, aber es erfüllt den Zweck.

    Public Function FieldExists(tableName As String, fieldName As String) As Boolean On Error GoTo fieldExists_Exit Dim fld As dao.Field FieldExists = False Set fld = CurrentDb.TableDefs(tableName).Fields(fieldName) FieldExists = True fieldExists_Exit: Set fld = Nothing End Function

    Diese Funktion habe ich ursprünglich vor mehr als 10 Jahren hier auf der Webseite veröffentlicht und ich verwende sie im Rahmen meiner eigenen Standard-Bibliothek in allen meinen Projekten ohne ein Problem.

    Gerade habe ich diesen kleinen Artikel in das Englische übersetzt und konnte dabei meine Aversion gegen das bewusste Auslösen von Laufzeitfehlern im normalen Programmablauf nicht unterdrücken.

    Also habe ich eine neue Implementierung der Funktion geschrieben, die in einer Schleife durch alle Felder der Tabelle läuft und aus der Schleife aussteigt, wenn das gesuchte Feld gefunden wird. Hier ist der Code dieser Implementierung.

    Public Function FieldExists(tableName As String, fieldName As String) As Boolean Dim db As dao.Database Dim td As dao.TableDef Dim fld As dao.Field Dim i As Long FieldExists = False Set db = CurrentDb Set td = db.TableDefs(tableName) For i = 0 To td.Fields.Count - 1 If td.Fields(i).Name = fieldName Then FieldExists = True Exit For End If Next End Function

    Dieser Code gefällt mir wesentlich besser. – Aber jetzt bin ich neugierig ob es zwischen den beiden Implementierungen einen Unterschied bzgl. ihrer Performance gibt. Ich vermute, dass die Variante, die in der Schleife durch die Felder läuft, wesentlich langsamer ist, wenn das gesuchte Feld nicht in der Tabelle existiert und daher alle Felder durchlaufen werden müssen.

    Also habe ich ein kleines Experiment zum Performancevergleich unternommen. Ich habe die beiden Varianten der FieldExists-Funktion in einer Schleife aufgerufen, um in einer Tabelle mit 60 Feldern nach einem nicht vorhandenen Feld zu suchen. – Die überraschende Erkenntnis ist, dass es in Hinsicht auf die Performance kaum einen Unterschied zwischen den beiden verschiedenen Varianten gibt. Ich musste die Anzahl der Schleifendurchläufe auf 100.000 erhöhen, um überhaupt einen Unterschied messen zu können. Erst nachdem ich auf 500.000 Schleifendurchläufe erhöht habe, ist der Unterschied wirklich klar messbar und meine These bestätigt.

    Variante 1 (mit direktem Zugriff auf das Feld und der abgefangenem Fehler) benötigt 2 Sekunden bei 500.000 Aufrufen. Variante 2 (mit der Schleife durch die Felder) benötigt dagegen 11 Sekunden.

    Das Fazit ist also, dass es zwar einen Performanceunterschied zwischen den beiden Implementierungen gibt, aber dieser ist so vernachlässigbar gering in allen mir vorstellbaren Einsatzszenarien der Funktion. Also verwende einfach die Variante, die dir persönlich besser gefällt.



    Zurück zur Übersicht

    ADO vs. DAO in Access

    In den Newsgroups wird gelegentlich danach gefragt, für welche Datenzugriffsstechnologie man sich zu Beginn der Entwicklung einer neuen Access-Anwendung entscheiden sollte. Dazu möchte ich hier mal meine Gedanken zusammenfassen, wobei ich hier nicht so sehr die harten "beweisbaren" Fakten auflisten möchte, sondern mich eher auf etwas schwammige, aber doch auf praktischen Erfahrungen basierende, persönlichen Einschätzungen konzentriere.

    Die Argumente, die gegen DAO häufiger vorgebracht werden, sind etwa folgende: "DAO ist veraltet!", "DAO wird nicht mehr weiterentwickelt.", "ADO bietet einen größeren Funktionsumfang.". Diese Argumente sind entweder falsch oder aus meiner Sicht nicht wirklich relevant.

    Richtig ist, DAO wird nicht mehr weiterentwickelt, aber das ist kein Argument, dass bei der Entwicklung einer reinen Access-Lösung in's Gewicht fällt. DAO ist für Access "Feature Complete" d.h. fast alle Funktionen, die die JET-Engine zur Zeit bietet (mit Ausnahme einiger SQL92 Erweiterungen in JET 4.0) sind mit DAO nutzbar, wichtige neue Funktionen, bei denen das dann anders wäre, sind für due JET-Engine in absehbarer Zeit nicht zu erwarten. Also kann das kaum als Nachteil gewertet werden. Ganz im Gegenteil, DAO hat schon seit geraumer Zeit einen sehr stabilen Zustand erreicht, ist meines Ermessens frei von gravierenden Fehlern und da keine elementaren Änderungen und Erweiterungen an DAO zu erwarten sind, kann man guten Gewissens hoffen, dass dies auch zukünftig so bleiben wird.

    DAO ist schwerpunktmäßig für den Zugriff auf Jet-Datenbanken ausgerichtet und optimiert. Beim Einsatz in einer reinen Access-Umgebung ist DAO daher performanter und für viele Einsatzbereiche auch deutlich komfortabler zu handhaben. Was die Performance im Vergleich zu ADO angeht, wollen manche Teilnehmer der Newsgroups einen Unterschied um einen Faktor größer als 10 zugunsten von DAO gemessen haben. Solche Aussagen sind meiner Meinung zwar nicht grundsätzlich falsch, jedoch ist ein so deutlicher Unterschied nur in speziellen, meist für diesen Zweck konstruierten, Szenarien messbar. Fakt bleibt jedoch; DAO ist beim Zugriff auf Access-Datenbanken schneller als ADO.

    Noch eine weitere Tatsache, die eher für die Verwendung von DAO bei der Entwicklung von reinen Access-Anwendungen spricht. Mit der Einführung von Access 2000 hat Microsoft als Datenzugriffsbibliothek ADO anstelle von DAO voreingestellt. Bei Access 2002 (XP) war dies noch genauso, aber mit Access 2003 konnte sich auch Microsoft nicht mehr der Tatsache verschließen, dass sich ADO, mit gutem Grund, bei den Access-Entwicklern nicht durchgesetzt hat, und hat wieder DAO als Standard-Datenzugriffsbibliothek voreingestellt.

    Und zu guter Letzt sollte man bei dem Thema auch nicht vergessen, dass ADO im Prinzip inzwischen genauso "überholt" ist, wie DAO. Mit ADO.NET gib es inzwischen eine weitere alternative Datenzugriffstechnologie, die zur Zeit zwar noch nicht in Access-Anwendungen verwendet werden kann, aber dennoch verdeutlicht, dass die zuküftigen Konzepte, die Microsoft für den Datenzugriff verfolgt, von dem abweichen, was ADO und DAO bisher geboten haben.

    Fazit: Für reine Access Anwendungen ist nach wie vor DAO das Mittel der Wahl, wenn es um den Datenzugriff geht. Es gibt viele Argumente, die für DAO sprechen, aber nur sehr wenige dagegen. Anders sieht das erst dann aus, wenn man flexibel sein möchte, was das Backend der Anwendung angeht und einen Umstieg auf ein Datenbakserversystem in Erwägung zieht oder zumindest darauf vorbereitet sein möchte.



    Zurück zur Übersicht

    7 Gründe für Quellcodeverwaltung bei der Microsoft Access Entwicklung

    Ich erkläre ausführlich meine 7 wichtigsten Gründe bei der Access Entwicklung Quellcodeverwaltung einzusetzen. Selbst wenn du bereits mit Quellcodeverwaltung arbeitest, kannst du vielleicht noch ein paar neue Ideen aus diesem Artikel gewinnen.



    Zurück zur Übersicht

    Darstellung von einer 1:N-Beziehung mit einer Auswahlabfrage

    In diesem Artikel erkläre ich, wie man in einer Access Datenbank Daten einer 1:N-Beziehung mit einer Auswahlabfrage übersichtlich und informativ aufbereiten kann. Demonstriert wird die Verwendung der SUM (Summe) und COUNT (Anzahl) Aggregatfunktionen sowie die Ermittlung des letzten Kind(N)-Datensatzes mit mehreren Hilfsabfragen.



    Zurück zur Übersicht

    Werte aus mehreren Datensätzen einer Spalte als kommagetrennten Text ausgeben

    Ausführliche Erklärung und Beispielcode wie man die Daten die in einer Spalte einer Tabelle gespeichert sind, oder in einer Abfrage ausgegeben werden, als eine Liste komma-getrennter Werte ausgeben kann.

    Dieser Text ist unter dem vorangegangem Link zu finden, da er für das Format hier zu umfangreich ist.



    Zurück zur Übersicht

    Binärdaten sortieren und indizieren

    Kennst du den Datentyp Binär in Access? Er wird nur selten benötigt, aber wenn du einmal in eine Situation kommst, in der du ihn brauchst, ist dieser Artikel zum Sortieren und Indexieren von Binärdaten unbezahlbar.



    Zurück zur Übersicht

    Natürliche Zahlensortierung mit VBA

    Ist dir eigentlich schon mal bewusst aufgefallen, dass der Windows Explorer für Dateiname, die Zahlen enthalten, eine logische, natürliche Sortierung verwendet? Wäre es nicht sehr benutzerfreundlich, denselben Sortieralgorithmus auch in deiner Access Anwendung zu integrieren? Hier erkläre ich, wie du die natürliche Zahlensortierung mit VBA in Access verwenden kannst.



    Zurück zur Übersicht

    Daten extern bearbeiten lassen

    Manchmal möchte man ausgewählte Daten von Benutzern überprüfen oder bearbeiten lassen, die keinen direkten Zugriff auf die eigentliche Access Anwendungen haben können oder haben sollen. In diesem rein theoretischen Artikel zeige ich ein paar Möglichkeiten auf, wie man externen Benutzern indirekten Zugriff auf Daten aus einer Access Datenbank ermöglichen kann.



    Zurück zur Übersicht

    In einem berechnetem Tabellenfeld das Quartal aus einem Datum ermitteln

    Hier ein kurzer Pointer auf einen längeren Artikel: Ab Microsoft Access 2010 gibt es die Möglichkeit, berechnete Spalten in Access Tabellen zu erstellen. Dieses Thema erläutere ich in dem verlinkten Artikel und zeige wie man in einer berechnten Spalte aus einem Datum das Quartal ermitteln kann.

    Update 2017-02-26:Außerdem zeige ich, wie du ein berechnetes Tabellenfeld mit VBA-Code erstellen kannst und es gibt einen kleinen Performancevergleich zwischen berechneten Feldern in Abfragen und in Tabellen. Zusätzlich gibt es den Inhalt jetzt auch als Video.



    Zurück zur Übersicht

    Datenbanken auf OneDrive, DropBox oder Offline-Ordnern

    DropBox, OneDrive und Offline-Ordner sind moderne Technologien, die es ermöglichen von überall, selbst komplett offline, auf Dateien zuzugreifen, diese zu bearbeiten und diese synchron halten. Man kann sogar Dateien mit anderen Benutzern teilen und die Änderungen von mehreren Benutzern synchronisieren lassen. Aber wenn du dies mit einer Access Datenbank versuchst, wird das Ergebnis wahrscheinlich nicht deinen Erwartungen entsprechen. Lies warum DropBox, OneDrive und Offline-Ordner Access-Datenbanken zerstören.



    Zurück zur Übersicht

    Access AccDE (MDE / ADE) Datei mit Code erzeugen (VBA/VB-Script)

    Ein Artikel über einen Fehler bei einem Funktionsaufruf in einem VB-Script zum Erzeugen einer AccDE/ADE/MDE Datei mit Code. Das Problem entsteht bei der Verwendung der undokumentierten Aktion 603 der SysCmd-Funktion und die Lösung macht zwei Lektionen deutlich, die jeder Entwickler lernen sollte.



    Zurück zur Übersicht

    Berechnete Felder in Abfragen

    Anders als in Excel rechnete man in Access nicht in Tabellen. Aber Access beherrscht einfache mathematische Berechnungen genauso wie komplexe VBA-Funktionen. - Die Ergebnisse können in Mit berechneten Feldern in einer Abfrage dargestellt dargestellt werden.



    Zurück zur Übersicht

    Access SQL - Select Top X Datensätze – Ohne Bindungen

    Wenn du die ersten (TOP) X Datensätze aus einer Tabelle oder Abfrage in Access ermitteln möchtest, erhältst du evtl. mehr als die erwartete Anzahl Datensätze als Ergebnis, wenn es mehrere Datensätze mit gleichen Werten in der Spalte gibt, über die du sortierst. Dieser Artikel erläutert wie du in verschiedenen Szenarienn exakt X Datensätze mit einfachem SQL ermitteln kannst.



    Zurück zur Übersicht

    Access/VBA - Tutorial zur Ereignisbehandlung (Teil 1) - Erste Schritte für Anfänger

    Dies ist der erste Artikel in einer Artikelserie zu Ereignisbehandlung in Access/VBA. Dieses Tutorial behandelt Grundlagen der Ereignisbehandlung in Access für Anfänger.



    Zurück zur Übersicht

    Feststellen ob ein Access Formular als Dialog geöffnet ist

    Ein Access Formular kann entweder normal oder als Dialog geöffnet werden. Wie kann man feststellen, welchen Zustand es gerade hat?

    .

    Zurück zur Übersicht

    Abonniere meinen Newsletter

    *

    Ich werde Deine Email-Addresse niemals weitergeben. Du kannst den Newsletter jederzeit abbestellen.



    © 1999 - 2017 by Philipp Stiefel