How to include Text, nText and Image datatypes in a Union query

As you probably know, in Microsoft database parlance the Text, nText and Image datatypes all hold very large amounts of data. If you've ever tried to add these types to a Union query in Visual Basic, the query probably generated errors, claiming that the Text, nText or Image data type can't be selected as DISTINCT.

When you run a UNION query, the keyword forces the database to eliminate duplicates from the results--the equivalent of adding the DISTINCT keyword to the SQL statement. Text, nText and Image fields, by their very blobular nature, are potentially too large to be compared one by one by the database. As a result, you can't select unduplicated records when displaying fields of these data types.

Fortunately, SQL offers a way around this with the UNION ALL clause. This clause works exactly like the UNION keyword, except that it selects all records, duplicates or not. The following SQL provides an example:

SELECT id1, memoText
FROM dbo.tblCatalog

UNION ALL

SELECT id3, browseImage
FROM dbo.tblImages