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 - 01 - 25








Sort and index binary data in an Access database

by Philipp Stiefel, originally published August 23rd, 2016


Dacing letters, Luminale 2016, Frankfurt Katharinenkirche

Photo by Sylvia Eisenhauer - Tigerpfote. Exclusively licensed to codekabinett.com, all rights reserved.

Now and then you want to store binary data in your Microsoft Access database. If the binary data in question is a file like a picture, PDF or an Office document, this is no problem at all. You use the OLE Object (OLE = Object Linking and Embedding) data type to store this kind of data in an Access table. – Easy.

However, sometimes you might be dealing with binary data, which is not any sort of document/file, but rather a short binary key. So what now?

Despite its name, OLE Object is primarily a data type to store binary data; any binary data. So of course we can just store our binary keys in an OLE Object column. - So far, so good.

If you hear “key” in a database related context, you should immediately think of some possible implications. So we should ask a couple of questions.

  • Can the Jet-/ACE-DB-Engine enforce uniqueness of the data in an OLE Object column? – No, sorry, it can’t.
  • Can you create any index on an OLE Object column? – No, again.
  • Can you sort the data in OLE Object column? – For the third time: No!

Well, these limitations make sense. Theoretically the size of an OLE Object can be up to 1 GB. Indexing such lengthy data would be insane. And there is not much point in sorting Office Documents or pictures by their binary representation either.

Still, we have the blatant wish to sort or index binary data.

If you look at the Access table designer, you will quickly notice that there is no other binary data type available. (The Attachment type is just an OLE Object wrapped in a complex structure.)

Binary to the rescue

Unbeknownst to many, Access has a native data type for short binary data. Binary.

For some weird reason, the Access team at Microsoft decided, you cannot create a column of this data type in the graphical table designer. So hardly any Access developer knows about the binary data type.

If a table already contains a column of the Binary data type, this column is displayed in the design view of the table and its properties can be edited.

Access Table designer, showing a binary column

The Binary data type can be up to 510 bytes in length, it can be sorted and indexed. - That is exactly what we need for our binary keys.

By default, the Binary data type in Access has variable length. Its name is confusing, as in SQL there is the Varbinary data type for variable length data and the Binary type for fixed length data. The Jet-/ACE-DB-Engine supports fixed length binary fields as well, but Access does not display this option anywhere in its user interface, not even for existing columns.

Fixed length Binary fields are always padded with zeroes to their maximum length.

So how to create a Binary column in a table, if this is not possible in the table designer?

There are three ways available to create a (Var-)Binary column in a Microsoft Access database.

1. Create a Binary / Varbinary column using DDL

You can use DDL (=Data Definition Language, a subset of SQL) to create a table with a Binary column. To create a new table, you use the CREATE TABLE statement. – Obvious, isn’t it?

To execute any DDL query, just create a new query, do not add any table to it, then switch to SQL view and enter the DDL statement in the SQL Editor window. – Here is the DDL statement to create a table with a fixed length Binary and a Varbinary column. For completeness sake, I included a LongBinary (OLE Object) column too.

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

If you want to add a Binary and/or Varbinary column to an existing table, use the ALTER TABLE DDL statement with the ADD COLUMN sub clause.

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

2. Create a Binary / Varbinary column using VBA and DAO

Of course you can also create a Binary and/or Varbinary column in a table using VBA and the DAO.TableDef-Object. You either create a new TableDef-Object or get the TableDef of an existing table. Then you create a DAO.Field with the Type DataTypeEnum.dbBinary and append it to the Fields-Collection.

This will create a variable length Binary column. If you want to create a fixed length Binary column, you add the dbFixedField flag to the Field’s Attributes.

Do get confused by the dbVarBinary item of the DataTypeEnum. This is meant for ODBC-Direct-Workspaces (deprecated/discontinued) only and cannot be used in a Jet/Ace-DB.

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. Copy an existing binary field

A simple 3rd option to create a binary field in your table is to copy one. You open the Navigation Options dialog for the Access Navigation Pane and enable Show System objects. Then open the MSysObjects table in design view, and copy the Owner column using either Copy from the context menu or [CTRL]+[C] on the keyboard, then open your own table in design view and simple paste the copied field using the context menu or [CTRL]+[V]. You can then rename the field and adjust its length. – This is a variable length Binary column then. There is no fixed length Binary column available to copy in the system tables.

Working with binary data in VBA

When you work with binary data in VBA, you use a Byte Array as data type for your binary data. To retrieve or write data from/to a table you just read/set the Value-Property of the DAO.Field in a Recordset.

Other than with the OLE/LongBinary fields, you cannot use the GetChunk/AppendChunk-Methods with Binary columns. Instead you’ll just use straight assignments from and to Byte-Array variables.

Here is a small sample procedure to add a new record to a table. The binary data is generated randomly by a helper function.

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

If you are comfortable with arrays, it’s pretty simple to work with binary data in VBA.

Warning – What you see is not what you get

If you view the data in a table containing a Binary column, Access will display the data as Unicode strings. Be aware that what is displayed might not be an accurate representation of the data.

Binary data displayed in an Access Table

Here are some problems and inaccuracies that can happen.

  • Usually each character you see is actually two bytes, but at the end of the data it might be just be one.
  • There are several non-printable characters. They are represented by plain squares. So you may see several, visually identical, squares, but each of them may represent a different binary byte.
  • It’s absolutely legitimate for binary data to contain any number of zeroes anywhere in a byte sequence. But if this data is considered to be a string (Access does this when displaying it), a zero-value-byte terminates the string. Access truncates the data then and displays only part of it.

So be discouraged to copy and paste the binary data displayed by Access around. You could lose data with that.

Conclusion

It might happen rarely that you need a to sort and/or index a binary column. But if it does, you now know there is the proper data type for that and you know how to create such a column in Microsoft Access.

I hope enjoyed reading and learned something new.

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

Subscribe to my newsletter

*

I will never share your email with anyone. You can unsubscribe any time.
This email list is hosted at Mailchimp in the United States. See our privacy policy for further details.

Benefits of the newsletter subscription



© 1999 - 2024 by Philipp Stiefel - Privacy Policiy