Language: Deutsch English


Home

Über mich

MS Access

Access & Email

Access-Client-Server

mySQL & Access

Visual Basic
for Applications

ASP

Random dumps of
dubious wisdom

Downloads

Windows Tips

Links

Impressum / Kontakt

 Last Update: 2024 - 05 - 21








Binärdaten in Access Datenbank sortieren und indizieren

von Philipp Stiefel, ursprünglich veröffentlicht 23. August 2016


Tanzende Buchstaben, Luminale 2016, Frankfurt Katharinenkirche

Foto von Sylvia Eisenhauer, Tigerpfote. Exklusiv lizensiert für codekabinett.com, alle Rechte vorbehalten.

Ab und zu möchtest du binäre Daten in einer Microsoft Access Tabelle speichern. Wenn es sich bei den Daten um eine Datei, wie z.B. ein Bild, ein PDF oder ein Office Dokument handelt, gibt es dabei kein Problem. Du verwendest einfach den Datentyp OLE-Objekt, um diese Art von Daten in einer Access Tabelle zu speichern. - Einfach.

Jedoch könntest du auch mit binären Daten zu tun haben, die nicht eine Art Datei/Dokument sind, sondern eher ein binärer Schlüssel. Was nun?

Trotz seinem Namen ist OLE-Objekt primär ein Datentyp für Binärdaten; beliebige Binärdaten. Also kannst du natürlich auch deine binären Schlüssel darin speichern.

Wenn du „Schlüssel“ im Kontext mit Datenbanken hörst, solltest du sofort an ein paar mögliche Implikationen denken. Also sollten wir ein paar Fragen stellen.

  • Kann die Jet-/ACE-Datenbankengine die Eindeutigkeit der Daten in einer OLE-Objekt-Spalte sicherstellen? – Nein, sorry, kann sie nicht.
  • Kannst du irgendeinen Index auf einer OLE-Objekt-Spalte erstellen? – Wieder nein.
  • Kannst du die Daten in einer OLE-Objekt-Spalte sortieren? – Nein, zum dritten.

Nun gut, diese Einschränkungen machen Sinn. Theoretisch kann die Größe eines OLE-Objektes bis zu 1 GB sein. Derartig lange Daten zu indizieren wäre Wahnsinn. Und es macht auch nicht wirklich Sinn, Office Dokumente oder Bilder nach ihrer Binärrepräsentation zu sortieren.

Dennoch, wir haben weiterhin den unverfrorenen Wunsch Binärdaten zu sortieren oder zu indizieren.

Wenn du im Access Tabellendesigner schaust, wirst du schnell feststellen, dass es dort keinen weiteren Datentyp für Binärdaten gibt. (Der Datentyp Anlage ist nur ein OLE-Objekt in einen komplexen Typ verpackt.)

Binär als Retter

Vielen unbekannt, verfügt Access aber doch über einen nativen Datentyp für kurze Binärdaten. Binär.

Aus unbekannten, seltsamen Gründen hat sich das Access Team bei Microsoft entschieden, dass man über den grafischen Tabellendesigner keine Spalte dieses Typs erstellen kann. Daher kennt kaum ein Entwickler den Binär Datentyp.

Wenn eine Tabelle bereits eine Spalte vom Binär-Datentyp enthält, wird diese aber korrekt in der Entwurfsansicht der Tabelle angezeigt und ihre Eigenschaften können bearbeitet werden.

Entwurfsansicht einer Microsoft Access Tabelle mit Binär-Spalte

Der Typ Binär kann bis zu 510 Bytes lang sein und er kann sortiert und indiziert werden. – Das ist genau was wir für unsere binären Schlüssel benötigen.

Standardmäßig hat der Binär Datentyp in Access eine variable Länge. Sein Name ist verwirrend, denn in SQL ist der Varbinary Datentyp für Daten variabler Länge vorgesehen und der Binary Typ für Daten fester Länge. Die Jet-/ACE-Datenbankengine unterstützt auch Binär-Spalten fester Länge, aber Access zeigt diese Option nirgends in der Benutzeroberfläche an, nicht mal für bestehende Spalten.

Binary/Binär-Spalten fester Länge werden immer bis zu ihrer Maximallänge mit Nullen aufgefüllt.

Wie können wir nun also eine Binär-Spalte in einer Tabelle erstellen, wenn es nicht über den Tabellendesigner möglich ist?

Es gibt drei Wege um eine Binär-Spalte in einer Microsoft Access Datenbank zu erstellen.

1. Eine Binär-Spalte mit DDL erstellen

Du kannst DDL (=Data Definition Language, eine Untermenge von SQL) verwenden um eine neue Tabelle mit einer Binär-Spalte zu erstellen. Zum Erstellen einer neuen Tabelle verwendest du das CREATE TABLE Statement.

Um eine DDL Abfrage auszuführen, erstelle einfach eine neue Abfrage, füge keine Tabellen hinzu, wechsle dann in die SQL-Ansicht und gibt das DDL-Statement in dem Editor Fenster ein. Hier ist das DDL-Statement, um eine Tabelle mit einer Binary-Spalte fester Länge, einer Varbinary-Spalte zu erstellen. Der Vollständigkeit halber habe ich auch eine Longbinary (OLE-Objekt) aufgenommen.

CREATE TABLE tblBinTestSQL ( Id counter NOT NULL PRIMARY KEY, VarbinaryColumn varbinary(100) NULL, BinaryColumn binary(100) NULL, OLEColumn Longbinary NULL );

Wenn du eine Binary- oder Varbinary-Spalte zu einer bestehenden Tabelle hinzufügen möchtest, verwende das ALTER TABLE DDL Statement mit der ADD COLUMN Klausel.

ALTER TABLE tblBinTestSQL ADD COLUMN VarbinaryColumn2 varbinary(100) NULL, BinaryColumn2 binary(100) NULL, OLEColumn2 Longbinary NULL;

2. Eine Binär-Spalte mit VBA und DAO erstellen

Natürlich kannst du mit VBA und dem DAO.TableDef-Objekt ebenfalls eine Binär-Spalte, sowohl variabler als auch fester Länge, erstellen. Du erstellst entweder ein neues TableDef-Objekt oder verwendest das TableDef einer bestehenden Tabelle. Dann erstellst du ein DAO.Field-Objekt mit dem Typ DataTypeEnum.dbBinary und fügst es an die Fields-Auflistung an.

Dies erzeugt eine Binär-Spalte variabler Länge. Wenn du eine Binär-Spalte fester Länge erzeugen willst, fügst du das dbFixedField Kennzeichen zur Attributes-Eigenschaft des Field hinzu.

Lass dich nicht durch das dbVarBinary Element des DataTypeEnum verwirren. Dies ist nur für ODBC-Direct-Workspaces (veraltet/eingestellt) vorgesehen und kann nicht in einer Jet-/ACE-Datenbank verwendet werden.

Public Sub CreateBinaryColumns() Dim td As DAO.TableDef Dim db As DAO.Database Dim fd As Field Set db = CurrentDb Set td = db.CreateTableDef("tblBinTestDAO") Set fd = td.CreateField("ID", DataTypeEnum.dbLong) fd.Attributes = fd.Attributes Or dbAutoIncrField td.Fields.Append fd Set fd = td.CreateField("BinaryColumn", DataTypeEnum.dbBinary, 100) fd.Attributes = fd.Attributes Or dbFixedField td.Fields.Append fd Set fd = td.CreateField("VarbinaryColumn", DataTypeEnum.dbBinary, 100) td.Fields.Append fd Set fd = td.CreateField("OLEColumn", DataTypeEnum.dbLongBinary) td.Fields.Append fd db.TableDefs.Append td End Sub

3. Ein bestehendes Feld kopieren

Eine einfache dritte Möglichkeit ein Binär-Feld in deiner Tabelle zu erzeugen ist es, einfache eine solche Spalte zu kopieren. Du öffnest die Navigationsoptionen und aktivierst Systemobjekte anzeigen. Dann öffnest du die Tabelle MSysObjects in der Entwurfsansicht und kopierst die Spalte Owner in die Zwischenablage.  Dann öffnest du deine eigene Tabelle in der Entwurfsansicht und fügst die kopierte Spalte dort ein. Danach kannst du sie dann umbenennen und die Größe ändern. – Dies ist dann eine Binär-Spalte variabler Länge. Es ist keine Binär-Spalte fester Länge in den Systemtabellen vorhanden, die man kopieren könnte.

Mit Binärdaten in VBA arbeiten

Wenn du mit den Binärdaten in VBA arbeitest, verwendest du ein Bytearray als Datentyp für deine Binärdaten. Um Daten aus einer Tabelle zu lesen oder hineinzuschreiben greifst du einfach auf die Value-Eigenschaft des DAO.Field in einem Recordset zu.

Anders als bei den OLE/Longbinary Feldern, kannst du die GetChunk/AppendChunk-Methoden mit Binary-Spalten nicht verwenden. Stattdessen verwendest du direkte Zuweisungen von und zu Bytearray Variablen.

Hier ist ein Beispiel, wie man einen neuen Datensatz erstellt. Die Binärdaten werden durch eine Hilfsfunktion zufällig erzeugt.

Public Sub AddBinaryData() Dim rs As DAO.Recordset Dim db As DAO.Database Dim aByteArray() As Byte Set db = CurrentDb Set rs = db.OpenRecordset("SELECT * FROM tblBinTestDAO WHERE Id<0") aByteArray = RandomByteArray(20) rs.AddNew rs.Fields("VarbinaryColumn").Value = aByteArray rs.Fields("BinaryColumn").Value = RandomByteArray(15) rs.Fields("OLEColumn").Value = RandomByteArray(150) rs.Update rs.Close Set rs = Nothing Set db = Nothing End Sub Private Function RandomByteArray(ByVal arrayLength As Integer) As Byte() ' Demo/helper function to create random byte array Dim retVal() As Byte, i As Integer Randomize ReDim retVal(arrayLength - 1) For i = 0 To arrayLength - 1 retVal(i) = CByte(Rnd() * 255) Next i RandomByteArray = retVal End Function

Wenn du mit Arrays vertraut bist, ist es ziemlich einfach mit Binärdaten in VBA zu arbeiten.

Warnung – Was du siehst, ist nicht was du bekommst

Wenn du dir die Daten in einer Tabelle mit Binärspalte anschaust, zeigt Access diese Daten als Unicode Strings an. Sei dir immer bewusst, dass das, was dort angezeigt wird, keine absolut akkurate Abbildung der tatsächlichen Daten ist.

Binary data displayed in an Access Table

Hier ein paar Probleme und Ungenauigkeiten, die dabei passieren können.

  • Üblicherweise repräsentiert jedes Zeichen, das du siehst, zwei Bytes, aber am Ende der Bytefolge kann es auch nur ein Byte sein.
  • Es gibt einige nicht darstellbare Zeichen. Sie werden alle durch ein schlichtes Quadrat abgebildet. Du kannst also mehrere, optisch identische Quadrate sehen, aber jedes von ihnen könnte einen anderen Binärwert repräsentieren.
  • Es ist vollkommen zulässig, wenn in Binärdaten eine beliebige Anzahl an Nullen an einer beliebigen Stelle aufeinanderfolgt. Wenn aber diese Daten als String behandelt werden (Access tut genau das, beim Anzeigen der Daten), bedeutet ein Null-Byte das Ende des Strings. Access schneidet dann die restlichen Daten ab und zeigt nur den ersten Teil der Daten an.

Also sei davor gewarnt die Binärdaten, die von Access so dargestellt werden, mit Copy&Paste hin und her zu kopieren. Du könntest dabei Daten verlieren.

Fazit

Es mag nur selten vorkommen, dass du Binärdaten Sortieren und/oder Indizieren musst. Aber falls es das tut, weißt du jetzt, dass es dafür einen geeigneten Datentyp in Microsoft Access gibt und du weißt wie du eine solche Spalte in deiner Tabelle erstellen kannst.

Ich hoffe die hat der Artikel gefallen und du hast etwas Neues gelernt.

Share this article: Share on Facebook Tweet Share on LinkedIn Share on XING

Abonniere meinen Newsletter

*

Ich werde Deine Email-Addresse niemals weitergeben. Du kannst den Newsletter jederzeit abbestellen.
Die Emailliste wird bei Mailchimp in den USA gespeichert. Diese Auftragsverarbeitung ist vertraglich geregelt. Weitere Details in der Datenschutzerklärung.

Vorteile des Newsletter-Abos



© 1999 - 2024 by Philipp Stiefel - Datenschutzerklärung