Tipps & Tricks
Datenbanken: Access und SQLite
1.
Access 2016 mit der Runtime-Version Installation mit Fallstricken Die Runtime-Version von Access ist kostenlos und wird daher häu g eingesetzt. Die Runtime muss auf jedem Rechner installiert werden, der keine AccessVollversion hat, genauso wie die Vollversion. Häu g kommt es dabei allerdings zu Problemen, die nicht ganz trivial sind.
Wenn die Access-Runtime-Version nicht funktioniert, kann das mehrere Gründe haben:
• Access Runtime muss für die Access-Version passen, auf der sie entwickelt wurde. Installieren Sie daher unbedingt die passende Version.
• Für die Runtime muss ein Startformular erstellt sein. Ohne dieses Formular kann die Runtime nicht arbeiten. Erstellen Sie darum ein Startformular, über das Sie zu den einzelnen Datenbankfunktionen wechseln können.
• Der VBA-Code muss fehlerfrei durch die DebuggerFunktion gelaufen sein. Erst mit einem korrekten Code wird das Programm ausgeführt. Öffnen Sie die VBA-Ansicht und kompilieren Sie alle Module. Sobald die Kompilierung fehlerlos durchlaufen ist, kann der Programmcode von der Runtime-Version ausgeführt werden.
• Je nach Betriebssystem und Sicherheitseinstellungen blockiert Windows das Ausführen der Datei. Die Datenbank muss auf einem freigegebenen Ordner laufen. Standardmäßig müssen Sie im Sicherheitscenter jedes PC-Client den Pfad der Datenbank als sicheren Ort eintragen, ansonsten wird kein Code ausgeführt, und die Runtime schließt Access wieder. In der Runtime ist die Sache mit dem freigegebenen Ordner etwas knif iger, da Sie in der Runtime-Version nicht auf diese Freigabe zugreifen können.
Die Lösung am Beispiel Access 2016, Access 2019 und Office 365: Öffnen Sie die Registry, und nehmen Sie dort folgende Eintragungen vor: [HKEY_CURRENT_USER\Software\Microsoft\ Office\16.0\Access\Security] [HKEY_CURRENT_USER\Software\Microsoft\ Office\16.0\Access\Security\Trusted Locations\ Location(n)]
"Path"="C:\PfadZurDB\" "AllowSubfolders"=dword:00000001 "Description"="Die Beschreibung" "Date"="01.01.2007 12:00"
Für jeden Pfad wird eine Location in der Registry angelegt. Zur Syntax der Einträge:
• Path: Der Pfad des vertrauenswürdigen Speicherortes (mit abschließendem „\“)
• AllowSubfolders: Unterordner sind ebenfalls vertrauenswürdig? (0=Nein; 1=Ja)
• Description: eine beliebige Beschreibung
• Date: Änderungsdatum (Format: TT.MM.JJJJhh:mm) Für andere Access-Versionen wählen Sie in der Registry die passende Access-Version aus.
2.
Cloud
Datenbanken Cloud-fähig machen Die Digitalisierung führt in vielen Firmen zu einem hohen Datenaufkommen. Daher ist es verständlich, dass Unternehmen ihre Datenbanken in die Cloud verlagern wollen. Dabei müssen Sie aber auf eini
ge Dinge achten. Eine Datenbank einfach mal in die Cloud legen, das ist keine gute Idee. Optimieren Sie die Datenbanken vorher.
Da der Preis für eine Cloudlösung unter anderem von der Menge der Daten abhängt, die Sie migrieren wollen, bereinigen Sie den Datenbestand um unnötige Datensätze. Dabei kann es sich zum Beispiel um Daten handeln, die in der Vergangenheit angefallen, jedoch von minderer Qualität sind und lediglich aus rechtlichen Gründen gespeichert werden mussten. Denken Sie bei personenbezogenen Daten auch an die Datenschutz-Grundverordnung (DSGVO). Diese schreibt vor, dass Daten lediglich so lange gespeichert werden dürfen, wie es für die Verarbeitung nötig ist. Sofern der gesetzlich vorgeschriebene Zeitraum verstrichen ist, müssen diese Daten auf jeden Fall gelöscht werden.
Nachdem die zu bereinigenden und archivierenden Daten identi ziert sind, haben Sie die zu migrierende Datenmenge ermittelt. Jetzt überprüfen Sie die technischen Einstellungen der Datenbank. Stellen Sie dabei folgende Punkte sicher:
• Feldgrößen optimieren, sodass kein unnötiger Platz verschwendet wird.
• Namenstandards einhalten (Objekte wie Tabellen, Trigger, Views, Stored Procedures).
• Eindeutige Datentypen verwenden.
• SQL-Abfragen optimieren (belastbare Online-Abfragen, Time-out).
Mit diesen Voraussetzungen und einer guten Dokumentation können Sie Datenbanken sorgenfrei in die Cloud bekommen. 3.
Access ab 2010 Trigger erzeugen
Lange Zeit musste selbst geschriebener VBA-Code die Triggerfunktionen in Access ersetzen. Ab der Version Access 2010 können Sie die Triggerfunktionen über Datenmakros erstellen. Diese sind ausschließlich im Dateiformat accdb möglich. Die alten mdbDateiformate unterstützen keine Datenmakros.
Wird in einer Tabelle ein Datensatz eingefügt, geändert oder gelöscht, ist das ein Trigger, der ein Datenmakro starten kann. Datenmakros werden unmittelbar in der Entwurfsansicht einer Tabelle erstellt.
Zum Erstellen eines Triggers in Access 2016 öffnen Sie zunächst die Tabelle, für die das Makro aufgeführt werden soll. Aktivieren Sie dann in den Tabellentools das Register Tabelle. Sie finden in dieser Ansicht unter anderem die Gruppen Vorabereignisse und Nachfolgeereignisse. Eine oftmals gestellte Anforderung besteht darin, das letzte Datum der Änderung an einem Datensatz zu hinterlegen.
Zum automatischen Eintragen des letzten Änderungsdatums in einen Datensatz muss Ihre Tabelle zunächst um das Feld Änderungsdatum mit dem Datumsformat ergänzt werden. Nachdem Sie das Feld erzeugt haben, können Sie den Trigger erzeugen. Klicken Sie dazu in der Gruppe Vorabereignisse auf das Symbol Vor Änderung. Die Entwurfsansicht der Makrotools wird eingeblendet. Wählen Sie in diesem Fenster im Auswahlfeld NeueAktionhinzufügen den Eintrag Festlegen Feld aus. Der Eintrag wird erweitert. In das Eingabefeld Name tippen Sie den Namen des zu ändernden Feldes ein; in unserem Beispiel also Änderungsdatum. Bei der Eingabe des ersten Buchstabens schlägt das Programm bereits alle infrage kommenden Spalten vor, und Sie übernehmen mit einem Doppelklick den benötigten Feldnamen. In das Feld Wert tippen Sie die Funktion Jetzt() ein. Mit dieser Funktion wird das aktuelle Datum mitsamt der Uhrzeit ermittelt. Mit einem Mausklick auf die Schalt äche Schließen werden Sie gefragt, ob die Änderungen gespeichert werden sollen. Bestätigen Sie diese Nachfrage.
Sobald Sie ein beliebiges Feld in einem Datensatz dieser Datenbank ändern und den Datensatz verlassen, werden das aktuelle Datum und die Uhrzeit in das Feld Änderungsdatum eingetragen.
Sie können natürlich auch eine weitere Spalte mit dem Namen des Bearbeiters festlegen. Ergänzen Sie dann die Triggerfunktion um dieses Feld. So wissen Sie immer, wer die letzte Änderung zu welchem Zeitpunkt gemacht hat. 4.
SQLite 3.12.
SQL-Anweisungen mit SQLite-Browser
Mit SQLite erhalten Sie eine Datenbank, die kostenlos, klein und trotzdem leistungsstark ist. Mit der kostenfreien, gra schen Ober äche SQLite Browser lassen
sich ganz ohne SQL-Kenntnisse Tabellen anlegen und verwalten. Viel besser funktioniert das allerdings mit SQL-Befehlen. Dann sind dieser Datenbank keine Grenzen gesetzt.
Erstellen Sie mit ein paar Anweisungen eine neue Tabelle. Zum Verwalten von CDs bietet sich eine entsprechende Tabelle an. Diese Tabelle soll für jeden Datensatz eine eindeutige Nummer erhalten. Diese Nummer darf keinen Leerwert enthalten; das legt die Anweisung nut null fest. Die Eindeutigkeit erhalten Sie mit dem Befehl Primary Key. Nun legen Sie die benötigten Felder fest. Bei Textfeldern wird die maximale Länge durch die Eingabe in Klammern angegeben. Im Programm SQLite aktivieren Sie das Register SQL Ausführen und tippen den benötigten Code ein. Ein Beispiel zum Anlegen einer kleinen Tabelle: create Table CD (CDNummer INTEGER not null Pri mary Key, cd_titel CHAR(30) not null,
Interpret CHAR(10),
Datum date)
Mit einem Klick auf das Symbol Komplettes SQL ausführen werden die SQL-Anweisungen ausgeführt und als Ergebnis die gewünschte Tabelle erzeugt. Im unteren Bereich des Bildschirmfensters wird Ihnen das Resultat der Transaktion angezeigt. Sie verfügen jetzt zwar über eine Tabelle, jedoch ohne Datensätze. Zur Eingabe von Datensätzen aktivieren Sie das Register Daten durchsuchen und wählen dort das Symbol Fügt eine neue Zeile zur aktuellen Tabelle hinzu aus. Anstatt Informationen manuell einzugeben, funktioniert das auch über eine SQL-Anweisung: INSERT INTO tabellen_name (spalte1, spal te2, spalte3, etc.) VALUES ('Wert1', 'Wert2', 'Wert3', etc.)
In diesem konkreten Beispiel:
INSERT INTO CD (CDNummer, cd_titel, Interpret, Datum) VALUES ('10', 'Alternate Title', 'Mon keys', 1990)
Das Filtern von Daten führen Sie idealerweise auch mit SQL-Anweisungen durch.
Hier ein paar Beispiele, mit denen Sie alles aus Ihrer Datenbank herauskitzeln können. Als Beispiel wird im Folgenden eine Tabelle Kunden mit den Feldern Kundenname, Ort, und Umsatzziel aufgesetzt: select * from kunden;
Mit dieser Anweisung werden alle Felder und Datensätze ausgegeben. select kundenname, umsatzziel from kunden;
Diese Anweisung selektiert nur die Felder Kundenname und Umsatzziel. select kundenname, ort from Kunden where ort like "H%" order by kundenname;
Hiermit werden alle Kunden aus Orten, die mit einem H beginnen aufsteigend nach dem Kundennamen sortiert. select kundenname, umsatzziel from kunden where umsatzziel >3000 and umsatzziel < 10000; Möchten Sie alle Kunden mit einem Umsatzziel größer 3000 und kleiner 10.000 selektieren, ist dies die richtige Syntax.
Auch Rechenfunktionen und das Erzeugen virtueller Felder sind mit SQL möglich: select sum(umsatzziel) as GesUmsatz from Kunden; Hier wird der Gesamtumsatz aus allen Datensätze ermittelt und in dem virtuellem Feld GesUmsatz ausgegeben. select sum(umsatzziel)*1.1 as GesUmsatz from kunden;
Diese Anweisung erhöht das Umsatzziel um zehn Prozent.
Die neueste Version des Browsers nden Sie auf der Webseite https://sqlitebrowser.org/ (PortableApp for 3.12.1).
5.
Access 2016
Eine Frage der Union
Zum Ermitteln der Vereinigungsmenge oder der Schnittmenge aus zwei Tabellen benötigen Sie eine UNION-Abfrage. Mit dieser Abfrageart können Sie mehrere Abfragen hintereinander ausführen und das Ergebnis als Summe dieser einzelnen Abfragen ausgeben. Die UNION-Abfrage kann allerdings nicht schnell in der QBE-Ansicht mit der Maus zusammengeklickt werden; stattdessen müssen Sie manuell eine entsprechende SQL-Abfrage erstellen.
Bevor Sie eine UNION-Abfrage ausführen, müssen ein paar Voraussetzungen erfüllt sein:
Alle Abfragen haben die gleiche Anzahl von Spalten. Alle Abfragen haben die gleichen Spaltenüberschriften. Wenn das ausnahmsweise nicht der Fall sein sollte, muss man mit Alias-Namen arbeiten.
Die Reihenfolge der Spalten muss bei allen Abfragen gleich sein. Falls das nicht so ist, müssen Sie die Reihenfolge der Spalten in der abweichenden Abfrage entsprechend ändern. Die Felddatentypen müssen übereinstimmen.
UNION-Abfragen ltern standardmäßig doppelte Datensätze heraus. Wenn Sie das nicht wollen, verwenden Sie das Schlüsselwort UNION ALL.
Um eine Union-Abfrage in Access zu erstellen, aktivieren Sie das Register Abfrageentwurf. Im Bereich des Abfragetyps wählen Sie das Symbol Union aus. Das leere SQL-Abfragefenster wird angezeigt. Falls das nicht der Fall ist, wählen Sie über das Symbol Ansicht die SQL-Ansicht aus. Nach Eingabe der SQL-Anweisungen aktivieren Sie das Symbol Ausführen. Das Ergebnis der Union-Abfrage wird angezeigt. Möchten Sie als Ergebnis die Vereinigungsmenge der Tabellen sehen, so hängen Sie zwei Auswahlabfragen hintereinander.
SELECT [Nr] FROM [Tabelle A]
UNION
SELECT [Nr] FROM [Tabelle B]
Zum Auswerten, welche Daten nicht in beiden Tabellen vorkommen, wird die Abfrage komplexer.
SELECT [Tabelle A].[Nr]
FROM [Tabelle A] LEFT JOIN [Tabelle B] ON [Ta belle A].Nr = [Tabelle B].Nr
WHERE [Tabelle B].Nr Is Null
UNION
SELECT [Tabelle B].[Nr]
FROM [Tabelle B] LEFT JOIN [Tabelle A] ON [Ta belle A].Nr = [Tabelle B].Nr
WHERE [Tabelle A].Nr Is Null
Wenn Sie mehrere Felder sehen möchten, müssen Sie in beiden Union-Abfragen die entsprechenden Feldnamen hinzufügen. Sie haben auch die Möglichkeit, mehrere Abfragen zu einer Union-Abfrage zu vereinigen. Dazu geben Sie anstatt der Tabellennamen die Namen der Abfragen ein. Wenn die Abfragen zur Union-Abfrage unterschiedlich sind, kann es vorkommen, dass in einem Ausgabefeld Daten verschiedener Typen kombiniert werden müssen. In diesem Fall gibt die Union-Abfrage die Ergebnisse als Textdatentyp zurück, weil dieser Datentyp sowohl Text als auch Zahlen enthalten kann. Die Zahlen werden bei weiterer Bearbeitung als Text verarbeitet, können also nicht mathematisch ausgewertet werden. 6.
Access 2016 Importspezi kationen
Für Datenimporte lohnt es sich, eine Importspezi kation zu verfassen. Das erreichen Sie in Access über das Register Externe Daten, Gruppe Importieren und Verknüpfen, Neue Datenquelle.
Dann legen Sie fest, welche Art von Datei Sie importieren möchten. Nach der Auswahl startet der Import-Assistent. Wenn Sie auf eine Datenquelle stoßen, die weitere Spezi kationen benötigt (in der Regel sind das Text- und CSV-Dateien), erscheint im unteren linken Fenster die Schalt äche Erweitert. Daraufhin wird das Bildschirmfenster Importspezi - kationen eingeblendet. Hier legen Sie die benötigten Spezi kationen fest. Sobald das erledigt ist, betätigen Sie die Schalt äche Speichern unter und geben einen Namen für diese Spezi kation ein. Die Tabelle mit den Spezi kationen sehen Sie allerdings nicht, denn es handelt sich dabei um eine der Systemtabellen. Erst mit dieser Einstellung machen Sie die Tabellen sichtbar:
Wählen Sie im Register Datei die Optionen aus und im Fenster Access-Optionen die Kategorie Aktuelle Datenbank. Im Bereich Navigation betätigen Sie die Schalt äche Navigationsoptionen. Das gleichnamige Dialogfenster wird eingeblendet. Setzen Sie in diesem Fenster einen Haken in den Kontrollkasten Systemobjekte anzeigen. Anschließend werden alle Systemobjekte angezeigt. Die Importspezi kationen be nden sich in der Systemtabelle MSysIMEXSpecs. Sobald Sie diese öffnen, sehen Sie alle für den Import de nierten Daten.
Beginnt der Import in der vierten Zeile, ändern Sie den Inhalt der Zelle StartRow. Peter Schnoor