How do I fix the OLE Object in Access Error 3118?

Question

Cannot join on Memo or OLE Object <name>. (Error 3118)

Answer 1

Probably you are trying to join columns of Long Text or OLE Object type. This is not possible. Check if the query is correct and you are using the right columns. If the texts are not large, the Long Text columns can be changed to Short Text (in Design Views of the tables). Or show more details about the issue.

Answer 2

Cannot join on Memo or OLE Object <name>. (Error 3118)

A pretty clear error message, isn't it?

Answer 3

Tables are joined by means of a foreign key column or columns in a referencing table referencing the primary key column or columns in a referenced table.  A column of Long Text (Memo) or OLE Object data type cannot be a key, however. Primary or foreign key columns are the only columns in which values are duplicated in two tables.  It is this duplication which enables the join of course, mapping rows in a referencing table to a row in a reference table. 

If you have tables in which the only available join columns are of Long Text or OLE Object data type, this would mean that data is being incorrectly duplicated in more than one table.  The join should be undertaken on 'surrogate' keys, normally of long integer number data type.  In the case of a primary key this can be, and in most cases is, an autonumber.  The corresponding foreign key column should be a conventional long integer number data type. 

Having introduced the surrogate key columns into the table, the Long Text or OLE Object column in the referencing table can, and should, be dropped from the table.  It is now redundant as the value at each Long Text or OLE Object column position in the referenced table can be returned in a query which joins the two tables on the newly introduced primary and foreign keys.