Why in Access can you not set an index to an OLEobject?

Answer 1

You use OLE Object fields to insert documents (Word, PDF, bitmap images, etc) into records. The OLE Object itself is actually a binary data field and the only way to compare any two OLE Objects is to compare the binary data itself. This makes no sense because we normally compare documents by type, size, author, title, creation date, last modified date, and so on, we don't compare their physical representations (the binary data itself). If we want to sort documents, we must extract the properties we wish to sort by and insert them as separate fields in our records, and use those fields as our index/sort keys.

Sometimes we really do need to use binary data as a sort key, but we cannot use an OLE Object to store that data because OLE Objects cannot be used as sort keys. Instead, we must use the built-in binary data field which can be indexed and sorted. Binary data fields can be fixed-length (binary) or variable length (varbinary). Note that OLE Objects are variable length with a theoretical limit of 1 GB, which is yet another reason why they are unsuitable for indexing. The fixed-length binary field has an upper limit of 510 bytes, making it ideal for indexing and sorting small amounts of binary data.

Unfortunately, the built-in binary data fields cannot be created using the table designer for no practical reason other than Microsoft decreed that you couldn't. Instead you must use DDL or VBA/DAO. Using DDL, the following will create a binary and a varbinary field. For completeness, I've also included an OLE Object field:

CREATE TABLE tblBinTestSQL (

Id counter NOT NULL PRIMARY KEY,

VarbinaryColumn varbinary(100) NULL,

BinaryColumn binary(100) NULL,

OLEColumn Longbinary NULL

);

Here's the VBA/DAO method of achieving the same thing:

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

Once you have at least one table with a binary or varbinary field, you can simply copy that field to other tables. You can also modify the properties of those fields in table designer; you just can't use table designer to create those fields.