Ist Ihnen der Begriff SQLite schon einmal untergekommen Wenn nicht, so ist das nicht weiter verwunderlich, handelt es sich dabei doch um ein Datenbanksystem, das Sie als Access-Benutzer wahrscheinlich nicht brauchen und viele ähnlichkeiten mit Access aufweist. Wenn doch, so könnte das der Verbreitung von Android-Smartphones und -Tablets geschuldet sein, denn unter Android ist SQLite das Standarddatenbankformat mit Systemunterstützung – die allermeisten Apps verwenden es, wenn größere Datenmengen zu speichern sind. Grund genug, dieses Format einmal genauer unter die Lupe zu nehmen.
Was ist SQLite
SQLite gibt es bereits seit dem Jahr 2000. Es wird im Kern von drei Programmierern weiterentwickelt, jedoch von einer großen Schar von Unterstützern mitbetreut, da der Quellcode unter Public Domain steht. Einst war es als Datenbanksystem für einige wenige Aufgaben gedacht gewesen. Statt etwa Programmeinstellungen in INI-Dateien unterzubringen, sollte die Möglichkeit bestehen, diese komfortabler in einer Datenbank zu verwalten, ohne dafür extra eine umfangreiche Datenbank-Engine installieren zu müssen. Dieser Grundgedanke – das Lite im Namen – steht auch heute noch an erster Stelle. Wenn Sie sich auf der Website von sqlite.org die Precompiled Binaries für Windows herunterladen, sind Sie vielleicht erstaunt, dass sich lediglich zwei Dateien im ZIP-Paket befinden. Die eine davon, die Kommandozeilenanwendung sqlite3.exe, reicht dabei schon völlig für die Verwaltung der Datenbankdateien aus.
Inzwischen hat sich SQLite gemausert und steht den Möglichkeiten der Access Database Engine (vormals Jet-Engine) kaum nach. Die Größe der Datenbanken kann in die Gigabytes gehen, alle analogen Datentypen sind vorhanden, der SQL-Dialekt ist umfangreicher als der von Access und ähnelt eher dem größerer DBMS, Fremdschlüssel werden unterstützt und Mehrbenutzerzugriffe sind kein Problem. Auch Transaktionen, Views und Trigger fehlen nicht. Inzwischen sind zahlreiche Schnittstellen zu allerlei Programmierumgebungen entstanden. Viele namhafte Software-Firmen haben das Format inzwischen adaptiert und verwenden es auch jenseits Android. Um einige Programme zu nennen, die einen großen Teil ihrer Daten über SQLite verwalten: Firefox, Chrome, Skype, Dropbox, XNView.
Um Missverständnisse zu vermeiden: SQLite ist ein Pendant zur Access Database Engine, nicht jedoch zu Access. Es gibt keine grafische Oberfläche wie für den Tabellenentwurf oder die Datenblatt-ansicht unter Access, sondern mit der sqlite3.exe lediglich ein Kommandozeilen-Tool zur Anlage und Abfrage der Datenbanken, oder mit der sqlite3.dll eine Programmierschnittstelle, die per API angesprochen werden muss. Wer eine grafische Verwaltung haben will, der benötigt separate Editoren, von denen allerdings zahlreiche vorhanden sind. Dazu später mehr.
Eine SQLite-Datenbank besteht aus nur einer Datei, die üblicherweise die Endung .sqlite oder .db aufweist, so, wie bei Access auch nur eine .mdb– oder .accdb-Datei nötig ist. Intern kann die Datei allerdings mehrere Datenbanken enthalten, wovon die eigentlich genutzte meist den Namen main trägt, während die anderen Systemdatenbanken sind. Darunter befinden sich die Tabellen, die relational miteinander verknüpft sein können. Dabei können Fremdschlüsselbeziehungen angelegt oder Beschränkungen (Constraints) definiert werden. Beziehungen zwischen mehreren Datenbankdateien, so wie gelinkte Tabellen unter Access, sind jedoch nicht direkt möglich. Views schließlich dienen der gefilterten Ausgabe der Tabellendaten. SQLite-Views sind im Unterschied zu den meisten DBMS aber nicht aktualisierbar.
Eine umfassende Darstellung des Formats und seiner Features kann an dieser Stelle nicht gegeben werden. Schauen Sie sich bei Interesse die durchaus gelungene Dokumentation auf der Seite der Herausgeber an. Betrachten wir eher, was wir unter Access mit SQLite anfangen können.
SQLite und Access
Wie kommen die Daten einer SQLite-Datenbank in oder zu Access Eine direkte Verknüpfung ist zunächst nicht möglich, da hierfür der entsprechende ISAM-Treiber fehlt. Per Code könnten Sie die sqlite3.dll ansprechen, benötigten dafür aber API-Deklarationen, die Sie für VB(A) vergeblich suchen werden. Zum Glück ist das aber überflüssig, da Olaf Schmidt mit dem vbRichClient eine kostenlose Schnittstelle zur Verfügung stellt, deren Programmierung fast identisch zu der von DAO oder ADO ist.
ODBC
Um eine Datenbank eines fremden DBMS direkt in Access zu bekommen, braucht es einen ODBC-Treiber, wie ihn etwa der MS SQL-Server oder MYSQL mitbringen. Auch für SQLite existieren ODBC-Treiber, wobei sich jener von Christian Werner quasi zum Standard entwickelt hat. Andere Treiber sind meist Modifikationen seines Treibers, der im Quellcode veröffentlicht ist. Laden Sie sich also das Installationspaket von dessen Seite herunter und starten das Setup.
Danach finden Sie im ODBC-Datenquellen-Administrator von Windows einen neuen Eintrag SQLite3 ODBC Driver auf der Treiberseite, wie in Bild 1. Den Datenquellen-Administrator finden Sie in den neueren Windows-Versionen über Startmenü|Verwaltung|Daten-quellen (ODBC).
Bild 1: Der ODBC-Datenquellen-Administrator von Windows mit der Liste installierter Treiber
Um anschließend eine neue Datenquelle (DSN) zu einer SQLite-Datenbank im Administrator anzulegen, öffnen Sie die Seite Benutzer-DSN, klicken Hinzufügen an und wählen dann den neuen Treibereintrag aus. Ein Klick auf Fertig stellen führt zum Konfigurationsdialog des Treibers (s. Bild 2). Die Voreinstellungen des Dialogs können Sie zunächst getrost übernehmen. Essentiell sind jedoch zwei Einträge: Unter Database Name geben Sie den Pfad zur SQLite-Datei an, den Sie auch über die Schaltfläche Browse ermitteln können. Was dann passiert, hängt aber vom Kontrollkästchen Don“t create Database ab. Ist hier kein Häkchen gesetzt, so wird nach dem Klick auf OK automatisch eine leere SQLite-Datei erstellt, falls die angegebene Datei noch nicht existiert. Ansonsten wird eine Verbindung zur bestehenden Datei hergestellt. Ist das Häkchen gesetzt, so wird keine leere Datei angelegt. Existiert die angegebene dann Datei nicht, so erfolgt beim Verbinden eine Fehlermeldung des Treibers. Lassen Sie für unseren Test, abweichend zur Abbildung, das Häkchen weg. Die wichtigsten weiteren Parameter des Dialogs:
Bild 2: Konfiguration einer SQLite-ODBC-Verbindung (DSN)
- No TXN bedeutet keine Transaktionen. Alle SQL-Anweisungen werden sofort ausgeführt, wenn hier ein Häkchen gesetzt ist. Andernfalls können Sie SQL-Anweisungen in BEGIN TRANSACTION .. COMMIT … ROLLBACK … END TRANSACTION-Blöcke einschließen.
- Foreigns Keys: Fremdschlüsselunterstützung eingeschaltet/ausgeschaltet. Standardmäßig ist dies deaktiviert.
- Lock Timeout: Hier können Sie einen Wert im Millisekunden angeben, die Zeitspanne, die eine Sperre zur SQLite-Datei maximal andauern darf, bevor der Treiber einen Schreibversuch abbricht. Für lokale Systeme hat dies normal keine Bedeutung.
Nachdem Sie den Dialog mit OK bestätigt haben, finden Sie in der Liste der Benutzerdatenquellen den Eintrag, den Sie im Dialog unter Data Source Name vergeben hatten.
Import/Export
Schreiten Sie nun zur Tat und öffnen eine beliebige Access-Datenbank, etwa die Demodatenbank sqlite_demo.mdb zu diesem Beitrag. Markieren Sie eine Tabelle, in unserem Test die Tabelle tblTest, und gehen Sie dann unter Access 2003 und früher auf Datei|Exportieren, wählen als Dateityp ODBC-Datenbanken() und geben einen neuen Namen für die zu exportierende Tabelle an.
Verwenden Sie hier tblTest2. Unter Access 2007 und neuer öffnen Sie stattdessen das Kontextmenü der Tabelle, gehen auf Exportieren|ODBC-Datenbank.
Anschließend öffnet sich der Datenquellendialog, in dem Sie unter Computerdatenquellen die zuvor angelegte SQLite-Verbindung auswählen. Im Normalfall sollte die Aktion nun erfolgreich quittiert werden – schon haben Sie eine Access-Tabelle nach SQLite transferiert.
Interessant ist, dass dies selbst bei Tabellen mit Anlagefeldern fehlerfrei funktioniert – versuchen Sie es etwa mit der versteckten Systemtabelle MsysResources unter Access 2010. Allerdings werden Sie anschließend mit dem Anlagefeld, welches als Memofeld in die SQLite-Datei exportiert wurde, nicht viel anfangen können. Dort steht dann nur der Name der Anlage, nicht aber deren Binärdaten.
Nachdem die Tabelle exportiert wurde, kommt jetzt der umgekehrte Vorgang. Wir möchten die Tabelle fürs Erste aber nicht reimportieren, sondern eine Verknüpfung zu ihr anlegen. Dazu gehen Sie unter Access 2003 auf Datei|Externe Daten|Tabellen verknüpfen und wählen wieder ODBC-Datenbanken().
Unter Access 2007 und neuer wählen Sie den Ribbon-Eintrag Externe Daten|ODBC-Datenbank|Verknüpfen. Nach Auswahl der SQLite-Verbindung sollten Sie mit einem Dialog konfrontiert werden, der die enthaltenen Tabellen anzeigt.
Lief im vorherigen Schritt alles glatt, dann sollte hier die Tabelle tblTest2 aufgeführt sein. Markieren Sie diese und klicken Sie auf OK – schon finden Sie die per ODBC verknüpfte Tabelle im Datenbankfenster oder Navigationsbereich vor.
Nun wird es spannend: was ist beim Export und Import aus den Feldern der ursprünglichen Tabelle geworden Die tblTest enthält alle Feldtypen, die bis Access 2007 möglich waren. öffnen Sie die Tabellen tblTest und tblTest2 parallel im Entwurf, sodass Sie das Schema vergleichen können – siehe Bild 3.
Bild 3: Originale Testtabelle unter Access und das ODBC-verknüpfte Pendant aus SQLite rechts im Entwurf
Tatsächlich sind die Felddefinitionen bis auf einige Ausnahmen identisch. Das ID-Feld wurde mit Long-Wert korrekt als Primärschlüssel übernommen. Der Autowert funktioniert ebenfalls, auch wenn er im Entwurf so nicht angezeigt wird. Abweichungen gibt es bei der GUID, die unter Access als Zahl/Replikations-ID definiert ist. In SQLite wurde diese als Binärfeld angelegt, was im Entwurf durch den Feldtyp OLE-Objekt ersichtlich wird.
Das ist sicherlich tragbar, wird dieser Feldtyp doch sehr selten verwendet. Bei Dezimalfeld kam es zur Konvertierung in ein Memo-Feld, also ein Textfeld mit mehr als 255 Zeichen.
Auch das ist zu verschmerzen, da der Dezimaltyp ohnehin weitere Angaben zur Genauigkeit und zur Kommaposition braucht. Der Single-Type kommt als Double zurück, was ebenfalls akzeptiert wird. Weniger Toleranz sollte dagegen die Wandlung von Währung in Memo erfahren. Zwar ist der Sachverhalt hier ähnlich, wie beim Dezimalfeld, jedoch lässt sich mit einem Memofeld schlecht rechnen. Zur Erläuterung finden Sie im Absatz zu den SQLite-Datentypen weitere Informationen.
Dass schließlich der proprietäre Typ Hyperlink nach Memo umgewandelt wird, ist nicht erstaunlich. Was außerdem fehlt, sind Standardwerte, die für Felder unter Access vergeben wurden.
Zusammenfassend lässt sich feststellen, dass der Export/Import-Vorgang zwar brauchbar ist, jedoch verbesserungswürdig. Dazu kommen zwei Lösungen infrage: entweder Sie modifizieren die SQLite-Felder nach Export mit einem SQLite-Editor Ihrer Wahl, oder Sie legen die Tabellen gleich per VBA-Code als DDL-SQL-Anweisungen selbst an. Dazu finden sie in der Demodatenbank einige Routinen, die später gesondert besprochen werden.
Machen wir als Nächstes den Test, was aus den Feldinhalten geworden ist. öffnen Sie dazu die beiden Tabellen übereinander, wie in Bild 4.
Bild 4: Anzeige der Daten aus originaler und verknüpfter Tabelle
Mit dem Ergebnis kann man zufrieden sein. Allerdings schleichen sich beim Single-Feld offenbar Rundungsfehler ein. Dasselbe gilt übrigens für alle Fließkommazahlentypen, auch wenn das aus der Abbildung nicht hervorgeht.
Es betrifft also alle Felder vom Type Single, Double und Currency. Erfreulich ist, dass offenbar auch das OLE-Objekt korrekt übernommen wurde: ein Doppelklick auf die Zelle öffnet das Icon, das in ihm verborgen ist. Wenn Sie mit den Datensätzen der verknüpften SQLite-Tabelle spielen, werden Sie bald feststellen, dass es zu seltsamen Fehlermeldungen kommt, ohne dass diese so einfach nachzuvollziehen wären.
Einmal funktioniert das Editieren nicht, dann doch, und das Löschen der Datensätze scheint ebenfalls dem Zufallsprinzip zu unterliegen. Meist erscheint die Fehlermeldung aus Bild 5.
Bild 5: Fehlermeldung beim Löschen von Datensätzen in der ODBC-verknüpften Tabelle
Wer ist der andere Benutzer, der dieselben Daten verändern will, und das gar beim Löschen Die Begründung für diese missverständliche Fehlermeldung liegt in den Untiefen der Access Database Engine verborgen.
Ich ließ einen ODBC-Trace während des Löschvorgangs loggen. Ein ODBC-Trace ist ein genaues Protokoll der Vorgänge zwischen Client und Server, hier also Access und dem SQLite-Treiber.
Es lässt sich im ODBC-Administrator über Aktivierung der Ablaufverfolgung anlegen. Eine Analyse der Logs ergab, dass Access beim Löschen und Editieren den entsprechenden Datensatz über eine Kombination fast aller Felder zu ermitteln versucht, statt den Primärschlüssel heranzuziehen, warum auch immer.
Die entscheidende Zeile im Protokoll sieht so aus:
DELETE FROM "tblTest2" WHERE "ID" = AND "TextFeld" = ''Sascha Trowitzsch'' AND "LongFeld" = 1
AND "IntFeld" = 1 AND "DoubleFeld" = 1.2 AND "SingleFeld" = 1.29999995231628 AND "ByteFeld" = 5
AND "BoolFeld" = AND "DateFeld" = {ts ''2015-02-01 08:44:00''}
Da aber die Rundungsfehler beim Double- und Singlefeld dazu führen, dass Abweichungen zu den Werten entstehen, die Access in der Tabellenansicht gespeichert hat, geht Access davon aus, dass die entsprechenden Daten mittlerweile von anderer Seite geändert wurden, und hält den Löschvorgang an.
Wir kommen deshalb nicht umhin, uns mit den SQLite-Datentypen zu beschäftigen.
SQLite Datentypen
Zwar können unter SQLite zahlreiche Feldtypen definiert und für diese wiederum diverse Parameter eingestellt werden, doch intern gibt es lediglich vier sogenannte Storage-Klassen. Das sind Integer, Real, Text und Blob. Real ist dabei ein Fließkommawert mit 8 Bytes.
Das bedeutet, dass jeglicher Datentyp auf einen dieser Grundtypen gecastet werden muss.
SQLite nimmt diese Konvertierung selbst vor, wobei die eigentliche Felddefinition herangezogen wird. Genaueres dazu erfahren Sie auf der Seite zu Data Types auf sqlite.org. Dort finden Sie eine Zuordnungsmatrix der Feldtypen zu den Grundklassen.
Diesen Umstand zu kennen, ist zweifellos wichtig, wenn es um die Interaktion von Access mit SQLite geht.
Es leiten sich daraus einige Regeln ab, die zu beherzigen sind.
- Vermeiden Sie die Typen Single und Währung. Verwenden Sie stattdessen Double. Wenn die Genauigkeit von Double für Währungsberechnungen nicht ausreichen sollte, so benutzen Sie Text (varchar)! Unter Access werden Sie dann allerdings zusätzliche Routinen zur Konvertierung von Text in Currency unterbringen oder Abfragen umgestalten müssen.
- Nehmen Sie für die numerischen Typen Decimal und Numeric ebenfalls Text.
- Boolean, also Ja/Nein-Feld, kann bleiben, wie es ist.
- Datumstypen definieren Sie unter SQLite entweder als DateTime oder Timestamp.
- Versehen Sie jede Tabelle mit einem Primärschlüssel. Außerdem ist es, wie bei anderen DBMS nützlich, ihr ein zusätzliches indiziertes Timestamp-Feld zu spendieren, wie einige Programmierer berichten. Das Timestamp-Feld muss mit dem Default-Wert CURRENT_TIMESTAMP versehen sein.
- Größenangaben für Textfelder interessieren SQLite intern nicht. In einem Textfeld können beliebig viele Zeichen abgespeichert werden. Die Definition ist aber für Access wichtig, denn als varchar(255) definierte Felder werden von Access als Text verstanden, größere als Memo. Letzteres gilt auch für nur als varchar angelegte Felder.
Alles in allem ist die Verbindung Access-SQLite nicht ganz so trivial, wie die zu anderen DBMS. Die Tabellen einer SQLite-Datenbank einfach verknüpfen und die Daten unter Access in Datenblättern und Formularen verwalten, reicht nicht aus. Der ODBC-Treiber ist nicht ganz konform mit der Access Database Engine. Um zusätzliche Programmierung und Workarounds kommen Sie nicht herum, wenn Sie die Datenintegrität sicherstellen wollen. Beispiel: Das Löschen eines Datensatzes, was in der Datenblattansicht schiefgeht, lässt sich problemlos mit folgender Code-Zeile bewerkstelligen:
CurrentDb.Execute "DELETE FROM tblTest WHERE ID=2"
Programmierung
Im Vorhergehenden hatten Sie die SQLite-Tabellen manuell mit Access verknüpft. Das geht jedoch auch per Code, und zwar mit der Anweisung DoCmd.TransferDatabase. Dieser können Sie als Parameter den ODBC-Connection-String übergeben, statt eine angelegte DSN zu verwenden. Alle Versuche dieser Art schlagen aber fehl. Access quittiert die Anweisung mit der Fehlermeldung -7778, „Es gibt keine Meldung für diesen Fehler“, obwohl der Treiber geladen wird. Sie können das im Modul mdlSQLiteODBC, Prozedur LinkSQLiteTable der Demo-Datenbank nachstellen.
Die Lösung ist, zunächst eine manuelle Verknüpfung anzulegen und später beim Aktualisieren der Verknüpfung über das TableDef-Objekt nur den Connect-String zu ändern:
sConx = "ODBC;Driver={SQLite3 ODBC Driver}; Database=c:\irgendwas\test_sqlite.db;"
CurrentDb.TableDefs("tblTest2").Connect = sConx
CurrentDb.TableDefs.Refresh
ADO
Aus der Sicht des Access-Programmierers sind die Möglichkeiten beschränkt, wenn es um ODBC-Datenbanken unter DAO geht. Konnte man unter Access 2003 noch über das Connection-Objekt von DAO aus direkten Zugriff auf ODBC-Objekte erhalten, so wie das PassThrough-Abfragen ermöglichen, scheidet diese Methode seit Access 2007 aus.
In der Access Database Engine wurde das Connection-Objekt gestrichen. Daher ist nun ADODB das Mittel der Wahl
Für eine Verbindung per ADODB ist ein entsprechender ADO-Provider nötig. Es existieren zwar native SQLite-Provider, doch sind diese entweder nicht kompatibel mit SQLite 3 oder sie arbeiten wenig vertrauenswürdig. Deshalb verwenden Sie besser den ODBC-Provider, der wiederum auf unseren ODBC-Treiber von C. Werner aufsetzt.
Ein rudimentärer ConnectionString für ein ADO-Connection-Objekt sieht so aus:
Ende des frei verfügbaren Teil. Wenn Du mehr lesen möchtest, hole Dir ...
den kompletten Artikel im PDF-Format mit Beispieldatenbank
diesen und alle anderen Artikel mit dem Jahresabo