Microsoft KB Archive Search

Notice: This website is an unofficial Microsoft Knowledge Base (hereinafter KB) archive and is intended to provide a reliable access to deleted content from Microsoft KB. All KB articles are owned by Microsoft Corporation. Read full disclaimer for more details.

  1. Home
  2. ACC2000: Error Using OLE or Memo Field in Union Query

ACC2000: Error Using OLE or Memo Field in Union Query

View products that this article applies to.

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.

↑ Back to the top

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.

↑ Back to the top

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.

↑ Back to the top

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.

↑ Back to the top

Article Info
Article ID : 208926
Revision : 4
Created on : 1/1/0001
Published on : 1/1/0001
Exists online : False
Views : 287