Access crashing over Union clause?

Report · Jan 30, 2007

Hi,

I'm using several rather complex SQL statements with an Access database.
They look like the following

select Forums.ID from Forums where Forums.boolPublish <> 0 and Forums.ID not
in (select Forums.ID from Forums inner join ForumUserCat on
ForumUserCat.numForumID = Forums.ID where Forums.boolPublish <> 0) union
select Forums.ID from Forums where Forums.boolPublish <> 0 and
Forums.boolRestrictedAccess <> 0 and Forums.ID in (select Forums.ID from
Forums inner join ForumUserCat on ForumUserCat.numForumID = Forums.ID where
ForumUserCat.numUserCatID in (3,8,13,27,28,32,41))

These type of statements are built dynamically by my webapplication, so
there are slight variations in the exact statement.
Usually they run fine, but every now and then running this type of query
results in an error of type "An exception occurred".

I think this may have something to with the the Union, in that the query may
be too complex for Access to handle.
Can this be the case? Or is there an other explanation for these exception
errors?

Regards, Marja

Report · Jan 30, 2007

Try UNION ALL

--
Jules
http://www.charon.co.uk/charoncart
Charon Cart 3
Shopping Cart Extension for Dreamweaver MX/MX 2004

Report · Jan 30, 2007

Hi Jules,

> Try UNION ALL

But that would return duplicates as well, wouldn't it? And I don't want
duplicates to be returned.

Regards, Marja