ACC2000: Error Using OLE or Memo Field in Union Query (208926)

The information in this article applies to:

Microsoft Access 2000

This article was previously published under Q208926

Moderate: Requires basic macro, coding, and interoperability skills.

This article applies only to a Microsoft Access database (.mdb).

SYMPTOMS

When you run a SQL-specific union query that contains an OLE Object field, you may receive the following error message:

Cannot use Memo, OLE, or Hyperlink Object field '<fieldname>' in the SELECT clause of a union query.

You observe this behavior when the Access database uses Microsoft Jet 4.0 Service Pack 5 or earlier.

CAUSE

By default, union queries implicitly sort the data and delete duplicate records. Because Memo, OLE, and Hyperlink Object fields cannot be sorted, the error occurs.

RESOLUTION

To avoid this error, add the ALL predicate to eliminate the sorting of the field data. For example, add the ALL predicate to the following SQL statement

SELECT DISTINCTROW Employees.[LastName], Employees.[Photo]
FROM Employees
UNION
SELECT DISTINCTROW Employees.[LastName], Employees.[Photo]
FROM Employees;

to produce the statement:

SELECT DISTINCTROW Employees.[LastName], Employees.[Photo]
FROM Employees;
UNION ALL
SELECT DISTINCTROW Employees.[LastName], Employees.[Photo]
FROM Employees;

Note that the SQL statement with the ALL predicate does not remove duplicate records.

REFERENCES

For more information about union queries, click Microsoft Access Help on the Help menu, type What is an SQL query and when would you use one in the Office Assistant or the Answer Wizard, and then click Search to view the topics returned.

Modification Type:	Major	Last Reviewed:	6/24/2004
Keywords:	kberrmsg kbprb KB208926 kbAudDeveloper