Trouble with TADOQuery

Hello All,

I am trying to execute a query with the following form using TADOQuery
but I am running into some trouble.

SELECT *
FROM some_table
WHERE some_id NOT IN (SELECT some_id FROM another_table WHERE x = y)

This does not seem to work with the TADOQuery component. If I run the
SQL in Access it returns the exact data I am looking for but when I
dump the SQL into a TADOQuery and run it with my project it returns an
empty dataset. Furthermore, if I remove the WHERE clause the SQL runs
fine in my project. I believe it is the NOT IN portion of the WHERE
clause that is causing the problem. I tried using the MINUS operator
on the two datasets (instead of NOT IN) but that would not work because
I am working with an Access database.

As a last resort I wrote a stored procedure that executes the query and
this returns the correct data to my application but there is still a
problem. I make multiple calls to the stored procedure (sometimes
without changing the parameters) and after the first call the stored
procedures will not return any data. I have tried multiple approaches
to refreshing the data but none of them seem to work. I have tried:
- Using both TADOQuery and TADOStoredProc to execute the stored
procedure
- Open / Close the stored procedure each time it is used.
- Using the Requery method.
- Using the Refresh method but it returns the exact same dataset as the
first call.

Any help would be greatly appreciated.

Thanks
Lawrence

.

Relevant Pages